Copying Database Backups to an Alternative Location

Thursday Aug 31st 2006 by Gregory A. Larsen

Learn how to use ALERTs, a SQL Agent job and a stored procedure (SP) to create a copy of your critical database backups on another physical machine as soon as the database backups are created.

One of the tasks a DBA must consider is developing a backup strategy for their databases. A key piece of this strategy is to ensure that a complete set of the most recent database backups are available should there be a hardware failure. This article will discuss a method of how to use ALERTs, a SQL Agent job and a stored procedure (SP) to create a copy of your critical database backups on another physical machine as soon as the database backups are created.

Database and transaction log backups are a key piece in managing a database. Backup requirements are designed around your disaster recovery needs. Typically the number of database and transaction log backups are driven by the number of records inserted, update and deleted from your database on a hourly, daily or weekly basis. For highly active databases, it is not unusual to have many backups taken daily, where most of these backup are likely to be transaction log backups.

Normally backups are first written directly to disk on the physical database server, and then nightly these backups are copied off to tape. This strategy can create a large window of time when the database backups are only on disk. This timeframe between when backups are written to disk and the time they are copied to tape is what I call "Lag Time." The longer the "Lag Time" the bigger the risk of you losing critical database backups should you have a hardware failure. As a DBA, it is your job to minimize the risk of database backups being lost during the "Lag Time."

There are a number of solutions that can be deployed to reduce the risks associated with long "Lag Times." This article will discuss one way to minimize the risk caused by "Lag Time." I will explain how to I built an SP, a SQL Agent job and a number of ALERTS to copy my database backups to another physical machine as soon as they were created.

The SP

The SP is the component of my process that does most of the heavy lifting. My SP needed to perform two tasks. First, it needed to identify what database backups to copy, and secondly it needed to copy the database backups to an alternative physical storage location.

The SP is the component of my process that does most of the heavy lifting.  My SP needed to perform two tasks.  First, it will need to identify what database backups to copy, and secondly it will need to copy the database backups to an alternative physical storage location. 

There are probably a number of ways to write a SP to identify and copy database backups.  Below is the SP I developed to perform the copying of database backups.  This code identifies all database backups that have been taken in the last 72 hour.  For each database backup, it determines if it has already been copied to the alternate locations.  If the backup has not been copied then the SP copies the database backup to my secondary backup storage location. 

create proc usp_copy_db_backup_files 
   @copyd varchar(200)=  null
Author: Gregory A. Larsen  Date: 08/27/2003
Description:  This stored procedure will copy database backups to 
              a secondary location for disaster recovery purpose.  When
              the proc runs it will copy any database backup that has  
              been created in the last 72 hours, provided the backup
              does not already exist in the alternative directory.
set nocount on 
declare @tmp  varchar(200)
declare @cmd  varchar(2000)
declare @oldcmd varchar(2000)
if @copyd is null 
   raiserror('No copy directory provided',16,1)
   Print 'Usage:usp_copy_db_backup_files @copyd=<directory for backup files>'
-- create tables to hold file lists
create table #filelist1 (output varchar(250)) 
create table #filelist2 (output varchar(250))
-- create temp table to hold commands to execute
  create table #commands (cmd varchar(2000))
set nocount on
-- get a list of the physical device name for all backups that where 
-- created in the last 72 hours. 
insert into #filelist1 select physical_device_name from msdb.dbo.backupmediafamily 
         join msdb.dbo.backupset 
         on msdb.dbo.backupmediafamily.media_set_id = 
   where backup_start_date > dateadd(hour,-72,getdate())
