全自动备份vss和sql数据库（含源码下载）

前言 visual source safe 里有代码，sql server 里有数据，这就是项目的全部。 拥有他们就拥有了全部，所以这些东西的安全实在是重要。 本文以批处理的方式，实现了全自动备份vss和全部sql数据库的功能。 备份数据库： 采用osql调用sql脚本的方式，自动备份

• 前言

visual source safe 里有代码，sql server 里有数据，这就是项目的全部。

拥有他们就拥有了全部，所以这些东西的安全实在是重要。

本文以批处理的方式，实现了全自动备份vss和全部sql数据库的功能。

• 备份数据库：

采用osql调用sql脚本的方式，自动备份全部数据库，并导出执行结果。

备份数据库的批处理文件（auto_backup_db.bat），他采用信任连接的方式调用auto_backup_db.sql脚本实现备份功能，

并将备份的日志写入backup_database_log.txt，为避免重名覆盖将文件名修改为backup_database_log_%DATE%.txt，

形如：backup_database_log_2011-10-09.txt。

<span>@echo</span> <span>off</span><br><br><span>@echo</span> <span>start</span> to <span>backup</span> database<br>osql -E -i auto_backup_db.sql -o backup_database_log.txt<br><span>@echo</span> finished <span>backup</span> database<br><br><span>FOR</span> /F "tokens=1-3 delims=- " %%i IN ('<span>date</span> /t') <span>DO</span> <span>SET</span> <span>DATE</span>=%%i-%%j-%%k<br><br><span>rename</span> "backup_database_log.txt" "backup_database_log_%DATE%.txt"<br><br><span>@echo</span> <span>on</span>

 备份数据库sql脚本auto_backup_db.sql如下，请根据情况修改文件备份路径：　　　　

<span>USE</span> master<br><span>go</span><br><br><span>DECLARE</span> <span>@bak_path</span> <span>NVARCHAR</span>(<span>200</span>)<br><span>DECLARE</span> <span>@bak_file_name</span> <span>NVARCHAR</span>(<span>200</span>)<br><br><span>--</span><span>设置文件备份路径</span><span><br></span><span>SET</span> <span>@bak_path</span> <span>=</span> <span>'</span><span>E:\DataBaseBAK</span><span>'</span><br><br><span>--</span><span>利用游标遍历，逐个备份数据库</span><span><br></span><span>DECLARE</span> <span>@db_name</span> SYSNAME <br><span>DECLARE</span> cur_database <span>CURSOR</span> <span>FOR</span><br><span>SELECT</span>  <span>[</span><span>name</span><span>]</span><span>--</span><span> 查询所有数据库</span><span><br></span>    <span>FROM</span>    sys.databases<br>    <span>WHERE</span>   <span>[</span><span>state</span><span>]</span> <span>=</span> <span>0</span> <span>--</span><span> 0 = ONLINE 在线状态</span><span><br></span>    <span>AND</span> <span>[</span><span>name</span><span>]</span> <span>NOT</span> <span>IN</span> ( <span>'</span><span>master</span><span>'</span>, <span>'</span><span>model</span><span>'</span>, <span>'</span><span>msdb</span><span>'</span>, <span>'</span><span>tempdb</span><span>'</span>, <span>'</span><span>ReportServer</span><span>'</span>,<span>'</span><span>ReportServerTempDB</span><span>'</span>)<br>    <span>--</span><span>系统数据库、演示数据库除外</span><span><br></span><br><span>OPEN</span> cur_database<br><span>FETCH</span> <span>NEXT</span> <span>FROM</span> cur_database <span>INTO</span> <span>@db_name</span><br><span>WHILE</span> ( <span>@@FETCH_STATUS</span> <span>=</span> <span>0</span> ) <br>    <span>BEGIN</span><br>        <span>--</span><span>设置备份文件名称，形如：dbname_2011-10-09.bak</span><span><br></span>        <span>SET</span> <span>@bak_file_name</span> <span>=</span> <span>@bak_path</span> <span>+</span> <span>'</span><span>\</span><span>'</span> <span>+</span> <span>@db_name</span> <span>+</span> <span>'</span><span>_</span><span>'</span><br>            <span>+</span> <span>CONVERT</span>(<span>VARCHAR</span>(<span>10</span>), <span>GETDATE</span>(), <span>120</span>) <span>+</span> <span>'</span><span>.bak</span><span>'</span>  <br>        <br>        <span>--</span><span>开始完整备份        </span><span><br></span>        <span>BACKUP</span> <span>DATABASE</span> <span>@db_name</span> <span>TO</span> <span>DISK</span> <span>=</span> <span>@bak_file_name</span>  <br>        <span>FETCH</span> <span>NEXT</span> <span>FROM</span> cur_database <span>INTO</span> <span>@db_name</span><br>    <span>END</span><br>    <br><span>CLOSE</span> cur_database<br><span>DEALLOCATE</span> cur_database

• 自动备份vss auto_backup_vss.bat

<span>@ECHO</span> <span>OFF</span><br>@TITLE Backing up source safe databases<br><br><span>SET</span> VSS_Install_Path="C:\Program Files\Microsoft Visual SourceSafe\"<br>SET VSS_DB="\\192.168.0.244\vss"<br>SET Bak_File="e:\%<span>DATE</span>%_vss_backup.ssa"<br>SET VSS_Admin_Name="admin"<br>SET VSS_Admin_Password="your_password"<br><br>FOR /F "tokens=1-3 delims=- " %%i IN ('date /t') DO SET DATE=%%i-%%j-%%k<br><br>%VSS_Install_Path%"ssarc" -d- -y%VSS_Admin_Name%,%VSS_Admin_Password% -s%VSS_DB%  %Bak_File% $/<br><br>@ECHO finished backup vss

请根据情况修改这些条目：

VSS_Install_Path = vss的安装路径

VSS_DB = vss数据库路径

Bak_File = 备份文件路径

VSS_Admin_Password=vss超级管理员密码