Introduction

I write this document based on one task that seemed to be huge and mainly boring but ended as an interesting challenge. Once a new client asked me to produce an auditing report of what was implemented in a production box. The RDBMS was quite full of user databases (108). They had several best practices implemented, but everything fell apart when I decided to look at the backups.

They trusted everything on a centralized full backup that was running only once every 24 hours. The client wasnt aware that could lose one entire day of work if everything went wrong.

I decided to draw a backup template/policy that would cover all databases on the server and would fill the needs of each department and application group model. After talking with several directors and team leaders, it was decided to go for a full backup once each 24 hours, a transaction log backup every 15 minutes and a differential every 4 hours. It was also decided that each set of backups would be stored for a month on other storage and on the server would be always the set of previous day's backups.

Implement the backup policy

When I started to implement the backup policy, I realized that it would be necessary to create 108 * 2 backup devices (two devices for each database: one for the full and the other for the transactional/differential). So creating backup devices to all databases would not be a sweet task.

In order to get some fun out of this boring task I decide to try to write some T-SQL to do the boring part for me. After a couple of hours I got all the scripts I needed to generate the T-SQL code in order to create devices and launch the backups for all databases (always excluding the system databases).

Each database was supposed to have 2 devices: one for the full backup (device name=<database name>) and another for the transaction and differential log backup (device name=TRAN_<database name>).

The following blocks show the code needed to create the devices and launch the backups.

  
  /********************/  /*FULL BACKUP DEVICE*/  /********************/  
  SELECT
  'IF  NOT EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N' +  char(39) + name 
  +  char(39) + ')' + ' EXEC sp_addumpdevice ' +  char(39) + 'disk' +  char(39) + ', ' 
  +  char(39) + name +  char(39) + ', ' +  char(39) + '<DRIVE>:<PATH_OF_BACKUP_REPOSITORY>' 
  + name +'.bak' +  char(39) + ' ' as inst
  FROM
  sysdatabases
  WHERE
  name not in ('master', 'model', 'msdb', 'tempdb')
  ORDER BY
  name
 
 

Block 1 The result of this T-SQL will create the full backup devices

  
  /****************************/  /*TRANSACIONAL BACKUP DEVICE*/  /****************************/  SELECT
  'IF  NOT EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = N' +  char(39) + 'TRAN_' 
  + name +  char(39) + ')' + 'EXEC sp_addumpdevice ' +  char(39) + 'disk' +  char(39) + ', ' 
  +  char(39) + 'TRAN_' + name  +  char(39) + ', ' +  char(39) 
  + '<DRIVE>:<PATH_OF_BACKUP_REPOSITORY>' + 'TRAN_' + name +'.bak' +  char(39) +
  ' ' as inst
  FROM
  sysdatabases
  WHERE
  name not in ('master', 'model', 'msdb', 'tempdb')
  ORDER BY
  name
 
 

Block 2 The result of this T-SQL will create the transactional backup devices

  
/**************//*Full Backups*//**************/  SELECT
  'BACKUP DATABASE [' + name +  '] TO [' + name + '] WITH  INIT ,  NOUNLOAD ,  NAME = N' 
  +  char(39) + 'Admin - ' + name + ' backup' +  char(39) + ',  SKIP ,  STATS = 10,  FORMAT' as Inst
  FROM
  sysdatabases
  WHERE
  name not in ('master', 'model', 'msdb', 'tempdb', 'GIP')
  ORDER BY
  name
 
 

Block 3 The result of this T-SQL will do the full backups

  
/**********************//*Transacional Backups*//**********************/   DECLARE @Date as varchar (30)
   SET @Date = getdate()
