Sql-server – Is it OK to use WITH INIT for the Transaction Log Backup to avoid having it grow too large

backupsql-server-2005

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?

Best Answer

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.

Related Question