Questions about a SQL database with hourly transaction log backups

Tags:
SQL
SQL Database
SQL Server backup
SQL transaction logs
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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.
    70,220 pointsBadges:
    report
  • carlosdl
    Sorry MrDenny, I misspelled your name in my last comment, :-)
    70,220 pointsBadges:
    report
  • JCDBA
    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?
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following