恢复数据库上的最新备份

如果您无法访问原始服务器并且您只有备份文件，那么您需要首先从备份文件中恢复标头以查看其中的内容。

您可以通过以下方式做到这一点：

create table #headers 
(
BackupName              nvarchar(128),
BackupDescription       nvarchar(255),
BackupType              smallint,
ExpirationDate          datetime,
Compressed              bit,
Position                smallint,
DeviceType              tinyint,
UserName                nvarchar(128),
ServerName              nvarchar(128),
DatabaseName            nvarchar(128),
DatabaseVersion         int,
DatabaseCreationDate    datetime,
BackupSize              numeric(20,0),
FirstLSN                numeric(25,0),
LastLSN                 numeric(25,0),
CheckpointLSN           numeric(25,0),
DatabaseBackupLSN       numeric(25,0),
BackupStartDate         datetime,
BackupFinishDate        datetime,
SortOrder               smallint,
[CodePage]              smallint,
UnicodeLocaleId         int,
UnicodeComparisonStyle  int,
CompatibilityLevel      tinyint,
SoftwareVendorId        int,
SoftwareVersionMajor    int,
SoftwareVersionMinor    int,
SoftwareVersionBuild    int,
MachineName             nvarchar(128),
Flags                   int,
BindingID               uniqueidentifier,
RecoveryForkID          uniqueidentifier,
Collation               nvarchar(128),
FamilyGUID              uniqueidentifier,
HasBulkLoggedData       bit,
IsSnapshot              bit,
IsReadOnly              bit,
IsSingleUser            bit,
HasBackupChecksums      bit,
IsDamaged               bit,
BeginsLogChain          bit,
HasIncompleteMetaData   bit,
IsForceOffline          bit,
IsCopyOnly              bit,
FirstRecoveryForkID     uniqueidentifier,
ForkPointLSN            numeric(25,0),
RecoveryModel           nvarchar(60),
DifferentialBaseLSN     numeric(25,0),
DifferentialBaseGUID    uniqueidentifier,
BackupTypeDescription   nvarchar(60),
BackupSetGUID           uniqueidentifier,
CompressedBackupSize    bigint,
Containment             tinyint
);
go

insert into #headers exec('restore headeronly from disk=''c:\DEV\UnitTest.bak''');
go 

您现在有一个临时表（#headers），其中包含该设备文件中包含的所有备份的详细信息。

为了找到最新的完整数据库备份，我们仅筛选完整数据库备份 ( backupType=1)，然后在设备中查找最新的备份，这将是具有最大 LSN 的备份。这为我们提供了位置值，该值在RESTORE命令中用于向 SQL Server 指定我们要恢复的备份：

declare @filepos int;
select top 1 @filepos=Position from #headers where BackupType=1 order by lastlsn desc;
restore database unittest from Disk=N'c:\DEV\UnitTest.bak' with file=@filepos, recovery, replace;

正如您所说，您仍然可以访问原始服务器，然后我们可以查询 SQL Server 用于存储此信息的 msdb 中的表。

以下查询假设您在原始服务器上的 SQL 会话中运行它们。如果您不允许这样做，那么您需要考虑使用链接服务器和 4 部分命名。

有 2 种方法可以指定要查看的备份集。一种是通过备份文件的路径，另一种是通过引用备份设备上的名称。我已将两者都包含在此查询中，并附有一些注释，说明要注释掉的行取决于您的操作方式。

这将生成一个RESTORE可以在单独的 SSMS 会话中运行的语句。我还在底部提供了一个示例，说明如何自动执行此操作，尽管您需要事先进行一些测试以确保它正在执行您想要的操作。

declare @filepos int, @dbname varchar(50), @devname varchar(50)

/*
dbname is the name of the database you want to restore. Need this, as it's possible for backups of more than one database to be present in the same backup file or device

devname is either the name of the backup device, or the path to the .bak file
*/

select @dbname='Unittest', @devname='c:\DEV\unittest.bak'

select @filepos=max(c.position) 
from 
msdb.dbo.backupmediafamily a 
inner join msdb.dbo.backupmediaset b on a.media_set_id=b.media_set_id
inner join msdb.dbo.backupset c on b.media_set_id=c.media_set_id
where 
a.physical_device_name=@devname  --use this line if you know the path to the backup
--a.logical_device_name=@devname --this line if you only know a device name.
and c.type='D' and c.database_name=@dbname

--This will build the restore statement if you know the device name
select 'restore database ['+@dbname+'] from '+@devname+' with file='+cast(@filepos as varchar(4))+', recovery, replace' 

--This will build the restore statement if you know the backup file path
select 'restore database ['+@dbname+'] from disk=N'''+@devname+''' with file='+cast(@filepos as varchar(4))+', recovery, replace' 

/*
This section only if feeling brave and confident, and you've checked the output previously, as it will execute whatever the generated restore command is, so this could cause a major problem if not checked first, or even overwrite production if run on the wrong box.
*/

declare @restore_cmd varchar(250)
select @restore = 'restore database ['+@dbname+'] from disk=N'''+@devname+''' with file='+cast(@filepos as varchar(4))+', recovery, replace' 
exec(@restore)

这应该给你一个开始：

SELECT 
  s.name, 
  s.backup_start_date, 
  s.backup_finish_date, 
  mf.physical_device_name,
  s.first_lsn, s.last_lsn, s.database_backup_lsn
FROM 
  msdb.dbo.backupset AS s
INNER JOIN 
  msdb.dbo.backupmediaset AS ms
  ON s.media_set_id = ms.media_set_id
INNER JOIN 
  msdb.dbo.backupmediafamily AS mf
  ON ms.media_set_id = mf.media_set_id
WHERE 
  s.database_name = N'UnitTest' 
ORDER BY 
  s.backup_finish_date DESC;