Is it OK to use WITH INIT for my Transaction Log Backup to avoid having it grow too large?

Question

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?

Answer 1

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.

Answer 2

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.