Восстановление нескольких резервных копий базы данных в транзакции

По сути, в одном из файлов, которые нужно было восстановить, была проблема, и процесс восстановления выдавал ошибку, но эта ошибка недостаточно серьезна, чтобы прервать процедуру. Вот почему нет проблем без try-catch. Однако добавление try-catch перехватывает любую ошибку с серьезностью выше 10, и поэтому поток управления переключается на блок catch, который отображает сообщения об ошибках и прерывает процесс.

Кроме того, если вы не удаляете записи NULL из своего списка файлов (поскольку он закомментирован), тогда ваш цикл, начинающийся с 0, заканчивается обработкой несуществующего файла для его последней итерации.

Вместо @index=0 должно быть @index=1

или раскомментируйте delete from @backup_files where file_path IS NULL

Проблемы, которые я заметил:

• Фиксация транзакции должна находиться внутри блока BEGIN TRY....END TRY.
• Курсор не будет закрыт или освобожден, если произойдет ошибка и управление перейдет к блоку BEGIN CATCH...END CATCH

Попробуйте этот модифицированный код. Это продемонстрирует, что ваш код работает нормально.

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

Радж

Фактическая проблема здесь заключается в том, что попытка и перехват дает вам только последнее сообщение об ошибке 3013 «резервное копирование завершается ненормально», но не дает вам ошибку более низкого уровня по причине, по которой была вызвана ошибка 3013.

Если вы выполните команду резервного копирования, например, с неправильным именем базы данных, вы получите 2 ошибки. резервная копия базы данных некорректное_имя_базы_данных на диск = 'диск:\путь\имя_файла.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.

Если вы хотите узнать фактическую ошибку, из-за которой происходит сбой резервного копирования при попытке поймать, хранимая процедура маскирует ее.

Теперь, что касается вашего вопроса. Что бы я сделал, так это после успешного восстановления я бы немедленно удалил или переместил .bak в новое место, тем самым удалив его из каталога, указанного вами в вашем параметре. В случае сбоя ваш оператор catch может содержать GOTO, который возвращает вас к состоянию до BEGIN TRY и начинает выполнение с того места, где он остановился, поскольку он не будет рекурсивно обнаруживать файлы, которые вы переместили из каталога.

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

Я не говорю, что это красиво, но это будет работать. Вы не можете поместить ссылку GOTO в блок TRY/CATCH, поэтому она должна быть вне его.

В любом случае, я просто подумал, что добавлю к этому свои мысли, хотя вопрос старый, просто чтобы помочь другим в такой же ситуации.