sqlserver数据库，按照指定日期将表中对应时间段内的记录备份出去。

Question

业务需求：

数据库自2009年起有5年的数据量。

数据库只保存近两年的数据。比如 2012-01—01 ~ 2014-01-01

首先将 2010-01-01 ~ 2012-01-01 的数据保存到大文件夹里【比如 D:\2010年及2011年数据】，以月份命名各个文件夹【每个文件夹里有当月的所有表，表中有当月的记录】
后将 2010-01-01 ~ 2012-01-01 的数据从数据库中删除

然后每一个月的最后一天【例:2014-01-31 24:00:00 点一过，也就是2014-02-01 00:00:01时刻】，
将近两年的第一个月的信息【也就是:2012-01-01 ~ 2012-01-31 的信息备份到本地文件，命名格式『2012年1月备份信息』】。
然后将这个月的信息【2012年1月】从数据库中删除.
从而实现数据库中只保存近二年的数据。

月底自动触发脚本【ssis，dts，维护计划？】。

碰到的问题：

碰到了一个棘手的问题：

这样的备份方式需要所有的表有一个日期，这样才好分别哪些记录是2010年1月的，哪些记录是2010年2月的，从而进行备份以及删除。

---解决方法1:

首先，前些时候的数据手工备份，留下二年的数据。
因为所有的记录都在同一张表里，如果没有日期这个东西加以区别，只能人工进行判断那些数据是指定月份的。

然后，添加一个日期操作表【根据实际情况要做修改】
id tableName recordId opTime
操作Id 被操作的表名 被操作的记录 操作时间

以后根据这个表来进行指定日期的数据备份。

---解决方法2:

根据数据库日志文件进行判断，将日志文件通过某种方法转换成sql可查询的格式，根据日志，截取表名称及插入的记录和时间，根据日期进行对相应的表记录进行备份。

---更好的解决方法3: ？？？？

Answer 1

以下回答参考 皆我百晓生、券券喵儿 等免费微信小程序相关内容作答，并由本人整理回复。

要实现您描述的功能，可以使用SQL Server的存储过程或触发器来执行定期的备份和清理工作。以下是一个可能的解决方案：

解决方案1：手动备份并标记日期

步骤1：创建备份表

在您的数据库中，创建一个包含recordId、tableName、opTime（操作的时间）的临时表，用于标识特定日期的记录。

CREATE TABLE #TempBackup (
    RecordId INT,
    TableName VARCHAR(100),
    OpTime DATETIME
);

步骤2：将数据写入临时表

对于每个月的数据，先将其写入#TempBackup表，同时保留当前的记录数量。

INSERT INTO #TempBackup (RecordId, TableName, OpTime)
SELECT
    R.RecordId,
    T.TableName,
    SYSDATETIME()
FROM
    [YourTableName] AS R
JOIN
    [YourTableName] AS T ON R.TableName = T.TableName AND R.OpTime <= '2012-01-01'
WHERE
    R.TableName IN ('[YourTableName]'::VARCHAR(100)) -- 这里替换为您实际的表名

步骤3：检查并备份记录

接下来，根据当前日期在#TempBackup表中查找并备份记录。

UPDATE #TempBackup
SET BackupDate = SYSDATETIME()
WHERE BackupDate IS NULL;

这将在#TempBackup表中创建新的行，其中包含当前日期作为备份日期。

步骤4：删除旧的备份

确保在删除任何记录之前，都确认它们不再需要。例如，如果某个表在最近的一个月内没有被访问，则应该将其从备份表中移除。

DELETE FROM #TempBackup
WHERE BackupDate < SYSDATETIME();

解决方案2：利用数据库的日志文件

步骤1：创建备份表

在数据库中创建一个新的表，用于存储备份的日期和对应的记录信息。

CREATE TABLE #LogBackup (
    BackupDate DATETIME,
    TableName VARCHAR(100),
    RecordId INT,
    OpTime DATETIME
);

步骤2：更新日志

更新数据库中的所有记录，并将这些记录添加到#LogBackup表中。

UPDATE YourTable
SET OpTime = SYSDATETIME(),
    BackupDate = SYSDATETIME() 
WHERE OpTime > '2010-01-01' AND OpTime <= '2012-01-01';

步骤3：定期备份

每晚或每天运行一个脚本来更新#LogBackup表，并将其导出到另一个文件中。

-- 每天晚上运行
CREATE TRIGGER DailyBackupTrigger
ON YourTable
AFTER INSERT
AS
BEGIN
    UPDATE #LogBackup
    SET BackupDate = SYSDATETIME()
    WHERE BackupDate IS NULL;
END;

-- 每周或每月的第一天早晨运行
CREATE TRIGGER WeeklyBackupTrigger
ON YourTable
AFTER INSERT
AS
BEGIN
    UPDATE #LogBackup
    SET BackupDate = SYSDATETIME()
    WHERE BackupDate IS NULL;
END;

解决方案3：使用DTS或SSIS

如果您更倾向于使用DTS或SSIS来自动化此任务，您可以考虑使用如下步骤：

步骤1：准备DTS或SSIS项目

定义一个任务，该任务将读取指定日期范围内的数据，将数据保存到指定的备份文件中，并在必要时删除历史记录。

步骤2：编写触发器或脚本

编写触发器或脚本来处理日期变化，并相应地调整备份和删除操作。

步骤3：配置DTS或SSIS

在DTS或SSIS中设置定时触发器，以便在特定日期开始时运行上述任务。

请注意，这只是一个基本的示例，具体实现可能会根据您的数据库结构和需求有所不同。