Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Building Automated Backups

By Paulo Luis,

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.

Total article views: 12686 | Views in the last 30 days: 11
 
Related Articles
FORUM

Backup Device

why use a disk backup device

FORUM

backup database command,which contain backup only for 14 days.

backup database command,which contain backup only for 14 days.

FORUM

BACKUP DATABASE is terminating abnormally.

BACKUP failed to complete the command BACKUP DATABASE

SCRIPT

Restore database from a device containing multiple backups

Script to restore the LATEST full and differential backups from a SQL Server 2008 R2 backup device.

FORUM

database backup

database backup

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones