Siusic Dot Com

I was asked to write a SQL server job to restore nightly a database running on a server onto another server. The database on the original

server was backed-up nightly by a database maintenance plan. So the job I was going to write was to find the latest backup on the original server, copy it to the second server and then restore it on the second server.

The first thought in my mind when I wrote this job was that I could use the xp_cmdshell system store procedure to find the latest backup file name. I later found that there was another system procedure that could do that more conveniently. That was sytem store procedure xp_dirtree. This system store procedure is available on SQL 2000 and up. It takes three parameters. The first parameter is the directory in which you want to get a list of files and sub directories. The second parameter is a number to indicate how deep in the directory hierarchy you want to go when listing the files and subdirectories. The default is 0 which will list all subdirectories and files recursively until all files and directories are listed. Be sure not to use 0 or default option on a large directory for example the root directory. It could take substantial amount of time and cause performance problem. The third parameter instructs the store procedure whether to include files or only folders. Default is 0 which shows folder only.

Example
exec xp_dirtree ‘c:\mssql\’, 3, 1

It lists the files and folders contained in the folder C:\mssql\ up to three levels down.
So how do we get the last backup file and copy it to the second server?

Here is what we can do

Declare @DBName varchar(255)
Declare @FileName varChar(255)
Declare @cmdText varChar(255)
Declare @BKFolder varchar(255)
Declare @DestFolder varchar(255)

set @DBName = ‘TestDB’
set @FileName = null
set @cmdText = null
set @BKFolder = ‘D:\BackupFoler\’

set @DestFolder = ‘\\SecondaryServer\RestoreFolder\’

create table #FileList (
FileName varchar(255),
DepthFlag int,
FileFlag int
)

–get all the files and folders in the backup folder and put them in temporary table
insert into #FileList exec xp_dirtree @BKFolder,0,1

–get the latest backup file name
select top 1 @FileName = @BKFolder + FileName from #FileList where Filename like @DBName + ‘%.BAK’ order by filename desc

–build the copy command
set @cmdText=’copy ‘+ @FileName + ‘ ‘ + @DestFolder + @DBName + ‘.BAK’

–copy the latest backup file to the secondary server
if @cmdText is not null exec xp_cmdshell @cmdText

–clean up
drop table #FileList

Now the backup file is available on the secondary server and the file name is always TestDB.BAK. A job can be set up on the secondary server to restore the backup. To go further we can also use the system store procedure sp_start_job on the first server to remotely initiate the restoration job on the second server.

Related Posts:

Filegroup

The Best Way to Backup SQL Server

A CommVault Galaxy QiNetix Bug Experienced in Restoring SQL Server Database

How to Separate Data and Indexes of a Table into Different Files

How to Restore a Database Used By Web Site 24/7?