sql server根据备份恢复数据到某个具体操作之前

个人总结：

（1）对于误操作而言是鸡肋（因为可能实际业务在同一时间有多个同类型的操作，你根本不知道你的误操作发生在哪一个LSN，也可以结合apexsql log 来解决，但既然都可以apexsql log了，干脆用那个恢复要好的多）

（2）对于备份还原的基本功而言是一次不错的理解练习与总结；

本文探讨了使用SQL数据库备份恢复由SQL Delete和SQL Truncate语句删除的数据的方法。

在继续本文之前，请阅读以下文章，以详细了解delete和truncate语句的工作方式。

【1】创建一个测试数据库环境

让我们为本文演示创建一个数据库环境。

【1.1】创建测试库（SQLShackDemo）

CREATE DATABASE SQLShackDemo;
GO
USE SQLShackDemo;
GO

【1.2】创建测试表（DeletemyData，TruncatemyData）

创建一个SQL表。我们将在此表上使用删除语句

CREATE TABLE DeletemyData
(id     INT IDENTITY(1, 1),
[Name] VARCHAR(40)
);
GO

创建另一个SQL表。我们将对该表使用truncate语句

CREATE TABLE TruncatemyData
(id     INT IDENTITY(1, 1),
[Name] VARCHAR(40)
);
GO

【2】回滚恢复数据演示

至此，我们有了一个带有两个空表[DeletemyData]和[TruncatemyData]的SQL数据库。

这是一个新数据库，我们没有任何数据库备份。让我们使用以下查询进行完整的数据库备份。

您也可以使用SSMS中的备份向导以图形方式进行备份。它是一个小型数据库，因此无需担心备份压缩。

【2.1】完整备份

Backup database SQLShackdemo to disk='c:\temp\SQLShackdemo.bak'

　　备份资料库

执行以下查询以从msdb系统数据库中检索数据库备份历史记录。

SELECT bs.database_name AS DatabaseName,
    CASE bs.type
        WHEN 'D'
        THEN 'Full'
        WHEN 'I'
        THEN 'Differential'
        WHEN 'L'
        THEN 'Transaction Log'
    END AS BackupType,
    CAST(bs.first_lsn AS VARCHAR(50)) AS FirstLSN,
    CAST(bs.last_lsn AS VARCHAR(50)) AS LastLSN,
    bmf.physical_device_name AS PhysicalDeviceName,
    CAST(CAST(bs.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS BackupSize,
    bs.recovery_model AS RecoveryModel
FROM msdb.dbo.backupset AS bs
  INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = 'SQLShackdemo'
ORDER BY backup_start_date DESC,
      backup_finish_date;

它还提供了第一个和最后一个日志序列号（LSN）详细信息。

日志序号

【2.2】测试表数据构造

现在，在两个表中插入十个记录。

DECLARE @id INT;
SET @ID = 10;
WHILE(@ID > 0)
    BEGIN
        INSERT INTO DeletemyData([Name])
    VALUES('Delete Data' + ' ' + CAST((@ID) AS VARCHAR));
        SET @ID = @ID - 1;
    END;
 
 
DECLARE @id INT;
SET @ID = 10;
WHILE(@ID > 0)
    BEGIN
        INSERT INTO TruncatemyData([Name])
    VALUES('Truncate data' + ' ' + CAST((@ID) AS VARCHAR));
        SET @ID = @ID - 1;
    END;

　　查看样本数据

【2.3】delete 和 truncate 操作模拟

现在，在SSMS中打开两个查询窗口。

在第一个查询窗口中，从[DeletemyData]表中删除一些记录。

DELETE FROM
WHERE id < 5;

在第二个查询窗口中，截断SQL表。我们无法在截断中指定WHERE子句，因此它将从表中删除所有记录。

Truncate table TruncatemyData

验证两个表中的记录。[TruncatemyData]表中有0条记录，而[DeletemyData]包含6条记录。

　　验证记录

【2.4】使用 fn_dblog(null,null) 获取事务日志详细信息

我们可以使用未记录的函数fn_dblog从事务日志中获取有关delete和truncate语句的信息。

　　请参阅本文，如何使用fn_dblog和fn_dump_dblog在SQL Server数据库中直接直接连续读取事务日志文件数据，以获取更多详细信息。

我们可以使用where条件中的delete和truncate table子句来过滤事务日志条目。

USE SQLShackDemo;
GO
SELECT 
[Transaction Name],
[Current LSN],
       [transaction ID] tranID,
       [begin time],
       Description,
       operation,
       Context
FROM ::fn_dbLog(NULL, NULL)
WHERE [Transaction Name] IN('Delete', 'Truncate table');

它显示了两个事务日志记录。我们可以使用描述列来隔离交易。根据以下屏幕截图，第一个条目用于删除，而后面的条目用于truncate语句。您可以记下这些事务的开始时间。

删除：2020/02/26 19：44：27：440
截断时间：2020/02/26 19：44：45：830

【2.5】备份事务日志

在完全恢复模型中，事务日志备份维护日志链。我们还可以使用事务日志备份来进行时间点恢复。让我们执行以下查询以进行日志备份。它需要备份所有数据更改。

Backup log SQLShackdemo to disk='c:\temp\SQLShackdemo_log.trn'

　　备份日志

使用上述查询，从msdb数据库查看数据库备份历史记录。它显示两个条目–完整和事务日志备份。

　　查看数据库备份历史记录

【3】备份还原=》恢复 Delete语句删除的数据

【3.1】以norecovery 的方式恢复完整备份=》以此构建一个新的数据库

现在，假设您需要从现有备份中恢复已删除的数据。我们将从完整备份中创建一个新数据库。

我们需要以NORECOVERY模式还原备份，以便我们可以在其上应用进一步的事务日志备份。

USE [master];
RESTORE DATABASE [SQLShackDemo_restore] FROM DISK = N'C:\TEMP\SQLShackdemo.bak' WITH FILE = 1,
MOVE N'SQLShackDemo' TO N'C:\sqlshack\Demo\SQLShackDemo.mdf',
MOVE N'SQLShackDemo_log' TO N'C:\sqlshack\Demo\SQLShackDemo_log.ldf',
NORECOVERY, NOUNLOAD, STATS = 5;
GO

数据库[SQLShackDemo_restore]处于还原模式。处于还原模式时，我们无法访问数据库。

　　恢复的数据库

在本文中，我们使用Restore log命令的STOPAT参数了解了SQL Server的时间点恢复。我们可以在STOPAT参数中指定特定的时间戳或LSN。

同样，我们可以在还原日志语句中使用STOPBEFOREMARK。顾名思义，此参数指示SQL Server在达到特定的时间戳或LSN后停止数据库还原。您可以参考Microsoft文档以获取关于STOPBEFOREMARK的更多详细信息。

【3.2】把 fn_dblog 中的16进制LSN转成10进制

以十进制格式转换HEX LSN值

在上面的fn_dblog的输出中，我们有LSN用于delete和truncate语句。

删除LSN：00000026：00000230：0001
截断LSN： 00000026：00000268：0001

fn_dblog中的LSN值采用十六进制格式。Restore log命令要求LSN为十进制格式。我们可以使用以下查询将其转换为十进制格式。在这里，在@LSN参数中指定LSN。

DECLARE @LSN VARCHAR(22), @LSN1 VARCHAR(11), @LSN2 VARCHAR(10), @LSN3 VARCHAR(5);
SET @LSN = '00000026:00000230:0001';
SET @LSN1 = LEFT(@LSN, 8);
SET @LSN2 = SUBSTRING(@LSN, 10, 8);
SET @LSN3 = RIGHT(@LSN, 4);
SET @LSN1 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN1, 8), 1) AS INT);
SET @LSN2 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN2, 8), 1) AS INT);
SET @LSN3 = CAST(CONVERT(VARBINARY, '0x' + RIGHT(REPLICATE('0', 8) + @LSN3, 8), 1) AS INT);
SELECT CAST(@LSN1 AS VARCHAR(8)) + CAST(RIGHT(REPLICATE('0', 10) + @LSN2, 10) AS VARCHAR(10)) + CAST(RIGHT(REPLICATE('0', 5) + @LSN3, 5) AS VARCHAR(5));

