Database restore after data file corruption

[jonwolds]

Hi,

If there is a problem with a database data file why does bol/exam guides advise me to immediately back up the transaction log using NO_TRUNCATE?

I understand that you need to take a log backup to restore the database to its current status but why not clear out the inactive transactions after the backup?

I am revising for the 228 exam.

Thanks
Jon

 

[maswien]

I guess the NO_TRUNCATE is not option anymore if the database itself is damaged. That's only way you can
backup the latest transaction log.

 

[jonwolds]

I have deleted the .mdf file and it still lets me backup the log without using NO_TRUNCATE.

Jon

 

[SQLBI]

To my knowledge, the log is a .ldf file and totally separate to the .mdf file.

.ldf entries are written to the .mdf file when a checkpoint occurs or SQL server attempts recovery during startup.




Cheers,
Leigh

You're only as good as your last backup!

 

[jonwolds]

It only lets you backup the tran log for a database with a corrupt datafile when recovery mode is 'Simple', which means a transaction log back up is pointless anyway.

Jon

 

[maswien]

<quote jon>

It only lets you backup the tran log for a database with a corrupt datafile when recovery mode is 'Simple', which means a transaction log back up is pointless anyway.

</quote>

This is not correct, when the recovery mode is 'full' and the database is corrupt, you can use 'NO_TRUNCATE' to backup the latest transaction log. Try change the database recovery mode to 'full' and redo your test.

 

[jonwolds]

Sorry meant to say:

It only lets you backup the tran log for a database (without using NO_TRUNCATE) with a corrupt datafile when recovery mode is 'Simple', which means a transaction log back up is pointless anyway.
Jon