Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
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 '13 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. –  Aaron Bertrand Jan 14 '13 at 19:46
add comment

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.

share|improve this answer
    
Thanks for the information about Tail-Log backups. I wasn't aware of them :) –  Rachel Jan 14 '13 at 19:46
    
Hi @AaronBertrand, good point. I'll reword it to 'one of the most recommended script librarys by SQL MVPs' –  Ali Razeghi Jan 14 '13 at 21:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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