Backup questions

[jimmythegeek]

Our proposed backup plan is to Backup the db every night with 1 hour trans log backups (the 1 hour increments are warranted, we have taken some time to determine this). That said,

If I backup the db at 11:00 PM and my trans logs backup on the hour from 12:00 AM to 10:00 PM, should I use "with init", or "no_truncate" with the log backups?

When I do a full backup of the db, does it clear the trans log? shrink it?

What is the best way to ensure that after a full backup, the transaction log starts over from that point?

Thanks in advance.

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[SQLBill]

If I understand your question clearly....

a transaction log is from the last backup of any kind. So if I do a Full backup at midnight and a transaction log backup at 0100 and 0200, the 0200 backup does not contain the data the 0100 trans backup does. You would need to restore the full backup and BOTH transaction logs.

A differential backup is everything since the last FULL Backup. If I do the same full backup at midnight, the two transaction logs, a differential at 0300, a transaction log backup at 0400; then when I restore I only need to restore the full backup, the differential, and the 0300 trans log backup.

NEVER overwrite a transaction log backup unless you have run either a full backup or a differential backup since the trans log backup.

Transaction Logs are 'tied' to the last full backup.

-SQLBill

 

[jimmythegeek]

So if I do a full backup at midnight, then hourly T-Log backups, I will have 23 Log backups by the next midnight? Wouldn't you have to create 23 backup devices to do that?

Maybe I wasn't clear on my question, can't you just create a single T-Log that overwrites each time? If so, after you create the full backup, does it clear the Log?

Sorry for any confusion.

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[SQLBill]

Yes, you will need 23 backup devices.

If you keep overwriting the transaction log backup, you can never use the backup to do a restore. You MUST have all of the transaction log backups that were done since the last full backup or the last differential backup.

If you create a full backup it clears the log. There's a little bit more than that, but basically it resets the log.

There's more on backing up in the FAQs in the MS SQL Server Programming forum.

Suggestion: Do a full backup at 1100 PM
Do transaction log backups every hour from 12 PM to 3 AM
Do a differential backup at 4 AM
Do transaction log backups every hour from 5 am to 7 am overwriting the original transaction log backup
Do a differential backup at 8 am overwriting the previous differential backup.
Continue this for the rest of the day.

To restore: Restore the Fullbackup, the most recent differential, and however many transaction log backups were done after the most recent differential.

-SQLBill