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

Conclusion

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: iecdba@hotmail.com)