--build dir command
set @tmp = 'dir /b ' + rtrim(@copyd) + '\*.*'
--get a list of the backup file that exist at alternative location
insert into #filelist2 exec master.dbo.xp_cmdshell @tmp
-- generate a copy command for all backup 
-- files that have yet to be copied.
insert into #commands select 
  'copy "' + rtrim(#filelist1.output) + '" ' + rtrim(@copyd) 
 from #filelist1 left join #filelist2 
 where #filelist2.output is null
--are there backups that need to be copied
if (select count(*) from #commands) > 0 
-- loop throught the commands and copy one file a time.  
  set @oldcmd = ''
  select top 1 @cmd = cmd from #commands order by cmd
  while @cmd <> @oldcmd
-- copy backup to other location
    print @cmd
    exec master.dbo.xp_cmdshell @cmd
    set @oldcmd=@cmd
-- get next command to process
    select top 1 @cmd = cmd from #commands where @oldcmd < cmd order by cmd
-- remove temporary files used.
drop table #filelist1, #filelist2, #commands

The above stored procedure accepts a single parameter, @copyd. The @copyd parameter identifies the target directory (secondary backup storage location) for where the database backups will be copied. I set this parameter to a UNC name for a network share located on a different physical box, then the one where I am copying the database backups. By reviewing this code you will see that this SP identifies database backups be copied by reviewing the database backup metadata stored in the "msdb" database. The DOS command "dir" is executed via an "xp_cmdshell" T-SQL command to identify the database backups currently residing at the target directory location. By comparing the physical database backup names for the backups created in the last 72 hours, with the name of the database backups residing at the target directory location, the SP is able to identify which backups need to be copied. The "xp_cmdshell" T-SQL command is also used to physically copy the database backup to the directory identified by the @copyd parameter, using a DOS copy command generated by the SP. Be aware that in order to get this SP to work, the account running this SP needs to have access to the target directory.

The SQL Agent Job

I have a very simple SQL Agent job to support this process. My job only contains a single step. This single step executes the "usp_copy_db_backup_files" stored procedure. The job I setup on my server is called "Copy Backups" and the single step executes the follow T-SQL code:

Exec usp_copy_db_backup_files


In my environment, we only take complete, differential and transaction log backups. So I needed my ALERT processes to fire each time a complete, transaction log or differential, backup is taken. Basically, I needed an ALERT to fire any time a 18264, 18265, or 18270 error message is generated. These are the error messages associated with the complete, transaction log and differential backups.

To implement my database backup copy trigger process, I created three different ALERTs. Each ALERT was set up to fire when one of the above error messages is created. When the ALERT fires, it will submit my SQL Agent job "Copy Backups" which in turn will execute the "usp_copy_db_backup_files" SP to copy my database backups. Let me review how I built one of my ALERTS to trigger the backup copy process.

The ALERT I will be showing you is the one that will fire when a complete database backup is executed for any database or when the 18264 error message is generated. To set up my ALERT I first expanded the "Management" folder in Enterprise Manager, then clicked on the plus sign (+) next to "SQL Agent," and then right clicked on "Alerts." From the menu of tasks that came up, I clicked on the "New Alert" task. After clicking on the "New Alert" task, the following window was displayed:

On this window, I entered the name of my new ALERT. I entered the name "Copy Complete Database Backup," since I was building this ALERT to fire whenever a complete database backup is taken. Since I wanted this ALERT to fire based on an event, I left the "Type" setting as above. For the "Event alert definition", I clicked on the "Error number:" radio button, since I wanted to fire this ALERT based on an error number. After doing this, I was allowed to enter "18264" as the error number for the ALERT. I left the "Database name:" field as it was, since I wanted the ALERT to be fired every time a complete backup is taken, regardless of the database. Once I completely filled out all the appropriate fields, my ALERT properties window looked like this:

Now all that was left to complete was what my ALERT would perform when it is fired. Basically, I wanted the ALERT to start my SQL Server Agent job "Copy Backups." I defined this in the ALERT, by completing the "Response" tab to look like this:

After that, I just saved the ALERT by clicking on the "OK" button. Now after performing the above tasks I had a single ALERT that would trigger the copy database backup process anytime a complete database backup is taken. Since I also needed additional processes to be triggered when any transaction log and differential backups are taken, I created two more ALERTS using the same process. One of the additional ALERTS will fire when error message 18265 is generated, and the other ALERT will fire when error message 18270 is produced.

Other Consideration:

Be aware that if you are taking database file backups as well then different error messages are generated for file backups. Therefore, if you would also like the file backups to be copied then you will need to create additional ALERTS that are triggered when the file backups are run.

In addition to copying the database backups to an alternative physical location, you will need to develop a method to purge the backups from this alternative location once they are no longer needed. If you don’t do this, eventually you will run out of disk space on the device where you are copying the database backups. I typically remove database backups that are over two weeks old.


If you are currently having a long delay between when database backups are written to disk prior to having them copied to tape, then you run the risk of losing your most recent database backups should you have a disk failure. If this is your situation, then you might consider implementing an ALERT triggering process similar to the one I’ve constructed. Having two copies of your database backups on separate physical drives attached to different physical machines greatly reduces the risk of losing your critical database backups.

» See All Articles by Columnist Gregory A. Larsen

Mobile Site | Full Site