Error recovery in sql try block not caught
I wrote a stored procedure that restores the database from a given .bak file and scheduled this procedure in the Job Agent Agent Job. Now it sometimes happened that the .bak file was not available or was copied or checked by the antivirus, or God knows what else during my recovery process is causing the OS error (the file accessed by another process) (not always, but sometimes). this, I put a Restore statement in a try-catch block in a loop like below, assuming it might fix the problem, but it doesn't work. Below is my procedure:
DECLARE @Repeat INT
SET @Repeat = 1
WHILE (@Repeat = 1)
BEGIN
BEGIN TRY
IF DB_ID('MyDBName') IS NOT NULL
BEGIN
DROP DATABASE MyDBName
END
SET @File = @Path+@File --@File contains valid values, no problem in them
print @File
RESTORE FILELISTONLY FROM DISK = @File
--print 'Restore Filelist Successful'
RESTORE DATABASE BSG FROM DISK = @File
WITH REPLACE, MOVE 'ABC_Data' TO 'D:\MyDBName\MyDBName.mdf',
MOVE 'ABC_Log' TO 'D:\MyDBName\MyDBName_1.ldf'
SET @Repeat = 0
--print 'Restore Database Successful'
--print 'Move Successful'
END TRY
BEGIN CATCH
print 'Error Occured'
SET @Repeat = 1
END CATCH
END
Please note that my routines work fine in most cases 7/10, but sometimes when I see the job history I see the following error in the crash report:
Executed as user: NT AUTHORITY\SYSTEM. In Transaction [SQLSTATE 01000] (Message 0)
'*MyBackupDBFilePath*' [SQLSTATE 01000] (Message 0) Cannot open backup device
'*MyBackupDBFilePath*'. Operating system error 32(The process cannot access the file
because it is being used by another process.). [SQLSTATE 42000] (Error 3201).
The step failed.
I see that there was some problem while accessing my .bak file, but I don’t understand why my procedure doesn’t try to restore the db again (mainly why the error is not being caught by my trick that sets @repeat value to 1
source to share
I had problems with blocks TRY CATCH
not firing when errors occurred and this was due to errors that prevented the entire block from being executed. For example, if you have a try catch block to rollback a transaction in case of failure and encounters a non-existent column. This is not a parsing error, but it is a bug that prevents execution, and therefore it just completely fails. I had successful getting situations like this to disable the catch block if I include
SET XACT_ABORT ON
at the beginning of the script.
When SET XACT_ABORT is on, if the Transact-SQL statement generates a runtime error, the entire transaction is terminated and rolled back.
Hope this helps you. If not, it's still a good idea to use and we use it in our jobs that use blocks TRY CATCH
if a column is deleted or something.
source to share