谁删除了我的数据（SQL）

引言

上个月，我的朋友打电话给我说，有人删除了他SQL表中的重要数据，现在没有人承认。他让我寻找一种方法来找到罪魁祸首。经过一番摸索，我找到了一种方法，现在想与你分享。所以，让我们一起欣赏这篇文章吧。

本文将帮助你找到在你的表或数据库上执行DELETE或DROP语句的用户。

我们需要的东西

要查找罪魁祸首，我们需要读取数据库的事务日志条目。是的……你没听错，你可以读取SQL事务日志数据（即LDF文件）。让我们开始吧。

1. 我们将创建一个带有数据的示例表。
2. 从中删除行。
3. 尝试跟踪删除（软删除或硬删除）数据条目的用户（这里**软删除**表示使用查询删除记录，**硬删除**表示直接从SQL表中使用“DEL”按钮（或可能是鼠标）删除数据）。

开始吧

LDF:

（那些不知道什么是LDF的人）LDF是日志数据文件的扩展名，这些文件与MDF文件（包含实际数据）一起存在。LDF文件存储所有具有时间戳的事务，并在数据丢失的情况下帮助恢复数据库。

现在，要读取LDF文件，我们需要使用'fn_dblog'函数（这是SQL的未公开函数），在特定数据库上执行此函数后，你将能够看到在该数据库上执行的实时事务日志和操作。

让我们使用以下查询创建一个示例数据库和表。

CREATE DATABASE [Sample] ON  PRIMARY
( NAME = N'Sample_dat', FILENAME = N'D:\Sample\Sample.mdf' , SIZE = 13760KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 10%)
 LOG ON
( NAME = N'Sample_log', FILENAME = N'D:\Sample\Sample.ldf' , SIZE = 9216KB , MAXSIZE = UNLIMITED, 
FILEGROWTH = 10%)
GO

示例表

USE [Sample]
GO
CREATE TABLE [Emp] (
    [No] INT ,
    [Name] VARCHAR (50),
    [Address] VARCHAR (50)
);

现在让我们检查LDF日志中记录的内容。

在Sample数据库中运行'fn_dblog'函数。

select * from fn_dblog(null,null)

如果你看到上面的结果面板，仅仅是CREATE DATABASE和CREATE TABLE脚本就记录了近35行。

让我们在表中插入一些行。

Insert into Emp values(1,'name1', 'address1')
Insert into Emp values(2,'name2', 'address2')
Insert into Emp values(3,'name3', 'address3')
Insert into Emp values(4,'name4', 'address4') 

跟踪DELETE活动

现在，只需从数据库中删除所有行，使用以下简单查询：

Delete from Emp

我们的Emp表现在为空，因为我们已经删除了所有查询。

让我们检查日志表，其中操作类型为'LOP_DELETE_ROWS'，再次执行fn_dblob函数，看看你得到了什么。

select * from fn_dblog(null,null) where Operation = 'LOP_DELETE_ROWS'

结果

上面的结果面板显示了所有在特定数据库表上具有'DELETE'条目的事务行，你需要搜索你的'特定'表（你从中丢失了数据的地方），查看'AllocUnitName'列，此列包含你已执行'DELETE'语句的表名。

在我们的例子中，表名为'Emp'，现在获取该特定'表'条目记录的事务ID，执行以下查询以获取特定表的记录。

select Operation, [Transaction ID], AllocUnitName,  * from fn_dblog(null,null) 
where Operation = 'LOP_DELETE_ROWS' and allocUnitName = 'dbo.emp'

结果

在我们的例子中，事务ID相同，因为所有条目都是用单个'DELETE'语句（一次性）删除的（例如0000:0000079f）。

借助上述事务ID，我们将找到何时从数据库中删除我们的条目。为此，我们需要搜索操作为LOP_BEGIN_XACT的记录，在数据库上执行以下查询。

select  [Operation], [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
 
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] = '0000:0000079f' AND [Operation] = 'LOP_BEGIN_XACT'

上面的查询将提供事务的开始时间。

现在我们得到了有人在数据库上执行DELETE查询的确切时间，要了解活动的结束时间，你可以尝试以下查询。

SELECT
   [Begin Time], [End Time]
FROM
    fn_dblog(NULL, NULL)
WHERE
[transaction id] = '0000:000007a1' and [Operation] = 'LOP_BEGIN_XACT' or [operation] = 'LOP_COMMIT_XACT'

这是上面查询的结果。

现在让我们找到罪魁祸首，我们将找到执行删除查询的真实数据库用户。

Transaction SID列包含加密的十六进制文本，它实际上就是执行'Delete'查询的用户名。

执行以下查询以获取[Transaction SID]列，借助事务ID和操作='DELETE'。

select  [Operation], [Transaction Name], [Transaction SID]
FROM fn_dblog(NULL, NULL) where [Transaction ID] = '0000:000007a1' and [Transaction Name]='DELETE'

上述查询的输出是

你只需要从[Transaction SID]列复制加密的十六进制内容，并在主数据库上执行以下查询，根据我们的结果，我的十六进制字符串是0x01。

SELECT SUSER_SNAME(0x01)

**SUSER_SNAME**是内置函数，它只检查安全标识号（SID）并返回与其关联的登录名。

当我运行上面的查询时，我得到了以下输出。

是的……我们最终找到了执行Delete查询的真正罪魁祸首。

跟踪DROP活动

同样，如果有人从数据库中删除了你的表，我们可以使用以下查询来跟踪该活动，

让我们使用以下简单查询删除表。

Drop table Emp

现在使用事务名称'DROPOBJ'跟踪活动。

检查以下查询。

SELECT [Transaction Name], Operation, [Transaction Id], [Transaction SID],  [Begin Time] 
FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] = 'DROPOBJ'

我得到了以下结果。

与DELETE场景相同，根据我们的结果，十六进制字符串是0x01，在主数据库上执行以下查询。

SELECT SUSER_SNAME(0x01)

这与用户'sa'相同。

所以，总结一下

SQL将其所有事务存储在日志表中，我们可以使用fn_dblob函数读取事务日志文件，我们可以借助此函数对每个事务进行更多研究。所有事务都以不同的操作记录，借助SUSER_SNAME函数，我们可以轻松地追踪出加密的用户名。

在我的下一篇文章中，我将深入探讨“读取SQL的事务日志（LDF）”的要点，所以请继续关注。

并享受这篇文章。

**不要更改fn_dblog的条目，或者除非你有备份，否则不要在生产环境中运行这些命令。**

*欢迎提出建议和意见

-祝追踪愉快

Prasad