Copying Backups using XP_CMDSHELL

,

This script is designed to backup the database, and copy it over the network to our backup server.  The requirement for this particular database is 7 days of data kept on the LAN.  The share on the backup server is mapped to the Y:\ drive.  The datename function is used over the datepart function for returning a string for ease of administration, particularly if non DBAs have to interpret the script.   The live version of this actually runs in a job, with the backup portion being separated from the copy, allowing us to back out based on results, of the backup.

--Backup databse MYDB to FullDevice.  
--The path name of full Device is:
--c:\backups\Full\Fulldevice.bak

BACKUP DATABASE [MYDB] TO [FullDevice] WITH  INIT ,  NOUNLOAD ,  NAME = N'FullBack',  NOSKIP ,  STATS = 10,  NOFORMAT 



--Declare @DY, this variable will hold the Day of the week string
Declare @DY  varchar(15)
--Sets the day of week using the dataname function with the dw parameter 
set @DY = (select datename(dw, getdate()) )

--Declare @Pdy, this variable will hold command line text
Declare @Pdy varchar(150)

--Create command line text based on results of @DY
SET @Pdy = (select    
case 
when @DY = 'Sunday' then 'COPY "c:\backups\Full\Fulldevice.bak" "Y:\Sunday.BAK"'
when @DY = 'Monday' then 'COPY "c:\backups\Full\Fulldevice.bak" "Y:\Monday.BAK"'
when @DY = 'Tuesday' then 'COPY "c:\backups\Full\Fulldevice.bak" "Y:\Tuesday.BAK"'
when @DY = 'Wednesday' then 'COPY "c:\backups\Full\Fulldevice.bak" "Y:\Wednesday.BAK"'
when @DY = 'Thursday' then 'COPY "c:\backups\Full\Fulldevice.bak" "Y:\Thursday.BAK"'
when @DY = 'Friday' then 'COPY "c:\backups\Full\Fulldevice.bak" "Y:\Friday.BAK"'
when @DY = 'Saturday' then 'COPY "c:\backups\Full\Fulldevice.bak" "Y:\Saturday.BAK"'
end)

--Execute command text held in @PDY
exec XP_CMDSHELL @Pdy

Rate

Share

Share

Rate