Home  >  Article  >  Database  >  全自动备份vss和sql数据库(含源码下载)

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

WBOY
WBOYOriginal
2016-06-07 14:59:081369browse

前言 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超级管理员密码



 

 

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn