title: Restoring multiple database backups in a transaction
body: <p>I wrote a stored procedure that restores as set of the database backups. It takes two parameters - a source directory and a restore directory. The procedure looks for all .bak files in the source directory (recursively) and restores all the databases.  </p>
  
  <p>The stored procedure works as expected, but it has one issue - if I uncomment the try-catch statements, the procedure terminates with the following error: </p>
  
  <pre><code>error_number = 3013  
  error_severity = 16  
  error_state = 1  
  error_message = DATABASE is terminating abnormally.
  </code></pre>
  
  <p>The weird part is sometimes (it is not consistent) the restore is done even if the error occurs. The procedure:</p>
  
  <pre><code>create proc usp_restore_databases
  (
      @source_directory varchar(1000),
      @restore_directory varchar(1000)
  )
  as
  begin       
  
      declare @number_of_backup_files int
  
  --  begin transaction
  --  begin try
  
      -- step 0: Initial validation
  
          if(right(@source_directory, 1) &lt;&gt; '\') set @source_directory = @source_directory + '\'
          if(right(@restore_directory, 1) &lt;&gt; '\') set @restore_directory = @restore_directory + '\'
  
      -- step 1: Put all the backup files in the specified directory in a table -- 
  
          declare @backup_files table ( file_path varchar(1000))
  
          declare @dos_command varchar(1000)
          set @dos_command = 'dir ' + '"' + @source_directory + '*.bak" /s/b'
  
          /* DEBUG */ print @dos_command
  
          insert into @backup_files(file_path) exec xp_cmdshell  @dos_command
  
          delete from @backup_files where file_path IS NULL
  
          select @number_of_backup_files = count(1) from @backup_files
  
          /* DEBUG */ select * from @backup_files
          /* DEBUG */ print @number_of_backup_files
  
      -- step 2: restore each backup file --
  
          declare backup_file_cursor cursor for select file_path from @backup_files
          open  backup_file_cursor
  
          declare @index int; set @index = 0
          while(@index &lt; @number_of_backup_files)
          begin
  
  
              declare @backup_file_path varchar(1000)
              fetch next from backup_file_cursor into @backup_file_path
  
              /* DEBUG */ print @backup_file_path
  
              -- step 2a: parse the full backup file name to get the DB file name.    
              declare @db_name varchar(100)
  
              set @db_name = right(@backup_file_path, charindex('\', reverse(@backup_file_path)) -1)  -- still has the .bak extension
              /* DEBUG */ print @db_name
  
              set @db_name = left(@db_name, charindex('.', @db_name) -1)          
              /* DEBUG */ print @db_name
  
              set @db_name = lower(@db_name)
              /* DEBUG */ print @db_name
  
              -- step 2b: find out the logical names of the mdf and ldf files
              declare @mdf_logical_name varchar(100),
                      @ldf_logical_name varchar(100)
  
              declare @backup_file_contents table 
              (
                  LogicalName nvarchar(128),
                  PhysicalName nvarchar(260),
                  [Type] char(1),
                  FileGroupName nvarchar(128),
                  [Size] numeric(20,0),
                  [MaxSize] numeric(20,0),
                  FileID bigint,
                  CreateLSN numeric(25,0),
                  DropLSN numeric(25,0) NULL,
                  UniqueID uniqueidentifier,
                  ReadOnlyLSN numeric(25,0) NULL,
                  ReadWriteLSN numeric(25,0) NULL,
                  BackupSizeInBytes bigint,
                  SourceBlockSize int,
                  FileGroupID int,
                  LogGroupGUID uniqueidentifier NULL,
                  DifferentialBaseLSN numeric(25,0) NULL,
                  DifferentialBaseGUID uniqueidentifier,
                  IsReadOnly bit,
                  IsPresent bit 
              )
  
              insert into @backup_file_contents 
              exec ('restore filelistonly from disk=' + '''' + @backup_file_path + '''')
  
              select @mdf_logical_name = LogicalName from @backup_file_contents where [Type] = 'D'
              select @ldf_logical_name = LogicalName from @backup_file_contents where [Type] = 'L'
  
              /* DEBUG */ print @mdf_logical_name + ', ' + @ldf_logical_name
  
              -- step 2c: restore
  
              declare @mdf_file_name varchar(1000),
                      @ldf_file_name varchar(1000)
  
              set @mdf_file_name = @restore_directory + @db_name + '.mdf'
              set @ldf_file_name = @restore_directory + @db_name + '.ldf'
  
              /* DEBUG */ print   'mdf_logical_name = ' + @mdf_logical_name + '|' +
                                  'ldf_logical_name = ' + @ldf_logical_name + '|' +
                                  'db_name = ' + @db_name + '|' +
                                  'backup_file_path = ' + @backup_file_path + '|' +
                                  'restore_directory = ' + @restore_directory + '|' +
                                  'mdf_file_name = ' + @mdf_file_name + '|' +
                                  'ldf_file_name = ' + @ldf_file_name
  
  
              restore database @db_name from disk = @backup_file_path 
              with
                  move @mdf_logical_name to @mdf_file_name,
                  move @ldf_logical_name to @ldf_file_name
  
              -- step 2d: iterate
              set @index = @index + 1
  
          end
  
          close backup_file_cursor
          deallocate backup_file_cursor
  
  --  end try
  --  begin catch
  --        print error_message()
  --      rollback transaction
  --      return
  --  end catch
  --
  --  commit transaction
  
  end
  </code></pre>
  
  <p>Does anybody have any ideas why this might be happening? </p>
  
  <p>Another question: is the transaction code useful ? i.e., if there are 2 databases to be restored, will SQL Server undo the restore of one database if the second restore fails?</p>