Copying Backups using XP_CMDSHELL

--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