SQL Server数据库的单表数据恢复
原创SQL Server数据库的单表数据恢复
原创
基于全量备份+事务日志，可以还原到任意时间点或者事务编号。
前提：
1、具备全量备份、和事务日志
2、数据库的recovery mode是full模式
案例演示
1、创建数据库并插入测试数据集
CREATE DATABASE OldDatabase;
ALTER DATABASE OldDatabase SET RECOVERY full;
USE OldDatabase;
CREATE TABLE Tab(id int not null,name varchar(50) not null, insert_time datetime not null);
-- 插入100条随机数据
INSERT INTO Tab SELECT CONVERT(INT,RAND()*100),'KK',GETDATE()
GO 100
-- 符合这个查询条件的记录数（我这里是53条）
SELECT count(*) FROM Tab where id>=50;2、全量备份数据
BACKUP DATABASE OldDatabase TO DISK = 'D:\OldDatabase.20240117.data.full.BAK'
WITH COMPRESSION,INIT,FORMAT,
NAME = N'OldDatabase.20240117.data.full.BAK' ;3、做一些crud操作
delete from Tab where id in (66,88,99,98);
这里删除了5行记录4、备份下事务日志
BACKUP LOG OldDatabase TO DISK = 'D:\OldDatabase.log.20240117.1.bak'
WITH COMPRESSION,INIT,FORMAT,
NAME = N'OldDatabase.log.20240117.1.bak';5、做一些crud操作
INSERT INTO Tab SELECT CONVERT(INT,RAND()*1000),'AA',GETDATE()
GO 30
这里插入了30条数据
select count(*) from Tab where name='AA';
当前表的总行数(我这里是125条)
select count(*) from Tab;6、备份下事务日志
BACKUP LOG OldDatabase TO DISK = 'D:\OldDatabase.log.20240117.2.bak'
WITH COMPRESSION,INIT,FORMAT,
NAME = N'OldDatabase.log.20240117.2.bak';7、做一个作update全表更新操作（模拟误操作）
update Tab set name='Admin' where 1=1 AND id>=1;
我这里是影响了122条记录8、发现误操作情况，联系DBA处理
恢复过程
1、找到误操作的时间点或者LSN
update 对应的是LOP_MODIFY_ROW， delete对应的是LOP_DELETE_ROWS， insert对应的是LOP_INSERT_ROWS
-- 填入库表名和操作类型，即可看到某个表的操作历史类型
SELECT [Current LSN], [Transaction ID], Operation, Context, AllocUnitName
FROM sys.fn_dblog(NULL, NULL)
WHERE 1=1
AND Operation = 'LOP_MODIFY_ROW'
AND AllocUnitName = 'dbo.Tab';
注意： 因为我们这里测试的环境没啥读写负载，这里的Transaction ID只查出来一条0000:0000043c。如果是生产环境，可能查到很多个Transaction ID，则还需要根据step2的sql来结合时间点来分析判断。
2、根据上面的transaction 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 1=1
AND [Operation] = 'LOP_BEGIN_XACT'
AND [Transaction ID]='0000:0000043c';
这里找到的lsn编号就是 00000023:0000010c:0002
既然日志序列号找到了，数据库还原可通过STOPATMARK 和 STOPBEFOREMARK指定日志序列号，日志序列号前面需要添加 lsn:0x，0x 表示十六进制格式。
PS：如果生产环境改动频繁，则在step1里面会查出来有很多个Transaction ID，则可以把这些Transaction ID都填进去，查出相关的时间点，然后。
例如：
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:0000043c'
OR
[Transaction ID]='0000:00000434'
);3、创建个新库，恢复全量备份文件
CREATE DATABASE [NewDatabase];
RESTORE DATABASE [NewDatabase]
FROM DISK = 'D:\OldDatabase.20240117.data.full.BAK'
WITH
MOVE 'OldDatabase' TO 'D:\OldDatabase2.mdf',
MOVE 'OldDatabase_log' TO 'D:\OldDatabase2_log.ldf',
REPLACE, NORECOVERY;说明：
-- 不覆盖原始库，需要加上 MOVE 参数。
这里的文件路径是随手写的，因为只是临时数据恢复用下，用完这个库就删掉了，也不会产生多大影响
4、再逐个恢复事务日志
-- 恢复第一个日志
RESTORE LOG [NewDatabase]
FROM DISK = N'D:\OldDatabase.log.20240117.1.bak'
WITH NORECOVERY, NOUNLOAD, STATS = 10;
-- 恢复第N个日志(步骤省略)
-- 恢复最后一个日志
RESTORE LOG [NewDatabase]
FROM DISK = N'D:\OldDatabase.log.20240117.2.bak'
WITH STOPATMARK = 'lsn:0x00000023:0000010c:0002';
-- 数据库打开读写
RESTORE DATABASE [NewDatabase] WITH RECOVERY;5、查询恢复出的数据是否正确
select * from NewDatabase.dbo.Tab;6、将数据导出或直接写回到原始库
7、其它
如果有需要，重新创建索引等
参考
https://www.freeviewer.org/blog/restore-only-one-table-from-sql-server-backup/
https://mp.weixin.qq.com/s/5Eud3AZ1Na7t9FmtV1IEnA
https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-and-recovery-overview-sql-server?view=sql-server-ver16
https://solutioncenter.apexsql.com/how-to-recover-a-single-table-from-a-sql-server-database-backup/
https://learn.microsoft.com/zh-cn/sql/relational-databases/backup-restore/complete-database-restores-full-recovery-model?view=sql-server-ver15&eqid=9d34a040000163690000000664643d14
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。