title: How to use SQL Server Transaction inside T-SQL TRY...CATCH block
body: <p>I have sample T-SQL code below, BEGIN TRY</p>
  
  <pre><code>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
  </code></pre>
  
  <p>That code throws the following errors, </p>
  
  <ol>
  <li><p>Msg 102, Level 15, State 1, Line 4
  Incorrect syntax near 'D:\Backup\MyDatabase.bak'.</p></li>
  <li><p>Msg 102, Level 15, State 1, Line 10
  Incorrect syntax near 'BEGIN'.</p></li>
  <li><p>Msg 102, Level 15, State 1, Line 17
  Incorrect syntax near ')'.</p></li>
  </ol>
  
  <p>But if i move the code out of the BEGIN TRY and BEGIN TRANSACTION blocks, it executes successfully with out any errors. </p>
  
  <p>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.</p>
  
  <p>Any suggestions on how i can better achieve this are very welcome.</p>