SQL Server 大数据管理——数据归档(段落备份)

背景：

在SQL Server 大数据管理——数据归档（主文件备份）一文中，提到的归档方案是将非归档数据移除主文档，把归档数据移入主文档，通过备份主文档的方式对数据进行归档。这种归档的方式需要一次归档数据移动，本文将使用段落还原的方式，解决归档数据移动的问题。

一. 主文件数据移动到辅文件

在SQL Server 大数据管理——数据归档（主文件备份）一文中已经详述移动的方案，这里不再详述

二. 归档历史数据

2.1 备份主文件

在被尾部日志的时候，要求对数据库独占，所以在备份开始的是断开网络（或者修改端口），kill掉连接数据库的进程，如下脚本可以生成killtest数据库所有连接的脚本

use master

select 'kill '+convert(varchar,spid) from sys.sysprocesses

where dbid=DB_ID(N'test')

进行文件备份归档前，做一次完整备份，以防误操作

--全备份

use master

BACKUP DATABASE [test] TO DISK=N'E:\backup\test_F.bak'

WITH NOFORMAT,NOINIT, NAME =N'test-完整数据库备份',SKIP,NOREWIND,NOUNLOAD, STATS= 10

--主文件备份

BACKUP DATABASE [test] FILEGROUP=N'PRIMARY'

TO DISK=N'E:\backup\test_PRIMARY_2018.bak'

WITH NOFORMAT,NOINIT, NAME =N'test-完整数据库备份',SKIP,NOREWIND,NOUNLOAD, STATS= 10

2.2 备份归档文件

--归档数据2019年以前的文件

use master

BACKUP DATABASE [test] FILEGROUP=N'Before2019'

TO DISK=N'E:\backup\test_Before2019.bak'

WITH NOFORMAT,NOINIT, NAME =N'test-完整数据库备份',SKIP,NOREWIND,NOUNLOAD, STATS= 10

2.3 备份结尾日志

对于完整恢复模式的数据库，进行段落还原时，需要尾部日志备份，所以备份当时的尾部日志，以供数据恢复使用：

--备份尾部日志

usemaster

BACKUPLOG [test] TO DISK=N'E:\backup\test_log.bak'

WITH NO_TRUNCATE, NOFORMAT,INIT, NAME =N'test-完整数据库备份',SKIP, NORECOVERY,NOREWIND,NOUNLOAD, STATS= 10

注意：备份尾部日志时，NO_TRUNCATE，NORECOVERY两个选项不可少

2.4 恢复生产库

备份结尾日志后，生产库处于还原状态，此时需要对生产库还原尾部日志，使其处于可用状态

RESTORELOG [test] FROM DISK=N'E:\backup\test_log_2019.bak'WITH FILE= 1, NOUNLOAD, STATS= 10

检查数据库是否正常，重开网络（或者把端口改回）

2.5 数据恢复测试

使用段落还原，恢复归档数据，主文件还原是partial（段落还原）选项，且该选项只适应于主文件还原

use master

--主文件还原时加partial（段落还原）选项,且该选项只能用在主文件还原时

RESTORE DATABASE [test2] FILE='test' FROM disk=N'E:\backup\test_PRIMARY.bak'

WITHfile=1,

MOVE N'test' TO N'D:\test2\test2.mdf',

MOVE N'test_log' TO N'D:\test2\test2_log.ldf',

PARTIAL,NORECOVERY,stats=5

--还原归档文件组

RESTORE DATABASE [test2] FILE='Before2019' FROM disk=N'E:\backup\test_Before2019.bak' WITH

MOVE N'Before2019' TO N'D:\test2\Before2019.ndf',

NORECOVERY,stats=5

--还原尾部日志

RESTORE log [test2] FROM disk=N'E:\backup\test_log_2019.bak' WITH

RECOVERY,stats=5

查询确认归档数据和生产库中归档数据一致，最后就可以删除生产库上的归档数据了。

2.6 删除归档数据

SQL Server 2016 以前版本，删除归档分区的数据，先将分区数据转为普通表数据，再truncate普通表，最后drop普通表

SQL Server 2016 增加truncate 某个分区数据的功能，具体如：

--truncate 表testpp 第1分区的数据

truncate table testpp with (partitions(1))

--truncate 表testpp 第1、2、4分区的数据

truncate table testpp with (partitions(1,2,4))

--truncate 表testpp 第1到4分区，第6分区的数据

truncate table testpp with (partitions(1 TO 4,6))

最后移除分区文件、文件组，建立归档说明及保存好相关备份及恢复脚本即可

三. 两种方案比计较

相同点：两种归档方案都涉及到主文件的备份还原

不同点：

1. SQL Server 大数据管理——数据归档（主文件备份）一文中，主文件中的数据是归档数据，只需要备份还原主文件就可以完成数据的归档和恢复，但需要多一次从辅文件中把归档数据转移到主文件中的操作，增加系统I/O压力。同时在还原主文档的时候，使用了replace选项，要防止恢复的数据库串改已有数据库（所以恢复时最好使用空实例）

本文主文件没有数据，或尽量少的非归档数据（如配置表）。如果源库为完全恢复模式，需要尾部日志，才能恢复数据；而备份结尾日志时需要独占数据库，不太适用于线上生产库，更适合数仓库类的数据归档；同时尾部日志备份完成后，原库处于还原状态，需要使用尾部日志备份，恢复源库；在恢复数据时，不仅要使用partial、norecovery选项恢复主文件，还需要恢复归档数据对应辅助文件，及尾部日志。

————————————————

原文链接：https://blog.csdn.net/zhoujunah/article/details/79669147