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

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

 

背景：

 

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

 

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

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

 

二.    归档历史数据

2.1  备份主文件

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

use master
go
select 'kill '+convert(varchar,spid) from sys.sysprocesses
where dbid=DB_ID(N'test')

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

--全备份
use master
go
BACKUP DATABASE  [test] TO  DISK=N'E:\backup\test_F.bak'
 WITH NOFORMAT,NOINIT,  NAME =N'test-完整 数据库 备份',SKIP,NOREWIND,NOUNLOAD,  STATS= 10
GO
--主文件备份
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
GO

2.2  备份归档文件

--归档数据2019年以前的文件
use master
go
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
GO

2.3  备份结尾日志

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

--备份尾部日志
usemaster
go
BACKUPLOG [test] TO  DISK=N'E:\backup\test_log.bak'
WITH  NO_TRUNCATE,  NOFORMAT,INIT,  NAME =N'test-完整 数据库 备份',SKIP, NORECOVERY,NOREWIND,NOUNLOAD,   STATS= 10
GO

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

2.4  恢复生产库

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

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

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

2.5  数据恢复测试

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

use master
GO
--主文件还原时加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
GO
 
--还原归档文件组
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
GO
 
--还原尾部日志
RESTORE log  [test2] FROM disk=N'E:\backup\test_log_2019.bak' WITH
RECOVERY,stats=5
GO

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

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选项恢复主文件，还需要恢复归档数据对应辅助文件，及尾部日志。

如果喜欢，可以扫码关注SQL Server 公众号，将有更多精彩内容分享：