【备份还原】sql server时间点还原，sql server精确点还原

【1】构造测试数据

OLTP数据库经常执行DML，有时候DBA维护数据也需要对数据进行更改。依墨菲定律，一旦有权限去修改数据，就一定会发生误操作。如果发生了误删数据，在SQL Server中，你知道怎么恢复吗？你知道恢复到哪个时间点才是最精确的吗？

下面，我们就简单进行一个测试，手把手教你如何精确恢复到指定操作前的数据。

USE master
GO
CREATE DATABASE TestGOUSE Test
GO
CREATE TABLE Tab(id int not null,name varchar(50) not null
, insert_time datetime not null
)
GO
INSERT INTO Tab SELECT CONVERT(INT,RAND()*100),'KK',GETDATE()GO 
SELECT * FROM Tab
GO

　　

我们插入了10行数据，当然，数据库默认是完整模式的，以便我们可以进行日志恢复。完整备份是必要的，我们首先进行一次完整备份。然后删除 id 大于80的数据。

BACKUP DATABASE Test TO DISK = 'E:\Backup\Test.BAK' WITH COMPRESSION,INIT,FORMAT
GO
DELETE FROM Tab WHERE id>80
GO

执行完成后，删除掉了2行数据。现在我们准备恢复此操作之前的数据，也就是该事务之前的数据。即使后续进行了多次删除，我们估算时间，也能找到某次删除前的所有数据。

所有的数据操作都会记录到事务日志中，我们需要找到该具体的事务ID。SQL Server 提供了一个函数 sys.fn_dblog，可以在线查询数据库的事务日志内容。如下，我们可以查看 Delete的记录，也可以指定表来查询。

_{【2】使用 sys.fn_dblog 解析日志文件或日志备份}

（2.1）根据操作类型找队友的的 LSN、事务

SELECT [Current LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM sys.fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_ROWS'
--AND AllocUnitName = 'dbo.Tab'
GO

　　

（2.2）通过事务号查询该事务最早的 LSN

通过日志我们可以看到有2行删除记录，这2行记录都是在同一个事务ID（Transaction ID）中。

但数据库并没有以事务ID还原的方法，我们最终需要的是日志序列号（LSN）。

上图中的LSN并不是该事务最早的LSN，我们需要通过事务ID再次查询LSN。

SELECT [Current LSN],  Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM sys.fn_dblog(NULL, NULL)
WHERE [Operation] = 'LOP_BEGIN_XACT'
AND [Transaction ID]='0000:00000394'

　　

BACKUP LOG Test TO DISK = 'E:\Backup\Test_log.trn' WITH COMPRESSION,INIT,FORMAT
GO

（2.3）从日志备份文件中查询的办法 sys.fn_dump_dblog

对于从日志中查看记录，其实还有另一种方法，直接从日志备份文件中查询。

SELECT [Current LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM sys.fn_dump_dblog(NULL, NULL, N'DISK', 1,N'E:\Backup\Test_log.trn',
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 [Operation] = 'LOP_DELETE_ROWS'

查询文件会比较慢些（相当慢），不过查询的内容是一样的。

（2.4）STOPATMARK 还原到指定LSN，STOPAT 还原到指定时间点

既然日志序列号找到了，数据库还原可通过STOPATMARK 和 STOPBEFOREMARK 指定日志序列号，日志序列号前面需要添加 lsn:0x，0x 表示十六进制格式。

USE master
GO
RESTORE DATABASE Test_COPY FROM
DISK = 'E:\Backup\Test.BAK' WITH
MOVE 'Test' TO 'E:\Backup\Test2.mdf',
MOVE 'Test_log' TO 'E:\Backup\Test2_log.ldf',
REPLACE, NORECOVERY;
GO
RESTORE LOG Test_COPY FROM DISK = N'E:\Backup\Test_log.trn' 
WITH STOPATMARK  = 'lsn:0x00000033:00000150:0001'
GO

当然，上面我们查询最早的LSN时，我们也看到了事务的执行时间为 2022/08/12 16:19:32:567 ，也可以通过具体的时间点来恢复数据。

RESTORE LOG Test_COPY FROM DISK = N'E:\Backup\Test_log.trn' 
WITH STOPAT = '2022/08/12 16:19:32:567'
GO

【参考文档】

本文转自CSDN KK微信公众号文章：https://mp.weixin.qq.com/s?__biz=MzIwMDkwNDA3MA==&mid=2247484670&idx=1&sn=bdb8c11df9c197eedd5887f7dcebedbb&chksm=96f75817a180d101336909b9cd5008723e2e8bc26d5c97e4130ec66c4863ad82a6839da755c1&mpshare=1&scene=23&srcid=0815TZsP3W7iwZ1uN8xbjMaJ&sharer_sharetime=1660521901518&sharer_shareid=926aaab39ee1a0091ecfd3e337a5f0a7#rd

日志分析工具：https://github.com/ap0405140/MSSQLLogAnalyzer