SQLSERVER的一个不显眼的功能备份文件的分割当完整备份数据库的时候，我们有时候可能会遇到一种极端情况，比如服务器上C,D,E三个盘符都只剩下5G空间了但是如果要完整备份业务库需要12G的空间，那么这时候怎么办呢？使用文件组备份吗？但是数据库没有做表

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

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

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

使用文件组备份吗？但是数据库没有做表分区，没有分多个文件组，就只有一个主文件组啊

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

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

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

备份

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

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

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

登录后复制

可以看到需要31MB大小

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

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

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

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

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

<span>BACKUP</span> <span>DATABASE</span> <span>[</span><span>Temp2</span><span>]</span>
<span>TO</span> 
<span>DISK</span><span>=</span><span>@FileName</span><span>,
</span><span>DISK</span><span>=</span><span>@FileName2</span>
<span>WITH</span> FORMAT

登录后复制

C盘

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

D盘

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

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

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

<span>RESTORE</span> FileListOnly <span>From</span> <span>Disk</span><span>=</span><span>'</span><span>C:\Temp2_FullBackup_Partial1_2014_12_19_150533.bak</span><span>'</span>
<span>RESTORE</span> FileListOnly <span>From</span> <span>Disk</span><span>=</span><span>'</span><span>D:\Temp2_FullBackup_Partial2_2014_12_19_150533.bak</span><span>'</span>
<span>RESTORE</span> HeaderOnly <span>From</span> <span>Disk</span><span>=</span><span>'</span><span>C:\Temp2_FullBackup_Partial1_2014_12_19_150533.bak</span><span>'</span>
<span>RESTORE</span> HeaderOnly <span>From</span> <span>Disk</span><span>=</span><span>'</span><span>D:\Temp2_FullBackup_Partial2_2014_12_19_150533.bak</span><span>'</span>

登录后复制

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

还原

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

<span>GO</span>

登录后复制

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

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

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