Restoring multiple database backups in a transaction

Question

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.

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:

error_number = 3013  
error_severity = 16  
error_state = 1  
error_message = DATABASE is terminating abnormally.

The weird part is sometimes (it is not consistent) the restore is done even if the error occurs. The procedure:

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) <> '\') set @source_directory = @source_directory + '\'
        if(right(@restore_directory, 1) <> '\') 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 < @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

Does anybody have any ideas why this might be happening?

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?

Answer 1

Also, if you are not removing the NULL records from your file list (since its commented out) then with your loop starting at 0 it ends up processing a non-existant file for its last iteration.

Instead of @index=0 instead it should be @index=1

or uncomment out the delete from @backup_files where file_path IS NULL

Answer 2

The actual problem here is that the try and catch only gives you the last error message 3013 "backup terminating abnormally", but does not give you the lower level error for the reason the 3013 error was triggered.

If you execute a backup command such as with an incorrect databasename, you will get 2 errors. backup database incorrect_database_name to disk = 'drive:\path\filename.bak'

Msg 911, Level 16, State 11, Line 1
Could not locate entry in sysdatabases for database 'incorrect_database_name'. No entry found with that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

If you want to know the actual error for why you backup is failing within a try a catch, the stored procedure is masking it.

Now, on to your question.. what I would do is when a restore succeeds, I would immediately delete or move the .bak to a new location, thereby removing it from the directory you stated in your parameter. Upon a failure, your catch statement can contain a GOTO that takes you back to before the BEGIN TRY and starts executing where it left off because it will not recursively detect the files you have moved from the directory.

RUN_AGAIN:
BEGIN TRY
RECURSIVE DIR FOR FILENAMES
RESTORE DATABASE...
ON SUCCEED, DELETE .BAK FILE
END TRY
BEGIN CATCH
ON FAILURE, MOVE .BAK to A SAFE LOCATION FOR LATER ANALYSIS
GOTO RUN_AGAIN
END CATCH

I'm not saying it is pretty, but it will work. You cannot put a GOTO reference within a TRY/CATCH block, so it has to be outside of it.

Anyway, I just thought I would add my thoughts to this even though the question is old, just to help out others in the same situation.

Answer 3

Essentially, what was happening was that one of the files that needed to be restored had a problem, and the restore process was throwing an error, but the error is not severe enough to abort the proc. That is the reason there is no problem without the try-catch. However, adding the try-catch traps any error with severity greater than 10, and therefore the control flow switches to the catch block which displays the error messages and aborts the proc.

Answer 4

Problems I noticed:

• Commit Transaction needs to be inside the BEGIN TRY....END TRY block
• Cursor will not get closed or deallocated if an error is encountered and control goes to BEGIN CATCH...END CATCH block

Try this modified code. It will demonstrate that your code is working fine..

ALTER proc usp_restore_databases
(
    @source_directory varchar(1000),
    @restore_directory varchar(1000)
)
as
begin 
    declare @number_of_backup_files int

  begin transaction
  begin try
    print 'Entering TRY...'
--     step 0: Initial validation

        if(right(@source_directory, 1) <> '\') set @source_directory = @source_directory + '\'
        if(right(@restore_directory, 1) <> '\') 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 < @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
print @index

              --  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 'Entering Catch...'
        print error_message()
      rollback transaction
      return
  end catch

  commit transaction

end

Raj