Posted by: Khairey July 19, 2010
SQL server backup question
Login in to Rate this Post:     0       ?        
In other words, sql is taking all your transaction log files as a single chunk while you are restoring. It is not just referring to the current log file. If you delete any one of the log backup in the sequence then you will not be able to recover anything after that sequence.
e.g
4:05 PM - Full_backup.bak, and your transaction log resets
4:10 PM - transaction log backup -log1.trn
4:11 PM - delete record 1
4:15 PM - transaction log backup - log2.trn
4:16 PM - restore at point 4:12 PM (record 1 will get restored), but if you have deleted log1.trn or log2.trn and try to restore at point 4:12 PM then record 1 will not get restored (you will not have option to restore at that point or it will error out or simply you can't restore).

So the concept you are thinking is right. Its just SQL is making a 'smart' decision to take into consideration all the transaction log backup while restoring.

I am not sql dba, just have worked little on its backup and log shipping/mirroring. I may be totally wrong. So please verify yourself.

And benefits of transaction log backup looks obvious to me. If you hadn't backed up transaction log and your single transaction log is corrupted then you will loose all the data after the last full backup. But if you have multiple file, then you wont loose the data until you loose the sequence of trn log. If your tail is corrupted, you can restore up to the last trn backup which could be just few minutes earlier. So you can restore up to any point your trn files are fit. Further trn backup is completed in much less time compared to full backup. So it is usually done every few minutes. The previous trn backup after the full backup is not needed. This way you are also preventing your log file from increasing indefinitely.
Hope I made you clear.

Last edited: 19-Jul-10 09:14 PM
Read Full Discussion Thread for this article