使用上面的查询，我们为delete和truncate语句获得以下LSN值。

删除LSN：38000000056000001
截断LSN： 38000000061600001

【3.3】使用restore log stopbeforemark 还原数据到被 Delete LSN 操作之前

现在，使用STOPBEFORMARK参数运行还原日志查询。此查询在指定的LSN之前停止数据库还原的处理。

Restore log  [SQLShackDemo_restore] FROM DISK = N'C:\TEMP\SQLShackdemo_log.trn'
with STOPBEFOREMARK ='lsn:38000000056000001'

我们得到以上RESTORE LOG命令的以下输出。

以十进制格式转换HEX LSN值

恢复日志备份后，就可以访问数据库了。验证[DeletemyData]表中的记录，它显示数据可用。我们可以使用此数据，并使用导出和导入向导将其导出到原始数据库。

恢复从SQL Delete语句删除的数据

【4】恢复从SQL Truncate语句中删除的数据

我们已经恢复了由delete语句删除的数据。让我们执行类似的测试以从truncate语句中恢复数据。

【4.1】以NORECOVERY模式还原完整的数据库备份

USE [master];
RESTORE DATABASE [SQLShackDemo_restore_1] FROM DISK = N'C:\TEMP\SQLShackdemo.bak' WITH FILE = 1,
MOVE N'SQLShackDemo' TO N'C:\sqlshack\Demo\SQLShackDemo.mdf',
MOVE N'SQLShackDemo_log' TO N'C:\sqlshack\Demo\SQLShackDemo_log.ldf',
NORECOVERY, NOUNLOAD, STATS = 5;
GO

【4.2】使用restore log stopbeforemark 还原数据到被 Truncate LSN 操作之前

Restore log  [SQLShackDemo_restore_1] FROM DISK = N'C:\TEMP\SQLShackdemo_log.trn'
with STOPBEFOREMARK ='lsn:38000000061600001'

验证[TruncatemyData]表中的数据

结论

在本文中，我们在数据库备份的帮助下使用SQL Delete和SQL Truncate语句恢复了已删除的数据。您不应在生产数据库中执行任何测试。您可以创建测试环境并探索数据恢复。