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

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

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

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

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

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

 

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

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

 

---

备份

我们做一个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大小

 

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

刚才是备份到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盘

 

D盘

 

可以看到每个备份文件的大小是平均的，都是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>

 

---

还原

<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盘

 

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

 

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

---

总结

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

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

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o