TSQL to copy a most recent sql server backup to a new location

In this blog I would like to post the TSQL script that I have written and am using to move a most recent sql server backup from one location to another. I am using the script inside a sqlagent job that runs when the backup exec job is done backing up the bak file to tape.  Please note that the command I am using is the robocopy for copying the file and I am also using the /MOV switch which copies the backup file to the destination folder and then deletes it from the source location. I am using this script as a temporary fix for shortage of storage on one of our staging database servers. Once I have my LUN expanded I will disable this job.

The script requires the xp_cmdshell to be enabled so I am enabling the show advanced options to enable the xp_cmdshell and I am disabling it right after the script execution is done.


EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
declare @date datetime
declare @dbname nvarchar(40)
DECLARE @cmd nvarchar(4000)
DECLARE @srcbkpLocation nvarchar(4000)
DECLARE @destbkpLocation nvarchar(4000)
DECLARE @bkpFileName nvarchar(4000)
DECLARE @bkpFileName1 nvarchar(4000)
set @destbkpLocation='\\Destserver\FULL\Backups\DBNameFolder\'
SELECT top 1 @date=max(bs.backup_finish_date),@dbname=bs.database_name,
@bkpFileName=bms.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms
ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s
ON bs.database_name = s.name
WHERE
s.dbid IN (6) AND
bs.type = 'D'
order by bs.backup_finish_date desc
set @srcbkpLocation=(select substring(@bkpFileName,1,LEN(@bkpFileName) - Charindex('\',Reverse(@bkpFileName))+1))
set @bkpFileName1=(Select Substring(@bkpFileName,LEN(@bkpFileName) - Charindex('\',Reverse(@bkpFileName))+2,LEN(@bkpFileName)))

SET @cmd = 'robocopy '+@srcbkpLocation+' '+@destbkpLocation+' '+@bkpFileName1+' '+'/MOV'
print @cmd
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

 

Hope this helps!

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s