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

1.  找出被删除的表名(无schema,需要从日志中分析进行提取,后续完善)、object_id、表所在数据库、删除人、删除时间等

  1. declare @database_name varchar(200),@type varchar(2),@pass_hours int,
  2. select @database_name='AdventureWorks2014',@pass_hours=-48
  3. declare @file_path sql_variant
  4. select @file_path=value from fn_trace_getinfo(0where property=2 and traceid=1
  5. SELECT max(gt.HostName) as 'LoginHostName'
  6. ,max(gt.ApplicationName) as 'ApplicationName'
  7. ,max(gt.LoginName) as 'LoginName'
  8. ,min(gt.StartTime) as 'MinStartTime'
  9. ,max(gt.StartTime) as 'MaxStartTime'
  10. ,gt.ObjectID
  11. ,max(gt.ServerName) as 'DBServerName'
  12. ,max(gt.ObjectName) as 'ObjectName'
  13. ,max(gt.DatabaseName) as 'DatabaseName'
  14. ,max(sv.subclass_name) as 'subclass_name'
  15. ,max(e.name) as 'OperationType'
  16. FROM fn_trace_gettable(convert(varchar(2000),@file_path), DEFAULT) gt 
  17. LEFT JOIN sys.trace_subclass_values sv 
  18. ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype] 
  19. INNER JOIN sys.trace_events e 
  20. ON gt.[eventclass] = e.[trace_event_id] 
  21. INNER JOIN sys.trace_categories c 
  22. ON e.[category_id] = c.[category_id] 
  23. WHERE StartTime>=dateadd(HOUR,@pass_hours,getdate()) and StartTime<=getdate()
  24. and [eventclass]=47 --drop talbe/index
  25. and databasename=@database_name
  26. and subclass_name='U'
  27. group by gt.ObjectID

image.png

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

  1. select * from msdb.dbo.backupmediafamily where media_set_id=(
  2. select top 1 media_set_id from msdb.dbo.backupset 
  3. where database_name=@database_name
  4. and backup_finish_date>=@max_start_time
  5. and type='L' order by backup_finish_date)

image.png

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

  1. declare @lockinfo varchar(100)
  2. set @lockinfo='%SCH_M OBJECT%:'+@ObjectID+':%'
  3. select [Transaction id],'lsn:0x'+[Current LSN] 
  4. from fn_dump_dblog (
  5.         NULLNULL, N'DISK'1@backupfile,
  6.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  7.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  8.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  9.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  10.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  11.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  12.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  13.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  14.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTas a
  15. where convert(datetime,[Begin Time])
  16. between dateadd(ss,-5,@min_start_time) and dateadd(ss,5,@max_start_time)
  17. and [Transaction Name]='DROPOBJ'
  18. and exists(select 1 from fn_dump_dblog (
  19.         NULLNULL, N'DISK'1@backupfile,
  20.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  21.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  22.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  23.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  24.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  25.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  26.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  27.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT,
  28.         DEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULTDEFAULT)
  29.         where [Transaction id]=a.[Transaction id] and [Lock Information] like @lockinfo)

4. 恢复所需相关信息

image.png

5. 邮件显示
image.png

6. 恢复语句(最后执行的一句)

Restore log DBName from disk='xxx' with stopbeforemark='xxx'