SQL Server数据库的单表数据恢复

2024-01-17 11:58:59 浏览数 (2)

基于全量备份 事务日志，可以还原到任意时间点或者事务编号。

前提：

1、具备全量备份、和事务日志

2、数据库的recovery mode是full模式

案例演示

1、创建数据库并插入测试数据集

代码语言：sql复制
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、全量备份数据

代码语言：sql复制
BACKUP DATABASE OldDatabase TO DISK = 'D:OldDatabase.20240117.data.full.BAK' 
WITH COMPRESSION,INIT,FORMAT,
NAME = N'OldDatabase.20240117.data.full.BAK' ;

3、做一些crud操作

代码语言：sql复制
delete from Tab where id in (66,88,99,98);
这里删除了5行记录

4、备份下事务日志

代码语言：sql复制
BACKUP LOG OldDatabase TO DISK = 'D:OldDatabase.log.20240117.1.bak' 
WITH COMPRESSION,INIT,FORMAT,
NAME = N'OldDatabase.log.20240117.1.bak';

5、做一些crud操作

代码语言：sql复制
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、备份下事务日志

代码语言：sql复制
BACKUP LOG OldDatabase TO DISK = 'D:OldDatabase.log.20240117.2.bak' 
WITH COMPRESSION,INIT,FORMAT,
NAME = N'OldDatabase.log.20240117.2.bak';

7、做一个作update全表更新操作（模拟误操作）

代码语言：sql复制
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

代码语言：sql复制
-- 填入库表名和操作类型，即可看到某个表的操作历史类型
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。

代码语言：sql复制
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都填进去，查出相关的时间点，然后。

例如：

代码语言：sql复制
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、创建个新库，恢复全量备份文件

代码语言：sql复制
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、再逐个恢复事务日志

代码语言：sql复制
-- 恢复第一个日志
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、查询恢复出的数据是否正确

代码语言：sql复制
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

sqlserver

0 人点赞