This article describes how to implement a flexible backup strategy for all databases in a given SQL server. All databases are backed up completely or differentially by using a backup strategy control character depending on the database name and the day in the week. The control character will state a "C" for a complete backup or a "D" for a differential backup.
The idea of A flexible backup strategy crossed my mind half a year ago when I was asked by a little startup company to design a backup strategy for all their SQL databases. Each database backup will be named with the database name, the backup mode and the current date. The system databases were to be backed up in full mode, the Larger production databases should be backed up in full mode on weekends and differentially during the week work days and the Test databases will be backed up completely on each odd day and differentially on each even day.
I used a control Table I build in the master database to implement the data structure. This is the script to initialize the Backup_Strategy Table (download here).
use master go if exists (select * from dbo.sysobjects where id = object_id(N'dbo.DatabasesBackupStrategy' ) and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table dbo.DatabasesBackupStrategy GO CREATE TABLE dbo.DatabasesBackupStrategy ( db_name varchar (50) NULL , backup_strategy char (7) NOT NULL ) GO ALTER TABLE dbo.DatabasesBackupStrategy WITH NOCHECK ADD CONSTRAINT DF_DatabasesBackupStrategy_backup_strategy DEFAULT ('CDDDDDD') FOR backup_strategy GO Insert dbo.DatabasesBackupStrategy (db_name) select name from sysdatabases where name <> 'tempdb' Update dbo.DatabasesBackupStrategy set backup_strategy = 'CCCCCCC' where db_name in ('master','model','msdb') go
The backup strategy character word was initialized to "CCCCCCC" for all system databases (excluding tempdb), meaning that all system databases will be backed up completely on each day. For all other databases the initial strategy is to backup completely on Sunday and on each other day. To do a differential backup, just update the control character vector for the database. You want to change to do the desired combination of full or differential backup. Each character in the word represents a day of the week, so full backups on Sunday and Wednesday with a differential the rest of the week would look like "CDDCDDD"
I coded a stored procedure called sp_flex_db_strategy to implement the idea. The procedure gets a directory name (including path) on the server and loops over all databases and, find the backup strategy control character column and decides for the current day of the week what to do.
The backup directory should exist on the server prior to running the procedure. You should execute this procedure in a job that runs once a day, each day. (Example exec sp_Flex_Db_Backup 'c:\backup'. This is the code implementation of the Flexible Backup Process: (download here)
create procedure sp_Flex_Db_Backup (@BackupDir varchar(255)) as DECLARE @BackupFile varchar(255) Declare @DbName varchar(30) Declare @BackupDescription varchar(255) Declare @BackupName varchar(30) Declare @MediaName varchar(30) Declare @BackupMode char(1) Declare @BackupStrategy char(7) Declare @BackupStatement varchar(500) DECLARE strategy_cursor CURSOR FOR SELECT db_name, backup_strategy FROM dbo.DatabasesBackupStrategy OPEN strategy_cursor FETCH NEXT FROM strategy_cursor INTO @DbName, @BackupStrategy WHILE @@FETCH_STATUS = 0 BEGIN select @BackupMode = Substring (@BackupStrategy, DatePart(dw, CURRENT_TIMESTAMP),1), @MediaName = @DbName + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP,112), @BackupName = @DbName + '(Daily Backup) ' , @BackupFile = @BackupDir + '\' + @DbName + CASE @BackupMode WHEN 'C' THEN 'Full' ELSE 'Diff' END + '_' + CONVERT(varchar, CURRENT_TIMESTAMP , 112) + REPLACE(LEFT( CONVERT(varchar,CURRENT_TIMESTAMP,108),5),':','') + '.BAK', @BackupDescription = CASE @BackupMode WHEN 'C' THEN 'Normal' ELSE 'Differential' END + ' BACKUP at:'+CONVERT(varchar,CURRENT_TIMESTAMP)+'.' IF @BackupMode = 'C' BEGIN SET @BackupStatement = 'BACKUP DATABASE ' + @DbName + ' TO DISK = ' + '''' + @BackupFile + '''' + 'WITH NAME = ' + '''' + @BackupName + '''' + ', DESCRIPTION = ' + '''' + @BackupDescription + '''' + ',MEDIANAME = ' + '''' + @MediaName + '''' + ' , @BACKUPDESCRIPTION = ' + '''' + @BackupDescription + '''' + ' , STATS = 10 ' end ELSE begin set @BackupStatement = 'BACKUP DATABASE '+@DbName + ' TO DISK = ' + '''' + @BackupFile + '''' + ' WITH DIFFERENTIAL, NAME = ' + '''' + @BackupName + '''' + ', DESCRIPTION = ' + '''' + @BackupDescription + '''' + ',MEDIANAME = ' + '''' + @MediaName + '''' + ', @BACKUPDESCRIPTION = ' + '''' + @BackupDescription + '''' + ', STATS = 10' end exec (@BackupStatement) FETCH NEXT FROM strategy_cursor INTO @DbName, @BackupStrategy end close strategy_cursor deallocate strategy_cursor go
This procedure can be used as a general "Tool" for implementing quickly a flexible but robust backup strategy for all your databases, without the work of creating devices, using many backup jobs etc. All the work is done be a single procedure that should be scheduled to work each day by a single job.
Eli Leiba works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies. (e-mail: firstname.lastname@example.org)