| Downloads |
|---|
| 128818.zip |
In "Intelligent Transaction Log Backups," I presented an automated solution for backing up transaction logs. I've also developed a solution for automating differential backups of databases and a script that lets you quickly restore transaction logs from backups if the need should arise. With all three tools, backing up and restoring your SQL Server system is a snap.
The Differential Backup Solution
Like the transaction log backup solution, the differential backup solution uses an admin job and a worker job. The admin/worker job concept is simple. You schedule the admin job to run periodically. Each time the admin job executes, it creates or edits the worker job, then starts it. The worker job, which is updated every time the admin job executes, is responsible for performing the differential backups of databases.
In the differential backup solution, the admin job is a SQL Server Agent job named Server Daily Maintenance - Differentials. This job contains three steps:
- The admin job first creates the msdb.dbo.SDM_Cleanup table if it doesn't exist. This table stores records on database backups that have been performed. Records are added to the SDM_Cleanup table by the worker job.
- The admin job calls the mnt_CleanUp stored procedure. As its name suggests, this stored procedure performs cleanup operations, including deleting old database backups. It also deletes the associated records in SDM_Cleanup.
- The admin job uses the mnt_BackupDifferential stored procedure to create a SQL Server Agent job named Maintenance_BackupDifferential. The admin job then executes Maintenance_BackupDifferential, which is a worker job.
The worker job will contain one step for each database. (You can choose to backup all databases or just databases in Full Recovery mode.) For each database, the worker job first checks to see whether a full backup exists. If a full backup doesn't exist, it performs one. If a full backup exists, it performs a differential backup. The backups can be performed using the SQL Server native format or LiteSpeed format. (LiteSpeed is a third-party utility from Quest Software.) The default backup format is SQL Server native, so LiteSpeed isn't required for this solution.
I created the SDM_Differentials_QuickSetup.sql script to quickly set up the differential backup solution. When you run SDM_Differentials_QuickSetup.sql, it creates the mnt_CleanUp and mnt_BackupDifferential stored procedures and the Server Daily Maintenance - Differentials admin job.
Note that the admin and worker jobs use xp_cmdshell when they run. I put xp_cmdshell inside a wrapper. That way, if you typically have xp_cmdshell disabled, the jobs will enable it for a brief moment so that they can perform their tasks, then return xp_cmdshell to the disabled state. If company policy forbids you to use xp_cmdshell even when it's in a wrapper, you won't be able to use this solution.
After you run SDM_Differentials_QuickSetup.sql, you can change some settings in the Server Daily Maintenance - Differentials admin job if desired:
- By default, the Server Daily Maintenance - Differentials admin job is scheduled to run every six hours. If desired, you can change the frequency in the New Job Schedule page.
- By default, the mnt_CleanUp stored procedure is set to delete database backup files that are older than one day. If you want to change that default, find the code
EXEC msdb.dbo.mnt_CleanUp<br> @DaysOld = 1
in the second job step and replace 1 with an integer that represents the number of days you want the database backup files to be kept on the local server. Note that the stored procedure deletes only those backup files created by the transaction log backup solution. - By default, the worker job performs differential backups of databases in Full Recovery mode using the SQL Server native format. It stores the backup files in E:\MSSQL\DIF, overwriting any existing backup file for a database. If you want to change these defaults, find the code
EXEC dbo.mnt_BackupDifferential @mode = 'Full',<br> @WriteOver = 1,<br> @Format = 'NV',<br> @dir = N'E:\MSSQL\DIF'
in the third job step. If you want to perform differential backups of all the databases, specify 'All' for @mode. If you don't want to overwrite existing backup files, specify 0 for @WriteOver. If you want to use the LiteSpeed format, specify 'LS' for @Format. If you want to store the backup files in a different location, replace E:\MSSQL\DIF with the desired directory in the @dir argument. The directory you specify must exist. The worker job will create subdirectories in that directory as needed, so the SQL Server service account must have write permissions to that directory.
The differential backup solution is a useful supplement to the transaction log backup solution. Another helpful supplement is the RestoreLogs.sql script.
