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?