我有一个sql Server数据库,每隔10分钟备份一次事务日志,并提供一夜间完整备份.
使用sql 2008 Management studio,我们似乎必须逐个选择每个事务日志.有没有办法把它指向一个目录？
我正在考虑每天运行几次差异备份,这可能会抵消其中的一些,但是逐个通过几十个/几百个文件似乎非常耗时.编写代码来尝试编写脚本似乎与我们的核心能力相差甚远.
解决方法
没有办法在sql Server Management Studio中指定要还原的一堆事务日志备份(oк文件夹).
但您可以在数据库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
