我有一个MSSQL 2008R2数据库在SIMPLE恢复模式，定期备份
BACKUP DATABASE MyDB to DISK = 'Z:\MyDB.cbak' WITH COMPRESSION 这会定期运行，在文件中创build多个备份集。 我想恢复所做的最新备份。
通过手工操作，我可以RESTORE HEADERONLY from Disk='Z:\DougHub.cbak'运行RESTORE HEADERONLY from Disk='Z:\DougHub.cbak' ，然后RESTORE DATABASE MyDB from Disk='Z:\MyDB.cbak' with FILE = <some number> RESTORE HEADERONLY from Disk='Z:\DougHub.cbak'find运行RESTORE DATABASE MyDB from Disk='Z:\MyDB.cbak' with FILE = <some number>的最新位置。我想自动执行此步骤，以便始终恢复最近的备份。
如何创buildTSQL命令，从备份文件恢复最近的备份？
像这样的事情可能会诀窍。 我只是很快修改了一些我用于稍微不同的目的。
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
它会提取有问题的数据库的最后备份date，并为您填充path和位置。