SELECT
'BACKUP LOG [' + name +  '] TO [TRAN_' + name + '] WITH  NOINIT ,  NOUNLOAD ,  NAME = N' +  char(39) 
+ 'Admin - ' + name + ' backup trans' +  char(39) + ',  SKIP ,  STATS = 10,  MEDIANAME = N' 
+  char(39) + 'set Transacional da bd' + name + char(39) + ' ,  MEDIADESCRIPTION = N' +  char(39) 
+ @Date + char(39) as Inst_trn
FROM
  sysdatabases
WHERE
 name not in ('master', 'model', 'msdb', 'tempdb')
ORDER BY
  name
 
 

Block 4 The result of this T-SQL will do the transactional backups

After the scripts were completed, I needed to automate those routine administration tasks to run the result of the scripts.

In order to automate all the process, some DTS packages and associated jobs were implemented. A DTS package would then run each day to generate the backup devices and the backup instructions file.

Creating the files for the backups

In this case the IT department only accepted databases going into production between 8:00 and 18:00. For instance suppose the Jobs will run every day at 23:00, in this case each database created between the 8:00 and 18:00 will be added a backup device and the backup instruction will be added to the backup file. This is all automatic and you wont have to write any extra line of code for that.

Inside each Transformation Data Task a script is added to generate the full, tansaction log and the differential backup.

After the DTS packages generate the proper files from the script, we need to implement some jobs to run those files.

In this case it was decided to run the full backup job daily at 7:00 (after runing all the packages). The Transactional log backup is set to run every 15 minutes and the differential will run every 4 hours.

OSQL/SQLCMD instructions in JOBs

To run the backups I decided to put some osql commands inside the jobs' tasks. In the next blocks there are the commands for the three types of backups. I decided to include a report file (*.rpt) to get more accurate results of what was done during the jobs execution.

  osql -S <SERVER NAME> -E -h-1 -i <DRIVE>:\<PATH TO FILE>\<FILE NAME>.txt  
      -o <DRIVE>:\<PATH TO FILE>\<FILE NAME>.rpt
 

Block 5 osql Command to run the backups

  sqlcmd -S <SERVER NAME> -E -h-1 -i <DRIVE>:\<PATH TO FILE>\<FILE NAME>.txt 
     -o <DRIVE>:\<PATH TO FILE>\<FILE NAME>.rpt
 

Block 6 sqlcmd Command to run the backups

Replacing the "placeholdrs" (<>) of the above example we will get something like this:

  osql -S MyServer -E -h-1 -i C:\MSSQL2K5\AuxFiles\Backup_Total.txt 
                -o C:\MSSQL2K5\AuxFiles\Backup_Total.rpt
 

Block 7 Example osql command for the transactional backup

  osql -S MyServer -E -h-1 -i C:\MSSQL2K5\AuxFiles\Backup_Tran.txt 
                -o C:\MSSQL2K5\AuxFiles\Backup_Tran.rpt
 

Block 8 Example osql command for the transactional backup

  sqlcmd -S MyServer -E -h-1 -i C:\MSSQL2K5\AuxFiles\Backup_Diff.txt 
                -o C:\MSSQL2K5\AuxFiles\Backup_Diff.rpt
 

Block 9 Example sqlcmd command for the differential backup

There are slight differences between implementing these commands in MS SQL 2000 and 2005. The following two figures show how to do it in both enviroments.

Figure 3 MS SQL 2000 job with the CmdExec osql command.

Figure 4 MS SQL 2005 job with the CmdExec sqlcmd command.

The difference between each of those instructions is that the MS SQL 2000 instruction starts with osql and the MS SQL 2005 starts with sqlcmd.

Conclusion

Including each of these steps in the development of an automation backups solution will ensure a successful backup implementation. I hope that this could be interesting and useful for those who read it and some may decide to apply it or design their own solutions. Above all I do hope that all of the readers wont forget to implement good and accurate backup solutions for their databases.

Dont forget an important quality assurance that is the Test of your Backups. The DBA should periodically test the integrity of the backups by performing test restorations. The restorations should be random for each RDBMS to detect faulty media, hardware, or processes.