4

I'm still struggling to understand SQL's backup jobs.

We currently have a SQL Server 2005 instance using the FULL recovery model.

We do a full database backup every week.

BACKUP DATABASE [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase.Bak'
WITH INIT, NAME = N'MyDatabase.Bak'

And a differential backup once a day

BACKUP DATABASE [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase_Diff.Bak'
WITH INIT, DIFFERENTIAL, NAME = N'MyDatabase_Diff.Bak'

Now I am trying to figure out how to schedule Transaction Log Backups every hour, so at most we would lose an hours worth of work.

BACKUP LOG [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase_Log.Bak'

My problem is, our storage space is limited and I don't want the log file to grow too large. Can I use WITH INIT on the transaction log backup to force it to create a new file every hour? Or do I need all the Transaction Log backups since the last Differential backup to restore to a specific point in time during the day?

And if I do need to keep all copies since the last differential, is there a way to tell it to reset anytime I do a backup?

2 Answers 2

6

No, do not use WITH INIT for transaction log backups to the same file. If you do that, you may as well not take them in between full/diff backups.

Typically you will backup the log to a unique file each time. Each one will have some form of a timestamp in the name and have a .trn extension rather than .bak. This is mostly for management purposes - it makes it really easy to identify when a log backup was taken, when it is safe to delete, whether it should be part of your point-in-time recovery operation, etc.

2
  • Thanks. Is there an easy way have SQL clean up old transaction log backups, or does that have to be a manual or non-SQL process?
    – Rachel
    Jan 14, 2013 at 19:46
  • 3
    If you are using a maintenance plan, there is a checkbox that will say "delete files older than <n>." Otherwise you will have to manage that yourself just like you do with .bak, .diff, etc. Jan 14, 2013 at 19:46
4

Thanks for posting.

so at most we would lose an hours worth of work.

If your transaction logs are on a different disk array than your data file, you perform a 'tail of the log backup'in case of a data array failure. This would ensure minimal data loss so you wouldn't even lose that hour.

Can I use WITH INIT on the transaction log backup to force it to create a new file every hour

No, as to your next question, you will need every single transaction log backup after the last diff backup to do any 'point in time' recovery. Do not overwrite them, keep them all, or they'll be useless.

You might want to check out the one of the most recommended backup/maintenance scripts by SQL MVPs by OlaHallgren, which perform a lot of the error handling and optimizations for you. You will want to specify a new file name for each transaction log backup if you don't want 1 big file so you can restore them all when needed.

In case of an emergency, check out this post on how to generate the restore scripts but DOUBLE CHECK THAT YOU ARE NOT RUNNING THEM IN PROD IF YOU DON'T WANT TO. IT WILL OVERWRITE YOUR DATABASES. Test them on a different server first.

2
  • Thanks for the information about Tail-Log backups. I wasn't aware of them :)
    – Rachel
    Jan 14, 2013 at 19:46
  • Hi @AaronBertrand, good point. I'll reword it to 'one of the most recommended script librarys by SQL MVPs' Jan 14, 2013 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.