Creating a Sequence of Transaction Log Backups

SQL Server
What is the best practices for creating daily (hourly) transcation log backups? Option 1: Create individual jobs for each hour written to individual backup files? Example: log0800am.bak log0900am.bak log1000am.bak so forth Option 2: Create two jobs: (1) first job initiates backup file WITH INIT; (2) second job backups up log hourly to same backup file WITH NOINIT? Example: logHourly.bak @ 8:00am initialized logHourly.bak @ 9:00am appended logHourly.bak @ 10:00am appended again so forth Thanks in advance ...

Answer Wiki

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

My preference would be to go for individual files, whose names include the date and time of the backup. Advantages being:

1. If you look at the backup directory, you can see at a glance which backups you have without having to go to msdb or the Enterprise Manager GUI
2. If you’re short of space in the backup directory, you stand a better chance of freeing enough space to continue, while keeping recent backups on hand in case you need them, if you have a large number of small files rather than a small number of large files.
3. If you have problems with a particular backup, you only affect the one file.

By the way, the Maintenance Plan setup wizard defaults tran log backups to have a .TRN suffix rather than .BAK; you might like to consider something similar to differentiate between your full and tran log backups. If you’re forced into doing a recovery in a hurry, anything which clarifies what you have to work with is good!

Discuss This Question: 1  Reply

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.
  • MisterMac
    I still think Maint Plans are a pretty good, quick and dirty method with some (not all) flexibility. After all, the wizard simply generates a scheduled job like this: EXECUTE master.dbo.xp_sqlmaint N'-PlanName MyUserDBMaintPlan -Rpt "..someLocalLogPathDBMP--UserDBs_LogBackup.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "F:SQLBACKUP" -DelBkUps 4DAYS -CrBkSubDir -BkExt "TRN"' ... which creates log dumps that look like this: ... MyDB_tlog_200405201700.TRN MyDB_tlog_200405201800.TRN ... However, if you need more flexibility in the naming of the dump file (or other criteria), I'd suggest creating a single proc that's scheduled every hour to do the backups. If it's the first log dump of the day (based on a datediff check with getdate() or perhaps based on a query of the last full backup time), init the backup file; otherwise, append. Good luck...
    0 pointsBadges:

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.

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


Share this item with your network: