SQL Server中从生产环境备份指定表至.bak文件（非BCP方式）

针对你遇到的生产库大表备份难题（单表2.5亿行、总备份近1TB，空间受限且无法用BCP），我推荐采用SQL Server的文件组定向备份方案，既能生成标准的.bak文件，又能避免全量备份的空间浪费，同时保证数据一致性：

1. 预准备：将目标表迁移至独立文件组 #

SQL Server的.bak备份无法直接指定单表，但可以把需要备份的表放到独立文件组中，通过备份该文件组实现"仅备份指定表"的效果：

-- 1. 为目标表创建专属文件组
ALTER DATABASE [prod] ADD FILEGROUP [FG_TargetTables];

-- 2. 向该文件组添加数据文件（根据表大小调整初始大小和增长值）
ALTER DATABASE [prod] ADD FILE 
(
    NAME = N'FG_TargetTables_Data', 
    FILENAME = N'D:\SQLStorage\FG_TargetTables_Data.ndf', -- 替换为你的存储路径
    SIZE = 1024MB, 
    FILEGROWTH = 512MB
) TO FILEGROUP [FG_TargetTables];

-- 3. 将目标表迁移至新文件组（针对聚集索引表，重建聚集索引即可）
CREATE CLUSTERED INDEX [CIX_YourTableName] ON [dbo].[YourTableName]
(
    YourClusteredKeyColumn -- 替换为你的表聚集键
)
WITH (DROP_EXISTING = ON, ONLINE = ON) -- ONLINE选项避免锁表（需企业版，标准版可在低峰期移除该参数）
ON [FG_TargetTables];

如果有多个需要备份的表，可以全部迁移至同一个文件组，或者为每个表创建单独的文件组（按需选择）。

2. 执行文件组备份生成.bak文件 #

现在可以直接备份该文件组，生成的.bak仅包含目标表的数据，空间占用远小于全量备份：

BACKUP DATABASE [prod] 
FILEGROUP = N'FG_TargetTables' -- 指定要备份的文件组
TO DISK = N'D:\Backups\prod_TargetTables.bak' -- 备份文件路径
WITH 
    INIT, -- 覆盖现有备份文件（如需追加改为NOINIT）
    COMPRESSION, -- 启用压缩节省空间（需企业版/标准版2016+）
    COPY_ONLY; -- 不影响现有备份链，适合临时备份场景

3. 保障频繁插入场景下的数据一致性 #

由于生产库存在大量插入操作，为确保备份数据的一致性，需配合事务日志备份：

-- 若数据库采用完整恢复模式，先备份当前事务日志
BACKUP LOG [prod] 
TO DISK = N'D:\Backups\prod_Log_PreBackup.bak'
WITH INIT, COMPRESSION;

-- 再执行文件组备份（同步骤2的命令）

这样后续恢复时，可通过日志备份将数据恢复到备份完成的时间点，避免插入操作导致的数据不一致。

4. 恢复验证示例 #

如果需要验证备份的可用性，可执行以下恢复操作：

-- 恢复文件组备份（NORECOVERY状态，等待日志恢复）
RESTORE DATABASE [prod_Restored] 
FILEGROUP = N'FG_TargetTables'
FROM DISK = N'D:\Backups\prod_TargetTables.bak'
WITH 
    NORECOVERY,
    MOVE N'FG_TargetTables_Data' TO N'D:\SQLStorage\prod_Restored_FG_TargetTables_Data.ndf'; -- 恢复后的文件路径

-- 恢复事务日志，完成恢复
RESTORE LOG [prod_Restored] 
FROM DISK = N'D:\Backups\prod_Log_PreBackup.bak'
WITH RECOVERY;

恢复完成后，你可以在prod_Restored数据库中查看目标表的数据是否完整。

为什么不推荐BCP？ #

• BCP导出的是平面文件，不是你需要的.bak格式；
• 针对2.5亿行的大表，BCP导出速度慢，且在频繁插入场景下，若不启用快照隔离，导出的数据可能存在不一致性；
• 导出后若要恢复为表，还需额外的导入操作，效率远低于直接恢复.bak文件。

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