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

我没有在 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]

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

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

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

尽管已经回答了这个问题，但想补充一点，SQL Server 启用了默认跟踪，它可用于找出谁删除/更改了对象。

对象事件

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

注意：默认情况下，SQL Server 有 5 个跟踪文件，每个 20 MB，并且没有已知的支持方法可以更改它。如果您有一个繁忙的系统，跟踪文件可能会滚动得太快（甚至在几个小时内），您可能无法捕捉到某些更改。

可以找到很好的例子：SQL Server 中的默认跟踪 - 性能和安全审计的力量

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

要查找用户，在您找到最后存在的值的日志备份后，您可以恢复数据库直到该日志备份，然后按照Mark Storey-Smith的回答。

一些先决条件

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

免责声明

这个解决方案远非防水，需要做更多的工作。

它还没有在大规模环境中进行过测试，甚至除了一些小测试之外，甚至没有在任何环境中进行过测试。当前运行在 SQL Server 2017 上。

您可以使用以下来自Muhammad Imran的过程，我将其修改为使用日志备份的内容而不是实时数据库日志的内容。

这样，您在技术上不会进行恢复，而是将日志内容转储到临时表中。它可能仍然很慢，并且对错误和问题非常开放。但理论上它可以工作™。

存储过程使用未记录的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)val3dbo.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]

我的结果（系统管理员）

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