Technical Article

Move End of Month file from source to destination

,

Anyone who is using this script can schedule this on 1st of every month for moving full backup file from source to destination location. The name of the source file is considered as "2014_05_31". You can change the source format according to your needs.

DECLARE @physical_device_name varchar(4000),
        @cmd_str varchar(4000)

SELECT  @physical_device_name=physical_device_name
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'database_name' and  type='D' and name=REPLACE(CAST(DATEADD("d", -DAY(GETDATE()), DATEADD("M", 0, GETDATE())) as DATE),'-','_')
ORDER BY backup_finish_date DESC

select @physical_device_name
select @cmd_str='MOVE /Y '+@physical_device_name+' '+'\\destination\folder\location\'
select @cmd_str

exec xp_cmdshell @cmd_str

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (1)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (1)

You rated this post out of 5. Change rating

Related content