![MSSQL:将数据库恢复到备份集中的最新文件](https://linux22.com/image/626264/MSSQL%EF%BC%9A%E5%B0%86%E6%95%B0%E6%8D%AE%E5%BA%93%E6%81%A2%E5%A4%8D%E5%88%B0%E5%A4%87%E4%BB%BD%E9%9B%86%E4%B8%AD%E7%9A%84%E6%9C%80%E6%96%B0%E6%96%87%E4%BB%B6.png)
我有一个 MSSQL 2008R2 DB,处于简单恢复模式,并定期备份
BACKUP DATABASE MyDB to DISK = 'Z:\MyDB.cbak' WITH COMPRESSION
它会定期运行,在文件中创建多个备份集。我想恢复最新的备份。
手动执行此操作我可以运行RESTORE HEADERONLY from Disk='Z:\DougHub.cbak'
,然后找到要运行的最新位置RESTORE DATABASE MyDB from Disk='Z:\MyDB.cbak' with FILE = <some number>
,但我想自动执行此步骤,以便它始终恢复最新的备份。
如何创建一个 TSQL 命令来从备份文件恢复最新的备份?
答案1
类似这样的方法可能会奏效。我只是快速修改了我用于稍微不同目的的东西。
declare @dbname varchar(80),
@lastfull datetime,
@fullback varchar(1024),
@position int,
@SQL nvarchar(max)
set @dbname = 'YourDB'
select @lastfull = MAX(backup_finish_date)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name AND b.type = 'D'
WHERE d.database_id NOT IN (2, 3) and d.name=@dbname
SELECT @fullback = m.physical_device_name, @position = b.position
FROM msdb.dbo.backupmediafamily AS m INNER JOIN
msdb.dbo.backupset AS b ON m.media_set_id = b.media_set_id
and b.type='D' and b.database_name=@dbname
AND b.backup_finish_date=@lastfull
set @SQL = 'RESTORE DATABASE [' + @DBname + '] FROM DISK = N''' + @fullback + ''' WITH FILE = ' + convert(nvarchar,@position) + ', RECOVERY, NOUNLOAD, STATS = 10'
EXEC SP_EXECUTESQL @SQL
它会提取相关数据库的最后备份日期并为您填写路径和位置。