seeking TLog Backup Clarity

[simian336]

Most people say to take a Full Backup then take a Tlog Backup to clear the transaction logs.

So I tried that and did....

6am_Full_Backup.bak
6am_TLog_Backup.bak

12pm_Full_Backup.bak
12pm_TLog_Backup.bak

Then I restored the 6am_Full_Backup.bak and attempted to do a partial tlog restore on the 12pm_TLog_Backup.bak back to 8 pm and it failed. (Plus I had to start with 6am_TLog_Backup.bak even though it was only a few seconds old.)

so then I reversed them and took the tlog backup first then the full.

6am_TLog_Backup.bak
6am_Full_Backup.bak

12pm_TLog_Backup.bak
12pm_Full_Backup.bak

Then I restored the 6am_Full_Backup.bak and then was able to do a partial tlog restore on the 12pm_TLog_Backup.bak back to 8 pm.

So if you want to be able to restore from tlogs should you do the tlog backup first.
Or am I missing something?

Thanks

Simi

 

[SQLBill]

Yes. A new full backup restarts the Tlog backup chain.

You did:

6am_Full_Backup.bak
6am_TLog_Backup.bak -- belongs to the 6am Full Backup

12pm_Full_Backup.bak
12pm_TLog_Backup.bak --belongs to the 12 pm Full Backup

So you can't restore the 6am Full backup and the 12 PM TLog backup since the 12 PM TLog belongs to the 12 PM Full backup.

What is normally done is to do a Full Backup once a day (or once a week) and TLog or Differential backups in between.

Sunday night - Full Backup
Monday - Tlog backup every hour
Tues, Weds, Thurs, Fri, Sat - same as Monday
Sunday - Tlogs every hour until the Full Backup is done.

Or every night a full backup is done, then a TLog backup made every hour (1/2 hour, 15 minutes....whatever you need)

But the 'chain' starts with the full backup and ends with the next full backup.

Is that clearer?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[simian336]

Yes, Thanks.

I have checked many sites and it is implyed in a few cases but not very specific.

So any one using Tlog backups should be very careful in the interval between their last tlog backup and the next full backup.

Most recommendations I see on the net leave that period suspect.

Thanks

Simi

 

[SQLBill]

Yes. You can do as many tlogs as you want, but the next full backup starts a new chain. Some people will do a tlog backup just before they do their next Full Backup.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875