I have sample T-SQL code below, BEGIN TRY
BEGIN TRY
BEGIN TRANSACTION
RESTORE FILELISTONLY
FROM DISK = 'D:\Backup\MyDatabase.bak'
GO
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
ALTER DATABASE MyDatabase
SET RECOVERY Simple
----Restore Database
RESTORE DATABASE MyDatabase
FROM DISK = 'D:\Backup\MyDatabase.bak'
WITH MOVE 'MyDatabase' TO 'C:\DataFolder\MyDatabase.mdf',
MOVE 'MyDatabase_log' TO 'C:\DataFolder\MyDatabase_log.ldf'
ALTER DATABASE MyDatabase SET MULTI_USER
GO
USE [MyDatabase]
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'user1')
CREATE USER [user1] FOR LOGIN [user1] WITH DEFAULT_SCHEMA=[user1]
GO
EXEC sp_addrolemember 'db_owner',N'user1'
GO
COMMIT
--Send email on successfull execution of script
USE [msdb];
EXEC sp_send_dbmail @profile_name='My Mail Profile',
@recipients='myemail@mydomain.org',
@subject='Test subject',
@query = '',
@body='Tested successfully.'
END TRY
BEGIN CATCH
--If there is any error in the script, roll back the transaction and also send an error report email notification
ROLLBACK
USE [msdb];
EXEC sp_send_dbmail @profile_name='My Mail Profile',
@recipients='myemail@mydomain.org',
@subject='Test error',
@body= ERROR_MESSAGE()
END CATCH
That code throws the following errors,
Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'D:\Backup\MyDatabase.bak'.
Msg 102, Level 15, State 1, Line 10 Incorrect syntax near 'BEGIN'.
Msg 102, Level 15, State 1, Line 17 Incorrect syntax near ')'.
But if i move the code out of the BEGIN TRY and BEGIN TRANSACTION blocks, it executes successfully with out any errors.
All i want to achieve is to execute that T-SQL script and if it fails i notify i send a notification to some email account with the error message that made the code fail in the email body.
Any suggestions on how i can better achieve this are very welcome.
TRY/CATCH blocks can only be used inside the scope a single request (batch). You are trying to span a TRY block over several batches. GO
is the default batch delimiter, fyi.
You also expect to support transactions and rollback for some operations that do not support transactions, like RESTORE
:
RESTORE is not allowed in an explicit or implicit transaction.
You would have a much better chance at handling this kind of problems at a higher level, in an app or even is a script. Note that sqlcmd
supports the -b
option to break on first error, and you can use the exit code (%ERRORLEVEL%
).