SQL Server 灾难恢复31天之第3天:在还原数据库时确定需要哪些备份文件
Posted on 2013-01-10 22:19 nzperfect 阅读(867) 评论(0) 编辑 收藏 举报说明:灾难恢复系列的文章是由 Robert Davis 写的,发布在SQLSoldier, 个人认为挺不错的,所以根据自己的理解,边测试边整理,并非直接翻译,如有不准确,欢迎指正。
本篇进入数据库灾难恢复第三篇,主要提供一个脚本,该脚本通过读取msdb库的一些备份记录信息,自动得到指定数据库最后一个全备+最后一个差异+这之后的全部Log备份。
场景:
假设你的数据库在某一个时间点被误删除了一个表或者多个表数据,之后发现这个问题,现在需要把数据恢复,那么你可能要先还原一个全备,再加一个差异备(如果有),然后再还原一堆Log备份,如果你5分钟一个Log备份,那么你如果快速的确定这些文件名称及所在位置,以快速还原呢?
再有,如果你想知道一条数据在什么时间被改的,那么你也可能要还原一这个数据库的备份,在还原Log备份时,每还原一个完成后查看数据是否被修改,这时候也需要快速确定要还原哪些备份文件以及顺序。
脚本:以下为Rober Davis提供的一个脚本(下载),可以快速返回恢复数据库所需要的全部文件及目录,在使用时,需要指定数据库名称:即:Set @DBName = N'你的数据库名称';
Declare @DBName sysname, @DBBackupLSN numeric(25, 0); Declare @Baks Table ( BakID int identity(1, 1) not null primary key, backup_set_id int not null, media_set_id int not null, first_family_number tinyint not null, last_family_number tinyint not null, first_lsn numeric(25, 0) null, last_lsn numeric(25, 0) null, database_backup_lsn numeric(25, 0) null, backup_finish_date datetime null, type char(1) null, family_sequence_number tinyint not null, physical_device_name nvarchar(260) not null, device_type tinyint null) Set NoCount On; -- Set the name of the database you want to restore Set @DBName = N''; -- Get the most recent full backup with all backup files Insert Into @Baks (backup_set_id, media_set_id, first_family_number, last_family_number, first_lsn, last_lsn, database_backup_lsn, backup_finish_date, type, family_sequence_number, physical_device_name, device_type) Select Top(1) With Ties B.backup_set_id, B.media_set_id, B.first_family_number, B.last_family_number, B.first_lsn, B.last_lsn, B.database_backup_lsn, B.backup_finish_date, B.type, BF.family_sequence_number, BF.physical_device_name, BF.device_type From msdb.dbo.backupset As B Inner Join msdb.dbo.backupmediafamily As BF On BF.media_set_id = B.media_set_id And BF.family_sequence_number Between B.first_family_number And B.last_family_number Where B.database_name = @DBName And B.is_copy_only = 0 And B.type = 'D' And BF.physical_device_name Not In ('Nul', 'Nul:') Order By backup_finish_date desc, backup_set_id; -- Get the lsn that the differential backups, if any, will be based on Select @DBBackupLSN = database_backup_lsn From @Baks; -- Get the most recent differential backup based on that full backup Insert Into @Baks (backup_set_id, media_set_id, first_family_number, last_family_number, first_lsn, last_lsn, database_backup_lsn, backup_finish_date, type, family_sequence_number, physical_device_name, device_type) Select Top(1) With Ties B.backup_set_id, B.media_set_id, B.first_family_number, B.last_family_number, B.first_lsn, B.last_lsn, B.database_backup_lsn, B.backup_finish_date, B.type, BF.family_sequence_number, BF.physical_device_name, BF.device_type From msdb.dbo.backupset As B Inner Join msdb.dbo.backupmediafamily As BF On BF.media_set_id = B.media_set_id And BF.family_sequence_number Between B.first_family_number And B.last_family_number Where B.database_name = @DBName And B.is_copy_only = 0 And B.type = 'I' And BF.physical_device_name Not In ('Nul', 'Nul:') And B.database_backup_lsn = @DBBackupLSN Order By backup_finish_date Desc, backup_set_id; -- Get the last LSN included in the differential backup, -- if one was found, or of the full backup Select Top 1 @DBBackupLSN = last_lsn From @Baks Where type In ('D', 'I') Order By BakID Desc; -- Get first log backup, if any, for restore, where -- last_lsn of previous backup is >= first_lsn of the -- log backup and <= the last_lsn of the log backup Insert Into @Baks (backup_set_id, media_set_id, first_family_number, last_family_number, first_lsn, last_lsn, database_backup_lsn, backup_finish_date, type, family_sequence_number, physical_device_name, device_type) Select Top(1) With Ties B.backup_set_id, B.media_set_id, B.first_family_number, B.last_family_number, B.first_lsn, B.last_lsn, B.database_backup_lsn, B.backup_finish_date, B.type, BF.family_sequence_number, BF.physical_device_name, BF.device_type From msdb.dbo.backupset B Inner Join msdb.dbo.backupmediafamily As BF On BF.media_set_id = B.media_set_id And BF.family_sequence_number Between B.first_family_number And B.last_family_number Where B.database_name = @DBName And B.is_copy_only = 0 And B.type = 'L' And BF.physical_device_name Not In ('Nul', 'Nul:') And @DBBackupLSN Between B.first_lsn And B.last_lsn Order By backup_finish_date, backup_set_id; -- Get last_lsn of the first log backup that will be restored Set @DBBackupLSN = Null; Select @DBBackupLSN = Max(last_lsn) From @Baks Where type = 'L'; -- Recursively get all log backups, in order, to be restored -- first_lsn of the log backup = last_lsn of the previous log backup With Logs As (Select B.backup_set_id, B.media_set_id, B.first_family_number, B.last_family_number, B.first_lsn, B.last_lsn, B.database_backup_lsn, B.backup_finish_date, B.type, BF.family_sequence_number, BF.physical_device_name, BF.device_type, 1 As LogLevel From msdb.dbo.backupset B Inner Join msdb.dbo.backupmediafamily As BF On BF.media_set_id = B.media_set_id And BF.family_sequence_number Between B.first_family_number And B.last_family_number Where B.database_name = @DBName And B.is_copy_only = 0 And B.type = 'L' And BF.physical_device_name Not In ('Nul', 'Nul:') And B.first_lsn = @DBBackupLSN Union All Select B.backup_set_id, B.media_set_id, B.first_family_number, B.last_family_number, B.first_lsn, B.last_lsn, B.database_backup_lsn, B.backup_finish_date, B.type, BF.family_sequence_number, BF.physical_device_name, BF.device_type, L.LogLevel + 1 From msdb.dbo.backupset B Inner Join msdb.dbo.backupmediafamily As BF On BF.media_set_id = B.media_set_id And BF.family_sequence_number Between B.first_family_number And B.last_family_number Inner Join Logs L On L.database_backup_lsn = B.database_backup_lsn Where B.database_name = @DBName And B.is_copy_only = 0 And B.type = 'L' And BF.physical_device_name Not In ('Nul', 'Nul:') And B.first_lsn = L.last_lsn) Insert Into @Baks (backup_set_id, media_set_id, first_family_number, last_family_number, first_lsn, last_lsn, database_backup_lsn, backup_finish_date, type, family_sequence_number, physical_device_name, device_type) Select backup_set_id, media_set_id, first_family_number, last_family_number, first_lsn, last_lsn, database_backup_lsn, backup_finish_date, type, family_sequence_number, physical_device_name, device_type From Logs Option(MaxRecursion 0); -- Select out just the columns needed to script restore Select RestoreOrder = Row_Number() Over(Partition By family_sequence_number Order By BakID), RestoreType = Case When type In ('D', 'I') Then 'Database' When type = 'L' Then 'Log' End, DeviceType = Case When device_type in (2, 102) Then 'Disk' When device_type in (5, 105) Then 'Tape' End, PhysicalFileName = physical_device_name From @Baks Order By BakID; Set NoCount Off;
这是我的数据库返回的结果:
总结:
这个脚本可以快速准确的找到这些文件,并排好顺序,你可以再次加工,直接输出restore database/log 这样的脚本,生成满足你的个性化定制脚本,比如再加上stop at/standby等等。
另外,提供另外一个比较强大的自动化还原脚本.
作者:nzperfect
出处:http://www.cnblogs.com/nzperfect/
引用或者转载本BLOG的文章请注明原作者和出处,并保留原文章中的版权信息。