Questions about a SQL database with hourly transaction log backups
We have a SQL database with a full recovery model that backs up the database once a day, along with hourly backups for the transaction logs. However, we only have one transaction log backup file, and it is overwritten every time. Are transaction log backups cumulative, and if so, does that one file contain all transactions since the previous database backup? Also, what is the correct extension for a transaction log backup?
Software/Hardware used:
Software/Hardware used:
ASKED:
September 19, 2008 4:50 PM
UPDATED:
September 23, 2008 5:46 PM
Answer Wiki:
No transaction logs are not cumulative. You have to keep each log backup taken between the full backups.
It is actually recommended to keep a couple of cycles worth of log files. Your cycle is daily, so I would recommend keeping 2-3 days worth of log file backups.
To see all answers submitted to the Answer Wiki: View Answer History.
I remember Mr Danny said a few days ago that transaction logs are not cumulative, and you need to restore a full backup and then all transaction logs.
Sorry MrDenny, I misspelled your name in my last comment,
Thanks everyone.
What if I used the following commands in a Job:
declare @filename as char(100)
declare @deletefile as char(100)
set quoted_identifier ON
– I’m deleting the previous day trans. log
set @deletefile = ‘Del “D:\MSSQL\BACKUP\HAZ_LOG_BCKUP_’+convert(char(8), getdate()-1, 112)+’.BAK”‘
exec xp_cmdshell @deletefile, NO_OUTPUT
– creating new log file for te day
set @filename = ‘D:\MSSQL\BACKUP\HAZ_LOG_BCKUP_’+convert(char(8), getdate(), 112)+ ‘.BAK’
backup log HAZ to Disk =@filename with NOINIT
Would NOINIT make the Log cummulative for that date ? (NOINIT is the default.)
If not, how would you suggest doing this?