sql-server – 如何恢复一批事务日志而不是一个一个

使用SQL 2008 Management studio,我们似乎必须逐个选择每个事务日志.有没有办法把它指向一个目录？

我正在考虑每天运行几次差异备份,这可能会抵消其中的一些,但是逐个通过几十个/几百个文件似乎非常耗时.编写代码来尝试编写脚本似乎与我们的核心能力相差甚远.

如果SQL Server Management Studio没有更快的方法,也许有第三方工具可用？

解决方法

但您可以在数据库MSDB(表备份集和相关)中找到有关SQL Server备份操作的所有信息.

以下是从备份生成还原数据库的SQL Server命令并应用从上次完整数据库备份执行的所有事务日志备份的脚本.
我认为它应该对你有帮助.

DECLARE @databaseName sysname
DECLARE @backupStartDate datetime
DECLARE @backup_set_id_start INT
DECLARE @backup_set_id_end INT

-- set database to be used
SET @databaseName = '<your_database_name_here>' 

SELECT @backup_set_id_start = MAX(backup_set_id) 
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D'

SELECT @backup_set_id_end = MIN(backup_set_id) 
FROM  msdb.dbo.backupset 
WHERE database_name = @databaseName AND type = 'D'
AND backup_set_id > @backup_set_id_start

IF @backup_set_id_end IS NULL SET @backup_set_id_end = 999999999

SELECT backup_set_id,'RESTORE DATABASE ' + @databaseName + ' FROM DISK = ''' 
               + mf.physical_device_name + ''' WITH NORECOVERY'
FROM    msdb.dbo.backupset b,msdb.dbo.backupmediafamily mf
WHERE    b.media_set_id = mf.media_set_id
           AND b.database_name = @databaseName
          AND b.backup_set_id = @backup_set_id_start
UNION
SELECT backup_set_id,'RESTORE LOG ' + @databaseName + ' FROM DISK = ''' 
               + mf.physical_device_name + ''' WITH NORECOVERY'
FROM    msdb.dbo.backupset b,msdb.dbo.backupmediafamily mf
WHERE    b.media_set_id = mf.media_set_id
           AND b.database_name = @databaseName
          AND b.backup_set_id >= @backup_set_id_start AND b.backup_set_id < @backup_set_id_end
          AND b.type = 'L'
UNION
SELECT 999999999 AS backup_set_id,'RESTORE DATABASE ' + @databaseName + ' WITH RECOVERY'
ORDER BY backup_set_id

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!