SQL Server 2019：用Partition SWITCH归档记录并规避静态数据重复备份

要同时实现旧数据归档到独立表，且冷数据与热数据分属不同文件组（支持单独备份恢复），可以通过分区切换+临时过渡表+分文件组分区架构的组合方案解决，具体如下：

核心思路 #

Partition SWITCH要求源/目标分区必须同文件组，因此我们需要用一个临时过渡表作为中间层：先把源表的旧分区切换到同文件组的临时表，再将临时表的分区迁移到冷数据文件组，最后切换到归档表。这样既满足SWITCH的约束，又实现了冷热数据的文件组隔离。

具体实施步骤 #

1. 预先搭建归档表的分区架构 #

首先为归档表创建独立的冷数据文件组和分区方案，确保每个归档月份（或按季度/年度）的分区对应单独的冷文件组：

-- 创建冷数据文件组（示例为2023年1月）
ALTER DATABASE YourDatabase ADD FILEGROUP FG_Archive_202301;
-- 为文件组添加数据文件（根据实际路径调整）
ALTER DATABASE YourDatabase ADD FILE (
    NAME = N'FG_Archive_202301_Data',
    FILENAME = N'D:\SQLData\FG_Archive_202301.ndf',
    SIZE = 100MB, FILEGROWTH = 100MB
) TO FILEGROUP FG_Archive_202301;

-- 创建归档用的分区函数（按YYYYMM范围分区）
CREATE PARTITION FUNCTION pf_Archive_MonthId(int)
AS RANGE RIGHT FOR VALUES (202301, 202302, 202303); -- 根据需要扩展范围

-- 创建归档分区方案，每个分区映射到对应冷文件组
CREATE PARTITION SCHEME ps_Archive_MonthId
AS PARTITION pf_Archive_MonthId
TO (FG_Archive_202301, FG_Archive_202302, FG_Archive_202303, [PRIMARY]);

-- 创建归档表，结构、约束、索引必须与源表完全一致
CREATE TABLE dbo.ArchiveTable (
    -- 复制源表的列定义、主键、约束
    ID INT PRIMARY KEY,
    MonthId INT NOT NULL,
    -- 其他列...
) ON ps_Archive_MonthId(MonthId);

-- 复制源表的非聚集索引（若有）
CREATE NONCLUSTERED INDEX IX_ArchiveTable_ColumnX
ON dbo.ArchiveTable(ColumnX)
ON ps_Archive_MonthId(MonthId);

2. 月度归档操作流程（以MonthId=202301为例） #

步骤1：创建临时过渡表

临时表必须与源表结构、约束、索引完全匹配，且分区与源表的目标分区同属热数据文件组：

-- 复制源表结构（含约束）
SELECT TOP 0 * INTO dbo.Temp_Archive_202301 FROM dbo.SourceTable;
-- 创建与源表一致的聚集索引（分区方式沿用源表的分区方案）
CREATE CLUSTERED INDEX IX_Temp_Archive_MonthId
ON dbo.Temp_Archive_202301(MonthId)
WITH (DROP_EXISTING = ON)
ON ps_Source_MonthId(MonthId); -- ps_Source_MonthId是源表的分区方案

-- 复制源表的非聚集索引（若有）
CREATE NONCLUSTERED INDEX IX_Temp_Archive_ColumnX
ON dbo.Temp_Archive_202301(ColumnX)
ON ps_Source_MonthId(MonthId);

步骤2：将源表旧分区切换到临时表

此时数据仍在热数据文件组，但已从源表分离：

ALTER TABLE dbo.SourceTable
SWITCH PARTITION $PARTITION.pf_Source_MonthId(202301)
TO dbo.Temp_Archive_202301
PARTITION $PARTITION.pf_Source_MonthId(202301);

步骤3：将临时表分区迁移到冷数据文件组

重建临时表的索引，把分区转移到对应的冷文件组：

-- 确保归档分区方案已将目标冷文件组设为下一个可用（若首次使用该文件组）
ALTER PARTITION SCHEME ps_Archive_MonthId NEXT USED FG_Archive_202301;

-- 重建聚集索引到冷数据文件组，同时保留原有结构
CREATE CLUSTERED INDEX IX_Temp_Archive_MonthId
ON dbo.Temp_Archive_202301(MonthId)
WITH (DROP_EXISTING = ON, ONLINE = ON) -- 企业版可加ONLINE避免锁表
ON ps_Archive_MonthId(MonthId);

-- 同步重建非聚集索引到冷文件组
CREATE NONCLUSTERED INDEX IX_Temp_Archive_ColumnX
ON dbo.Temp_Archive_202301(ColumnX)
WITH (DROP_EXISTING = ON, ONLINE = ON)
ON ps_Archive_MonthId(MonthId);

步骤4：将临时表分区切换到归档表

此时临时表的分区已在冷数据文件组，且归档表对应分区为空，执行切换完成归档：

ALTER TABLE dbo.Temp_Archive_202301
SWITCH PARTITION $PARTITION.pf_Archive_MonthId(202301)
TO dbo.ArchiveTable
PARTITION $PARTITION.pf_Archive_MonthId(202301);

步骤5：清理临时表

DROP TABLE dbo.Temp_Archive_202301;

3. 备份与恢复策略优化 #

• 热数据文件组：执行常规的完整/差异/日志备份，频率根据业务需求设置（如每日差异+每小时日志）。
• 冷数据文件组：仅在归档完成后执行一次完整备份即可，后续无需重复备份（因为冷数据不会再被修改）。恢复时：
  • 若仅需恢复活跃数据，可在RESTORE命令中指定SKIP冷数据文件组，只加载热数据备份。
  • 若需完整恢复，再加载冷数据的全量备份。

关键注意事项 #

• 源表、临时表、归档表的结构必须完全一致：包括列名、数据类型、精度、约束（主键、外键、检查约束）、索引的结构和分区方式。
• 归档表的目标分区必须为空才能执行SWITCH操作，因此需要确保归档逻辑不会重复处理同一月份的数据。
• 索引重建操作在非企业版中是离线的，会锁定表，建议在业务低峰期执行；企业版可使用ONLINE = ON参数避免锁表。
• 可以将上述归档流程封装为存储过程，通过SQL Agent作业按月自动执行。

内容的提问来源于stack exchange，提问作者SQL_Guy