I created a job Copy backups to M drive which copies backup files from one folder to another, this job runs ok, The only problem is the job does not fail if there are no backups to copy and also if the target
locatioin folder on the M drive or the M drigve it self is not available.
I would like the job to fail if there are no backup files to copy and also if the Target drive is unavailable
Any ideas...
Thanks
USE master
declare @sql varchar(2000)
set @sql = 'copy "D:\Program Files\Microsoft SQL Server\MSSQL\Backup\Sam\SAM.BAK" "M:\Program Files\Microsoft SQL Server\MSSQL\Backup\Sam\SAM_' + CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4)) +
RIGHT('0' + CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR(2)),2) + RIGHT('0' + CAST(DATEPART(DAY,GETDATE()) AS VARCHAR(2)),2) + '.BAK"'
exec master..xp_cmdshell @sql
set @sql = 'copy "D:\Program Files\Microsoft SQL Server\MSSQL\Backup\master\master.bak" "M:\Program Files\Microsoft SQL Server\MSSQL\Backup\master\master_' + CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4)) +
RIGHT('0' + CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR(2)),2) + RIGHT('0' + CAST(DATEPART(DAY,GETDATE()) AS VARCHAR(2)),2) + '.BAK"'
exec master..xp_cmdshell @sql
set @sql = 'copy "D:\Program Files\Microsoft SQL Server\MSSQL\Backup\msdb\msdb.ba
View Complete Post