SQL Server 大數(shù)據(jù)管理——數(shù)據(jù)歸檔(段落備份)
背景：
在SQL Server 大數(shù)據(jù)管理——數(shù)據(jù)歸檔（主文件備份）一文中，提到的歸檔方案是將非歸檔數(shù)據(jù)移除主文檔，把歸檔數(shù)據(jù)移入主文檔，通過備份主文檔的方式對(duì)數(shù)據(jù)進(jìn)行歸檔。這種歸檔的方式需要一次歸檔數(shù)據(jù)移動(dòng)，本文將使用段落還原的方式，解決歸檔數(shù)據(jù)移動(dòng)的問題。
一. ???主文件數(shù)據(jù)移動(dòng)到輔文件
在SQL Server 大數(shù)據(jù)管理——數(shù)據(jù)歸檔（主文件備份）一文中已經(jīng)詳述移動(dòng)的方案，這里不再詳述
二. ???歸檔歷史數(shù)據(jù)
2.1??備份主文件
在被尾部日志的時(shí)候，要求對(duì)數(shù)據(jù)庫獨(dú)占，所以在備份開始的是斷開網(wǎng)絡(luò)（或者修改端口），kill掉連接數(shù)據(jù)庫的進(jìn)程，如下腳本可以生成killtest數(shù)據(jù)庫所有連接的腳本
use master
go
select 'kill '+convert(varchar,spid) from sys.sysprocesses
where dbid=DB_ID(N'test')
進(jìn)行文件備份歸檔前，做一次完整備份，以防誤操作
--全備份
use master
go
BACKUP DATABASE? [test] TO? DISK=N'E:\backup\test_F.bak'
WITH NOFORMAT,NOINIT,? NAME =N'test-完整 數(shù)據(jù)庫 備份',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-完整 數(shù)據(jù)庫 備份',SKIP,NOREWIND,NOUNLOAD,? STATS= 10
GO
2.2??備份歸檔文件
--歸檔數(shù)據(jù)2019年以前的文件
use master
go
BACKUP DATABASE [test] FILEGROUP=N'Before2019'
TO? DISK=N'E:\backup\test_Before2019.bak'
WITH NOFORMAT,NOINIT,? NAME =N'test-完整 數(shù)據(jù)庫 備份',SKIP,NOREWIND,NOUNLOAD,? STATS= 10
GO
2.3??備份結(jié)尾日志
對(duì)于完整恢復(fù)模式的數(shù)據(jù)庫，進(jìn)行段落還原時(shí)，需要尾部日志備份，所以備份當(dāng)時(shí)的尾部日志，以供數(shù)據(jù)恢復(fù)使用：
--備份尾部日志
usemaster
go
BACKUPLOG [test] TO? DISK=N'E:\backup\test_log.bak'
WITH? NO_TRUNCATE,? NOFORMAT,INIT,? NAME =N'test-完整 數(shù)據(jù)庫 備份',SKIP, NORECOVERY,NOREWIND,NOUNLOAD,? STATS= 10
GO
注意：備份尾部日志時(shí)，NO_TRUNCATE，NORECOVERY兩個(gè)選項(xiàng)不可少
2.4??恢復(fù)生產(chǎn)庫
備份結(jié)尾日志后，生產(chǎn)庫處于還原狀態(tài)，此時(shí)需要對(duì)生產(chǎn)庫還原尾部日志，使其處于可用狀態(tài)
RESTORELOG [test] FROM? DISK=N'E:\backup\test_log_2019.bak'WITH? FILE= 1,? NOUNLOAD,? STATS= 10
GO
檢查數(shù)據(jù)庫是否正常，重開網(wǎng)絡(luò)（或者把端口改回）
2.5??數(shù)據(jù)恢復(fù)測試
使用段落還原，恢復(fù)歸檔數(shù)據(jù)，主文件還原是partial（段落還原）選項(xiàng)，且該選項(xiàng)只適應(yīng)于主文件還原
use master
GO
--主文件還原時(shí)加partial（段落還原）選項(xiàng),且該選項(xiàng)只能用在主文件還原時(shí)
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
查詢確認(rèn)歸檔數(shù)據(jù)和生產(chǎn)庫中歸檔數(shù)據(jù)一致，最后就可以刪除生產(chǎn)庫上的歸檔數(shù)據(jù)了。
2.6??刪除歸檔數(shù)據(jù)
SQL Server 2016 以前版本，刪除歸檔分區(qū)的數(shù)據(jù)，先將分區(qū)數(shù)據(jù)轉(zhuǎn)為普通表數(shù)據(jù)，再truncate普通表，最后drop普通表
SQL Server 2016 增加truncate 某個(gè)分區(qū)數(shù)據(jù)的功能，具體如：
--truncate 表testpp 第1分區(qū)的數(shù)據(jù)
truncate table testpp with (partitions(1))
--truncate 表testpp 第1、2、4分區(qū)的數(shù)據(jù)
truncate table testpp with (partitions(1,2,4))
--truncate 表testpp 第1到4分區(qū)，第6分區(qū)的數(shù)據(jù)
truncate table testpp with (partitions(1 TO 4,6))
最后移除分區(qū)文件、文件組，建立歸檔說明及保存好相關(guān)備份及恢復(fù)腳本即可
三. ???兩種方案比計(jì)較
相同點(diǎn)：兩種歸檔方案都涉及到主文件的備份還原
不同點(diǎn)：
1.????????SQL Server 大數(shù)據(jù)管理——數(shù)據(jù)歸檔（主文件備份）一文中，主文件中的數(shù)據(jù)是歸檔數(shù)據(jù)，只需要備份還原主文件就可以完成數(shù)據(jù)的歸檔和恢復(fù)，但需要多一次從輔文件中把歸檔數(shù)據(jù)轉(zhuǎn)移到主文件中的操作，增加系統(tǒng)I/O壓力。同時(shí)在還原主文檔的時(shí)候，使用了replace選項(xiàng)，要防止恢復(fù)的數(shù)據(jù)庫串改已有數(shù)據(jù)庫（所以恢復(fù)時(shí)最好使用空實(shí)例）
本文主文件沒有數(shù)據(jù)，或盡量少的非歸檔數(shù)據(jù)（如配置表）。如果源庫為完全恢復(fù)模式，需要尾部日志，才能恢復(fù)數(shù)據(jù)；而備份結(jié)尾日志時(shí)需要獨(dú)占數(shù)據(jù)庫，不太適用于線上生產(chǎn)庫，更適合數(shù)倉庫類的數(shù)據(jù)歸檔；同時(shí)尾部日志備份完成后，原庫處于還原狀態(tài)，需要使用尾部日志備份，恢復(fù)源庫；在恢復(fù)數(shù)據(jù)時(shí)，不僅要使用partial、norecovery選項(xiàng)恢復(fù)主文件，還需要恢復(fù)歸檔數(shù)據(jù)對(duì)應(yīng)輔助文件，及尾部日志。
————————————————
版權(quán)聲明：本文為CSDN博主「三空道人」的原創(chuàng)文章，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請(qǐng)附上原文出處鏈接及本聲明。
原文鏈接：https://blog.csdn.net/zhoujunah/article/details/79669147
