SQL Server的一个不显眼的功能备份文件分割

背景

当完整备份数据库的时候，我们有时候可能会遇到一种极端情况，比如服务器上C,D,E三个盘符都只剩下5G空间了

但是如果要完整备份业务库需要12G的空间，那么这时候怎么办呢？

使用文件组备份吗？但是数据库没有做表分区，没有分开多个文件组，就只有一个主文件组 另外一个问题是，对于超大型数据库，比如单个数据库达到几十个TB的量级，分割备份除了解决磁盘空间不足问题还可以节省备份时间

这时候我们可以使用备份文件分割

使用自己机器示范一下，我的机器上有一个Temp2的数据库，数据库大小为1GB

备份

我们做一个Temp2数据库的完整备份

DECLARE @CurrentTime VARCHAR(50), @FileName VARCHAR(200)
SET @CurrentTime = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120 ),'-','_'),' ','_'),':','')

        
--(Temp2 数据库完整备份)
SET @FileName = 'C:\Temp2_FullBackup_' + @CurrentTime+'.bak'
BACKUP DATABASE [Temp2]
TO DISK=@FileName WITH FORMAT 

可以看到需要31MB大小

那么如何分割备份文件呢？方法很简单

刚才是备份到C盘，现在我们备份到C盘和D盘

DECLARE @CurrentTime VARCHAR(50), @FileName VARCHAR(200),@FileName2 VARCHAR(200)
SET @CurrentTime = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 120 ),'-','_'),' ','_'),':','')

        
--(Temp2 数据库完整备份)
SET @FileName = 'C:\Temp2_FullBackup_Partial1_' + @CurrentTime+'.bak'
SET @FileName2 = 'D:\Temp2_FullBackup_Partial2_' + @CurrentTime+'.bak'

BACKUP DATABASE [Temp2]
TO 
DISK=@FileName,
DISK=@FileName2
WITH FORMAT 

C盘

D盘

可以看到每个备份文件的大小是平均的，都是16MB，如果是分成3个备份文件，那么就除以3，就是每个备份文件的大小 可以看到每个备份文件的大小是平均的，都是16MB，如果是分成3个备份文件，那么就除以3，就是每个备份文件的大小

当然，如果你要查询备份文件的信息，无论查询哪个备份文件都是可以查询出来的

RESTORE FileListOnly From Disk='C:\Temp2_FullBackup_Partial1_2014_12_19_150533.bak'
RESTORE FileListOnly From Disk='D:\Temp2_FullBackup_Partial2_2014_12_19_150533.bak'
RESTORE HeaderOnly From Disk='C:\Temp2_FullBackup_Partial1_2014_12_19_150533.bak'
RESTORE HeaderOnly From Disk='D:\Temp2_FullBackup_Partial2_2014_12_19_150533.bak'

还原

USE [master]
RESTORE DATABASE [Temp2] 
FROM  
DISK = N'D:\Temp2_FullBackup_Partial1_2014_12_19_150533.bak',
DISK = N'D:\Temp2_FullBackup_Partial2_2014_12_19_150533.bak' 
WITH  FILE = 1,  
MOVE N'Temp' TO N'E:\DataBase\Temp2.mdf',  
MOVE N'Temp_log' TO N'E:\DataBase\Temp2_log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 5

GO

还原的时候只需要指定所有的备份分割文件的路径就可以了，当然我们一般在服务器搬迁的时候都会把这些备份文件一起放到新服务器的同一个盘符下面，方便还原

而不会一个放C盘，一个放D盘，一个放E盘

还原好了，我们查询一下数据

还原出来的数据库没有问题，可以收工了

总结

有时候当服务器的任何一个盘符的空间都不足以放下一个完整备份文件，但是又急需要做一个完整备份，那么可以采取这种办法

当然，你也可以插入一个移动硬盘，将数据库备份到一个移动硬盘里去，但是当你做集群搬迁，只能远程到服务器去做备份的时候，这种办法就比较有用了

还有一个就是不需要用其他压缩软件来压缩分包了，SQL Server本身就存在这个牛逼的功能！

参考文章

https://www.sqlshack.com/split-sql-database-backups-into-multiple-backup-files-using-ssms/ 

https://blog.sqlauthority.com/2021/02/18/sql-server-creating-multiple-backup-files/ 

https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16 https://www.mssqltips.com/sqlservertip/5668/sql-server-script-to-automatically-split-database-backups-into-multiple-backup-files/