SQLServer 批量备份与还原

备份与还原是数据库避不开的主题，而作为DBA，经常会面临将一台机器上的所有数据库重新构建到一台新机器上的要求； 在现在都讲究自动化管理的时代，传统的界面操作备份还原的做法不仅浪费时间和精力，而且还很容易出错，并且这次完成后， 下次再有这样的要求

备份与还原是数据库避不开的主题，而作为DBA，经常会面临将一台机器上的所有数据库重新构建到一台新机器上的要求；

在现在都讲究自动化管理的时代，传统的界面操作备份还原的做法不仅浪费时间和精力，而且还很容易出错，并且这次完成后，

下次再有这样的要求，必须又重头开始（估计做5次就能做得人狂吐）；于是，我们需要一套应对这种频繁操作、耗时、耗精力

的通用处理方法，所以以下批处理脚本就诞生了。

脚本主要的功能：

1. 备份一个服务器上的所有数据库（当然你也可以选择），备份文件按数据库名+日期生成，以.bak 结尾；

2. 将所有的备份文件还原到一台新机器上；

3. 验证磁盘和路径的正确性；

说明：

脚本合适 SQLServer 2005 & 2008 版本；

批量备份数据库：

<span>--</span><span>---------------------------批量备份数据-------------------------------------------</span><span><br /></span><span>Use</span><span> master<br /></span><span>GO</span><span><br /></span><span>/*</span><span>=================Usp_BackUp_DataBase========================<br />  =====BackUp Sigle DataBase                            ======<br />  =====Ken.Guo                                          ======<br />  =====2010.9.10                                         ======<br />  =====Version: 2005 & 2008 SQL Server                  ======<br />  =====EXEC Usp_BackUp_DataBase 'MyDB','D:\BackUp'      ======<br />  ============================================================<br /></span><span>*/</span><span><br /></span><span>CREATE</span><span>PROC</span><span>[</span><span>dbo</span><span>]</span><span>.</span><span>[</span><span>Usp_BackUp_DataBase</span><span>]</span><span>@DatabaseName</span><span>nvarchar</span><span>(</span><span>200</span><span>),</span><span>@Path</span><span>nvarchar</span><span>(</span><span>200</span><span>)   <br /></span><span>AS</span><span>  <br />  </span><span>BEGIN</span><span> <br />  </span><span>DECLARE</span><span>@fn</span><span>varchar</span><span>(</span><span>200</span><span>)<br />           ,</span><span>@sql</span><span>varchar</span><span>(</span><span>1000</span><span>)   <br />  </span><span>SET</span><span>@fn</span><span>=</span><span>@Path</span><span>+</span><span>(</span><span>case</span><span>when</span><span>right</span><span>(</span><span>@Path</span><span>,</span><span>1</span><span>)   </span><span></span><span>'</span><span>\</span><span>'</span><span>then</span><span>'</span><span>\</span><span>'</span><span>else</span><span>''</span><span>end</span><span>)   <br />  </span><span>+</span><span>@DatabaseName</span><span>+</span><span>'</span><span>_</span><span>'</span><span>   <br />  </span><span>+</span><span>convert</span><span>(</span><span>char</span><span>(</span><span>8</span><span>),</span><span>getdate</span><span>(),</span><span>112</span><span>)</span><span>+</span><span>'</span><span>_</span><span>'<div>本文来源gaodai.ma#com搞#代!码网_</div></span><span>   <br /> </span><span>+</span><span>replace</span><span>(</span><span>convert</span><span>(</span><span>char</span><span>(</span><span>8</span><span>),</span><span>getdate</span><span>(),</span><span>108</span><span>),</span><span>'</span><span>:</span><span>'</span><span>,</span><span>''</span><span>)   <br />  </span><span>+</span><span>'</span><span>.bak</span><span>'</span><span>   <br />  </span><span>set</span><span>@sql</span><span>=</span><span>'</span><span>backup   database   </span><span>'</span><span>+</span><span>@DatabaseName</span><span>+</span><span>'</span><span>   to   disk   =   N</span><span>'''</span><span>+</span><span>@fn</span><span>+</span><span>''''</span><span>   <br />  </span><span>--</span><span>SELECT @sql </span><span><br /></span><span>EXEC</span><span>(</span><span>@sql</span><span>)  <br />  </span><span>END</span><span><br /><br /></span><span>GO</span><span><br /><br /></span><span>Use</span><span> master<br /></span><span>GO</span><span><br /></span><span>/*</span><span>=============BackUp Mutile DataBase=========================</span><span>*/</span><span><br /></span><span>DECLARE</span><span>@dbname</span><span>nvarchar</span><span>(</span><span>200</span><span>)<br />       ,</span><span>@backup_path</span><span>nvarchar</span><span>(</span><span>200</span><span>)<br /></span><span>SET</span><span>@backup_path</span><span>=</span><span>'</span><span>D:\BackUp\</span><span>'</span><span><br /></span><span>DECLARE</span><span> db_info </span><span>CURSOR</span><span> <br />    LOCAL <br />    STATIC <br />    READ_ONLY <br />    FORWARD_ONLY <br /></span><span>FOR</span><span>--</span><span>根据查询，添加其他筛选条件</span><span><br /></span><span>SELECT</span><span> <br />      name <br />  </span><span>FROM</span><span> master.sys.databases </span><span>WITH</span><span>(NOLOCK) <br />  </span><span>WHERE</span><span> <br />      database_id</span><span>></span><span>4</span><span><br /><br /></span><span>OPEN</span><span> db_info<br /></span><span>FETCH</span><span>NEXT</span><span>FROM</span><span> db_info </span><span>INTO</span><span>@dbname</span><span><br /><br /></span><span>WHILE</span><span>@@FETCH_STATUS</span><span>=</span><span>0</span><span><br /> </span><span>begin</span><span><br />  </span><span>EXEC</span><span> master.dbo.<span>Usp_BackUp_DataBase </span></span><span>@dbname</span><span>,</span><span>@backup_path</span><span><br />  </span><span>FETCH</span><span>NEXT</span><span>FROM</span><span> db_info </span><span>INTO</span><span>@dbname</span><span><br /> </span><span>END</span><span><br /></span><span>close</span><span> db_info<br /></span><span>deallocate</span><span> db_info<br /><br /></span><span>--</span><span>-------------------------------BackUp DataBase End------------------------------------</span>

喜欢 (0)赏

[搞代码]

分享 (0)