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
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
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
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
exec master.dbo.xp_cmdshell @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:
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.
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 dont 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 Ive
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