如何找出谁删除了一些数据SQL Server

我的老板昨天从一个客户那里询问，询问他们如何找出谁删除了他们在SQL Server数据库中的某些数据（如果重要的话，这是快速版）。

我认为可以从事务日志（假设它没有被截断）-这是正确的吗？如果是这样，您实际上将如何找到这些信息？

#1 楼

我没有在Express上尝试过fn_dblog，但是如果可用，以下内容将为您提供删除操作：

SELECT 
    * 
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'

获取您感兴趣的交易的交易ID，并标识发起交易的SID：

SELECT
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = @TranID
AND
    [Operation] = 'LOP_BEGIN_XACT'

，然后从SID中标识用户：

SELECT
    *
FROM 
    sysusers
WHERE
    [sid] = @SID

编辑：将所有这些汇总在一起以查找指定表上的删除：

DECLARE @TableName sysname
SET @TableName = 'dbo.Table_1'

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM 
        fn_dblog(NULL, NULL) 
    WHERE
        AllocUnitName LIKE @TableName + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]

这确实适用于SQL Express，但是在我的系统上，它仅显示今天发生的事务。我没想到SQL Express开箱即用地截断了事务日志吗？

–马特·威尔科（Matt Wilko）
2011年8月3日16:00

如果您的数据库处于简单恢复模型中，则您无法对非活动事务在日志中停留多长时间做出任何假设。

–亚伦·伯特兰（Aaron Bertrand）
2011年8月3日在16:27

事务日志是基本的，而不是可选的。什么是数据库的恢复模型（简单或完整），以及如何配置备份（仅完整或日志备份+完整）？

– Mark Storey-Smith
2011年8月3日在16:28

我在这里偷了我的答案，尽管重构了一些以避免在fn_dblog上进行自我联接。缺点之一是它返回数据库USERNAME（）而不是更有用的登录名。

–马丁·史密斯
2013年12月13日在18:15

#2 楼

如果数据库处于完全恢复模式，或者您具有事务日志备份，则可以尝试使用第三方日志读取器读取这些备份。

您可以尝试ApexSQL Log（高级版，但有免费试用版）或SQL Log Rescue（免费，但仅限sql 2000）。

#3 楼

他们如何找出谁删除了SQL Server数据库中的某些数据

尽管已回答，但想补充一下SQL Server已启用默认跟踪，并且可以用于发现谁丢弃/更改了对象。

对象事件

对象事件包括：更改对象，创建对象和删除对象

注释注意：默认情况下，SQL Server具有5个跟踪文件，每个跟踪文件20 MB，并且没有已知的支持的更改方法。如果系统繁忙，则跟踪文件的滚动速度可能会太快（甚至数小时之内），并且可能无法捕获某些更改。

可以找到出色的示例：默认SQL Server中进行跟踪-性能和安全性审核的功能

#4 楼

您可以尝试执行以下过程来查询日志备份文件，并查找在哪个/哪些日志备份文件中某个表的列的特定值仍然存在/最新存在。

要在之后找到用户您在哪个日志备份中找到最后一个值的值，可以还原数据库直到该日志备份，然后按照Mark Storey-Smith的回答进行操作。

一些先决条件

>了解从哪些列中删除了哪些值
处于完全恢复模式下并正在进行日志备份
您在日志备份中具有日期或标识符，例如使用Ola时
Hallengren的解决方案

免责声明

该解决方案远非防水，需要投入大量工作。

未经大规模环境测试，甚至是除一些小型测试之外的任何环境。
当前运行的是SQL Server2017。

您可以使用Muhammad Imran的以下过程，我将其修改为使用日志备份的内容，而不是实时数据库的日志的内容。 br />
这样，从技术上讲，您不进行还原，而是将日志内容转储到临时表中。它可能仍然会很慢，并且非常容易出现错误和问题。但是，从理论上讲，它是可行的。

存储过程使用未记录的fn_dump_dblog函数读取日志文件。

测试环境

考虑该数据库，我们在其中插入一些行，进行2次日志备份，在第三个日志备份中，我们删除所有行。

CREATE DATABASE WrongDeletesDatabase
GO
USE WrongDeletesDatabase
GO
BACKUP DATABASE WrongDeletesDatabase TO DISK ='c:\temp\Full.bak'

ALTER DATABASE WrongDeletesDatabase SET RECOVERY FULL
GO

CREATE TABLE dbo.WrongDeletes(ID INT, val varchar(255))

INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (1,'value1')
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log1.trn'
GO
INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (2,'value2')
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log2.trn'
GO
DELETE FROM dbo.WrongDeletes
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log3.trn'
GO
INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (3,'value3')
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log4.trn'
GO

过程

您可以在此处找到并下载存储过程。

我不能在这里添加它，因为它大于字符数限制，并且会使答案变得更不清楚。

除此之外，您应该能够运行该程序。

运行该程序

这样的一个示例，当我将所有日志文件（4）添加到存储过程并运行该过程以查找value1

EXEC dbo.Recover_Deleted_Data_Proc  @Database_Name= 'WrongDeletesDatabase',
                                    @SchemaName_n_TableName= 'dbo.WrongDeletes', 
                                    @SearchString = 'value1', 
                                    @SearchColumn = 'val',
                                    @LogBackupFolder ='C:\temp\Logs\'

这会得到我：

ID  val LogFileName
1   value1  c:\temp\Logs\log3.trn
1   value1  c:\temp\Logs\log1.trn

在哪里可以找到上一次对value1进行操作的时间，请在log3.trn中删除。

更多测试数据，并添加了一个表格不同的列

CREATE TABLE dbo.WrongDeletes2(Wow varchar(255), Anotherval varchar(255),Val3 int)

INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (1,'value1')
INSERT INTO dbo.WrongDeletes2(wOw,Anotherval,Val3)
VALUES ('b','value1',1)
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log1_1.trn'
GO
INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (2,'value2')
INSERT INTO dbo.WrongDeletes2(wOw,Anotherval,Val3)
VALUES ('c','value2',2)
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log2_1.trn'
GO
DELETE FROM dbo.WrongDeletes
DELETE FROM dbo.WrongDeletes2
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log3_1.trn'
GO
INSERT INTO dbo.WrongDeletes(ID,val)
VALUES (3,'value3')
INSERT INTO dbo.WrongDeletes2(wOw,Anotherval,Val3)
VALUES ('d','value3',3)
GO
BACKUP LOG WrongDeletesDatabase TO DISK = 'c:\temp\Logs\log4_1.trn'
GO

更改日志文件名并再次执行该过程

EXEC dbo.Recover_Deleted_Data_Proc  @Database_Name= 'WrongDeletesDatabase',
                                    @SchemaName_n_TableName= 'dbo.WrongDeletes', 
                                    @SearchString = 'value1', 
                                    @SearchColumn = 'val',
                                    @LogBackupFolder ='C:\temp\Logs\'

结果

ID  val LogFileName
1   value1  c:\temp\Logs\log1_1.trn
1   value1  c:\temp\Logs\log3_1.trn
1   value1  c:\temp\Logs\log3_1.trn

重新运行，在2的val3列中搜索整数（dbo.WrongDeletes2）

EXEC dbo.Recover_Deleted_Data_Proc  @Database_Name= 'WrongDeletesDatabase',
                                    @SchemaName_n_TableName= 'dbo.WrongDeletes2', 
                                    @SearchString = '2', 
                                    @SearchColumn = 'Val3',
                                    @LogBackupFolder ='C:\temp\Logs\'

结果

Anotherval  Val3    Wow LogFileName
value2  2   c   c:\temp\Logs\log2.trn
value2  2   c   c:\temp\Logs\log3.trn

应用Mark Storey-Smith的答案

我们现在知道它发生在第三个日志文件中，让我们还原到现在为止：
/>

USE master
GO
ALTER DATABASE WrongDeletesDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE WrongDeletesDatabase SET ONLINE 
GO
RESTORE DATABASE WrongDeletesDatabase FROM DISK = 'c:\temp\Logs\Full.bak' WITH NORECOVERY,REPLACE
RESTORE LOG WrongDeletesDatabase FROM DISK = 'c:\temp\Logs\log1.trn' WITH NORECOVERY
RESTORE LOG WrongDeletesDatabase FROM DISK = 'c:\temp\Logs\log2.trn' WITH NORECOVERY
RESTORE LOG WrongDeletesDatabase FROM DISK = 'c:\temp\Logs\log3.trn' WITH RECOVERY
GO
USE WrongDeletesDatabase
GO

运行他的答案中的最后一个查询

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM 
        fn_dblog(NULL, NULL) 
    WHERE
        AllocUnitName LIKE @TableName + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]

对我来说是结果（sysadmin）

UserName    TransactionStartTime
dbo 2019/08/09 17:14:10:450
dbo 2019/08/09 17:14:10:450

编程黑洞网

如何找出谁删除了一些数据SQL Server

#1 楼

评论

#2 楼

#3 楼

#4 楼