SQL Server误删表恢复 -云博客

SQL Server误删表恢复

# 数据库 2024-05-05 14:32 0 29 来源：云博客

SQL Server
完全恢复模式 下恢复误删除的表，进行
精准恢复

1. 找出被删除的表名(无schema，能找到schema的分享下)、object_id、表所在数据库、删除人、删除时间等

declare @database_name varchar(200),@type varchar(2),@pass_hours int,select @database_name='AdventureWorks2014',@pass_hours=-48declare @file_path sql_variantselect @file_path=value from fn_trace_getinfo(0) where property=2 and traceid=1SELECT max(gt.HostName) as 'LoginHostName',max(gt.ApplicationName) as 'ApplicationName',max(gt.LoginName) as 'LoginName',min(gt.StartTime) as 'MinStartTime',max(gt.StartTime) as 'MaxStartTime',gt.ObjectID,max(gt.ServerName) as 'DBServerName',max(gt.ObjectName) as 'ObjectName',max(gt.DatabaseName) as 'DatabaseName',max(sv.subclass_name) as 'subclass_name',max(e.name) as 'OperationType'FROM fn_trace_gettable(convert(varchar(2000),@file_path), DEFAULT) gt LEFT JOIN sys.trace_subclass_values sv ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype] INNER JOIN sys.trace_events e ON gt.[eventclass] = e.[trace_event_id] INNER JOIN sys.trace_categories c ON e.[category_id] = c.[category_id] WHERE StartTime>=dateadd(HOUR,@pass_hours,getdate()) and StartTime<=getdate()and [eventclass]=47 --drop talbe/indexand databasename=@database_nameand subclass_name='U'group by gt.ObjectID

2. 通过删除时间确定相关信息被记录在哪个日志文件上

select * from msdb.dbo.backupmediafamily where media_set_id=(select top 1 media_set_id from msdb.dbo.backupset where database_name=@database_nameand backup_finish_date>=@max_start_timeand type='L' order by backup_finish_date)

3. 从日志文件提取lsn相关信息

declare @lockinfo varchar(100)set @lockinfo='%SCH_M OBJECT%:'+@ObjectID+':%',[Begin Time]select [Transaction id],'lsn:0x'+[Current LSN] from fn_dump_dblog (        NULL, NULL, N'DISK', 1, @backupfile,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) as awhere convert(datetime,[Begin Time])between dateadd(ss,-5,@min_start_time) and dateadd(ss,5,@max_start_time)and [Transaction Name]='DROPOBJ'and exists(select 1 from fn_dump_dblog (        NULL, NULL, N'DISK', 1, @backupfile,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,        DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)        where [Transaction id]=a.[Transaction id] and [Lock Information] like @lockinfo)

4. 恢复所需相关信息