查看sql server数据什么时候被删除了

原创

mob64ca12f49f4b 2024-09-18 06:05:05 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12f49f4b的原创作品，请联系作者获取转载授权，否则将追究法律责任

如何查看 SQL Server 数据什么时候被删除了

在现代数据库管理实践中，了解数据删除的时间和原因是至关重要的。在 SQL Server 中，虽然直接获取已删除数据的时间并不容易，但我们可以采用一些策略来实现这个目标。本文将通过简洁的步骤引导您完成这一任务。

整体流程

我们将按照以下步骤进行操作：

步骤	描述
1	设置数据库审计
2	创建一个触发器用于记录删除操作
3	记录删除操作的时间和相应信息
4	查询记录以查看删除操作的信息
5	维护和管理审计记录

接下来，我们将逐步深入每个步骤。

步骤详细说明

1. 设置数据库审计

在 SQL Server 中，我们可以使用审计功能来跟踪对数据表的删除操作。首先，我们需要配置数据库审计。以下是创建审计的 SQL 代码：

-- 创建一个审计对象
CREATE SERVER AUDIT Audit_Delete
TO FILE ( FILEPATH = 'C:\AuditLogs\' ) -- 审计日志的存储路径
WITH (ON_FAILURE = CONTINUE);   -- 如果无法写入日志，继续执行
GO

-- 启动审计
ALTER SERVER AUDIT Audit_Delete
WITH (STATE = ON);
GO

2. 创建一个触发器用于记录删除操作

接下来，我们要创建一个触发器来捕获删除操作。这个触发器会在执行删除操作时记录当前时间和删除的数据。以下是 SQL 代码示例：

-- 创建审计删除操作的触发器
CREATE TRIGGER trg_AuditDelete
ON YourTableName     -- 指定要监控的表
AFTER DELETE
AS
BEGIN
    -- 将删除操作记录到审计表
    INSERT INTO AuditLog (DeletedTime, DeletedData)
    SELECT GETDATE(), *
    FROM deleted;     -- deleted 表包含被删除的记录
END;
GO

3. 记录删除操作的时间和相应信息

上面的触发器会将删除的数据和删除时间插入到一个审计日志表中。接下来，我们需要创建这个审计日志表以存储信息：

-- 创建审计日志表
CREATE TABLE AuditLog (
    LogID INT PRIMARY KEY IDENTITY(1,1),  -- 日志 ID
    DeletedTime DATETIME NOT NULL,         -- 删除时间
    DeletedData NVARCHAR(MAX) NOT NULL     -- 被删除的数据
);
GO

4. 查询记录以查看删除操作的信息

当我们想要检查已删除数据的记录时，只需查询 AuditLog 表，即可找到所有被删除的数据及其删除的时间：

-- 查询审计日志以查看全部删除记录
SELECT * FROM AuditLog
ORDER BY DeletedTime DESC;    -- 按删除时间降序排列

5. 维护和管理审计记录

审计记录会随着时间增长，因此需要定期清理过期的审计记录。以下是一个简单的 SQL 代码来清理 30 天前的记录：

-- 删除 30 天前的审计记录
DELETE FROM AuditLog 
WHERE DeletedTime < DATEADD(DAY, -30, GETDATE());

序列图展示

以下是整个流程的简单序列图，帮助我们理解过程：

sequenceDiagram
    participant User
    participant Database
    participant AuditLog

    User->>Database: 执行 DELETE 操作
    Database->>AuditLog: 触发器 Triggered
    AuditLog-->>Database: 记录删除时间和数据
    Database-->>User: 操作成功