在事务中恢复多个数据库备份

Question

我编写了一个存储过程来恢复数据库备份集。 它需要两个参数 - 源目录和恢复目录。 该过程在源目录中查找所有 .bak 文件（递归地）并恢复所有数据库。

存储过程按预期工作，但有一个问题 - 如果我取消注释 try-catch 语句，该过程将终止并出现以下错误：

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

奇怪的部分是有时（不一致）即使发生错误也会完成恢复。 程序：

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

有人知道为什么会发生这种情况吗？

还有一个问题：交易码有用吗？ 即，如果有2个数据库需要恢复，那么如果第二次恢复失败，SQL Server是否会撤消其中一个数据库的恢复？

Answer 1

另外，如果您没有从文件列表中删除 NULL 记录（因为它被注释掉了），那么当您的循环从 0 开始时，它最终会在最后一次迭代中处理一个不存在的文件。

而不是 @index=0 而应该是 @index=1

或取消注释
从 file_path 为 NULL 的 @backup_files 中删除

Answer 2

我注意到的问题：

• 提交事务需要在里面
  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

Raj

Answer 3

这里的实际问题是，try 和 catch 只给出最后一个错误消息 3013“备份异常终止”，但不会给出触发 3013 错误的较低级别错误。

如果您执行备份命令（例如使用不正确的数据库名称），您将收到 2 个错误。
backup database unavailable_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.

如果您想了解备份在 try a catch 中失败的实际错误，则存储过程会屏蔽它。

现在，关于您的问题..我要做的是，当恢复成功时，我会立即删除 .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 块内，因此它必须位于该块之外。

不管怎样，我只是想我会添加我的想法，即使这个问题已经很老了，只是为了帮助处于同样情况的其他人。

Answer 4

本质上，发生的情况是需要恢复的文件之一出现问题，并且恢复过程抛出错误，但该错误还没有严重到足以中止进程。 这就是没有 try-catch 就没有问题的原因。 但是，添加 try-catch 会捕获严重性大于 10 的任何错误，因此控制流会切换到 catch 块，该块显示错误消息并中止过程。