3

I run the following scripts for a backup job. After the backup job completes, (xp_delete) deletes the old copy of full backup.

Typically, if the backup fails the BACKUP DATABASE statement on the 2nd line fails which breaks out of the entire script. Now the problem is that the backup job fails and xp_delete command still deletes the old copy of the backup. I don't have any other copy of the backups.

DECLARE @fname NVARCHAR(100) = N'V:\Backup\MYDB_backup_' + CONVERT(NVARCHAR(10), SYSDATETIME(), 112 ) + N'.bak'; 

BACKUP DATABASE [MYDB] TO DISK = @fname WITH COMPRESSION, STATS = 10 

DECLARE @backupSetId AS INT 
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name=N'MYDB' AND backup_set_id = ( SELECT MAX( backup_set_id ) FROM msdb..backupset WHERE database_name=N'MYDB' ); 

IF @backupSetId IS NULL BEGIN 
    RAISERROR( N'Verify failed. Backup information for database ''N'MYDB'' not found.', 16, 1 ) 
END 

RESTORE VERIFYONLY FROM DISK = @fname WITH FILE = @backupSetId

DECLARE @cutoff NVARCHAR(50) = CONVERT(NVARCHAR(25), DATEADD(HOUR, -20, SYSDATETIME()), 120 ); 

EXECUTE master.dbo.xp_delete_file 0, N'V:\Backup', N'bak', @cutoff 

Note: I am using SQL Server 2008R2 standard edition .

1

Raising an error does not necessarily stop the execution of the script and this is why your files are being deleted. See this Connect request for Microsoft's response to a similar question and this Question on StackOverflow for more details.

Overcoming this issue in your particular example is actually pretty simple by using program flow like so:

DECLARE @fname NVARCHAR(100) = N'V:\Backup\MYDB_backup_' + CONVERT(NVARCHAR(10), SYSDATETIME(), 112 ) + N'.bak'; 

BACKUP DATABASE [MYDB] TO DISK = @fname WITH COMPRESSION, STATS = 10 

DECLARE @backupSetId AS INT 
SELECT @backupSetId = position 
  FROM msdb..backupset 
 WHERE database_name=N'MYDB' 
   AND backup_set_id = ( SELECT MAX( backup_set_id ) 
                           FROM msdb..backupset 
                          WHERE database_name=N'MYDB' ); 

IF @backupSetId IS NULL 
BEGIN 
     RAISERROR( N'Verify failed. Backup information for database ''N'MYDB''' not found.', 16, 1 ) 
END 
ELSE
BEGIN

RESTORE VERIFYONLY FROM DISK = @fname WITH FILE = @backupSetId

DECLARE @cutoff NVARCHAR(50) = CONVERT(NVARCHAR(25), DATEADD(HOUR, -20, SYSDATETIME()), 120 ); 

EXECUTE master.dbo.xp_delete_file 0, N'V:\Backup', N'bak', @cutoff 
END

If you has a more complicated example I would recommend a try-catch block but I don't think that is needed here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.