Database restore after 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 back to its current status but why not clear out the inactive transactions as part of the backup?

I am revising for the 228 exam.

Thanks
Jon

 

[SQLBI]

Hi,

I too am studying for 228, so forgive me if im not 100% but i thought id answer this question to test my knowledge. Please if anyone reading this spots any errors, please let Jon, and myself know.

When you back up a log normally, SQL server commits all committed transactions to disc, and truncates the log.

In the case of a corrupt DB, writing to disc may not be possible, or may make the problem worse, imagine if your disc is the reason the DB failed.

By backing up you log WITH NO_TRUNCATE you are preserving those transactions that have not yet been written to disc, and thus are able to restore to a point of failure.

Your restore procedure would be something like:

Restore full backup WITH NORECOVERY

Restore Differential backups WITH NO RECOVERY

Restore Transaction Log backups WITH NO RECOVERY

Restore final Log backup (NO_TRUNCATE) WITH RECOVERY

I hope i've not confused you too much and i'm on the right track.



Cheers,
Leigh

You're only as good as your last backup!

 

[jonwolds]

Leigh,

Thanks for the response

Sounds reasonable. I can't find anything to confirm it though.

Jon