T-SQL语句实现数据库备份与还原代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 T-SQL语句实现数据库备份与还原
作者:Dezai.CN / 发布于2011/11/17/ 743
<div>
<pre><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">利用T-SQL语句,实现数据库的备份与还原的功能 </span><span style="color: rgb(0,128,128)">----</span><span style="color: rgb(0,128,128)">体现了SQL Server中的四个知识点： </span><span style="color: rgb(0,128,128)">----</span><span style="color: rgb(0,128,128)">1． 获取SQL Server服务器上的默认目录 </span><span style="color: rgb(0,128,128)">----</span><span style="color: rgb(0,128,128)">2． 备份SQL语句的使用 </span><span style="color: rgb(0,128,128)">----</span><span style="color: rgb(0,128,128)">3． 恢复SQL语句的使用，同时考虑了强制恢复时关闭其他用户进程的处理 </span><span style="color: rgb(0,128,128)">----</span><span style="color: rgb(0,128,128)">4． 作业创建SQL语句的使用</span><span style="color: rgb(0,128,128)"></span> <span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">1.--得到数据库的文件目录@dbname 指定要取得目录的数据库名 如果指定的数据不存在,返回安装SQL时设置的默认数据目录 如果指定NULL,则返回默认的SQL备份目录名 </span><span style="color: rgb(0,128,128)">*/</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">--调用示例 select 数据库文件目录=dbo.f_getdbpath('tempdb') ,[默认SQL SERVER数据目录]=dbo.f_getdbpath('') ,[默认SQL SERVER备份目录]=dbo.f_getdbpath(null) --</span><span style="color: rgb(0,128,128)">*/</span> <span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(128,128,128)">exists</span> (<span style="color: rgb(0,0,255)">select</span> <span style="color: rgb(128,128,128)">*</span> <span style="color: rgb(0,0,255)">from</span> dbo.sysobjects <span style="color: rgb(0,0,255)">where</span> id <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,255)">object_id</span>(N<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">[dbo].[f_getdbpath]</span><span style="color: rgb(255,0,0)">'</span>) <span style="color: rgb(128,128,128)">and</span> xtype <span style="color: rgb(128,128,128)">in</span> (N<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">FN</span><span style="color: rgb(255,0,0)">'</span>, N<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">IF</span><span style="color: rgb(255,0,0)">'</span>, N<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">TF</span><span style="color: rgb(255,0,0)">'</span>)) <span style="color: rgb(0,0,255)">drop</span> <span style="color: rgb(0,0,255)">function</span> <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">dbo</span><span style="color: rgb(255,0,0)">]</span>.<span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">f_getdbpath</span><span style="color: rgb(255,0,0)">]</span> <span style="color: rgb(0,0,255)">GO</span><span style="color: rgb(0,0,255)">create</span> <span style="color: rgb(0,0,255)">function</span> f_getdbpath(<span style="color: rgb(0,128,0)">@dbname</span> sysname) <span style="color: rgb(0,0,255)">returns</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">260</span>) <span style="color: rgb(0,0,255)">as</span> <span style="color: rgb(0,0,255)">begin</span> <span style="color: rgb(0,0,255)">declare</span> <span style="color: rgb(0,128,0)">@re</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">260</span>) <span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(0,128,0)">@dbname</span> <span style="color: rgb(0,0,255)">is</span> <span style="color: rgb(0,0,255)">null</span> <span style="color: rgb(128,128,128)">or</span> <span style="color: rgb(255,0,255)">db_id</span>(<span style="color: rgb(0,128,0)">@dbname</span>) <span style="color: rgb(0,0,255)">is</span> <span style="color: rgb(0,0,255)">null</span> <span style="color: rgb(0,0,255)">select</span> <span style="color: rgb(0,128,0)">@re</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">rtrim</span>(<span style="color: rgb(255,0,255)">reverse</span>(filename)) <span style="color: rgb(0,0,255)">from</span> master..sysdatabases <span style="color: rgb(0,0,255)">where</span> name<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">master</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(0,0,255)">select</span> <span style="color: rgb(0,128,0)">@re</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">rtrim</span>(<span style="color: rgb(255,0,255)">reverse</span>(filename)) <span style="color: rgb(0,0,255)">from</span> master..sysdatabases <span style="color: rgb(0,0,255)">where</span> name<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,128,0)">@dbname</span><span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(0,128,0)">@dbname</span> <span style="color: rgb(0,0,255)">is</span> <span style="color: rgb(0,0,255)">null</span> <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@re</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">reverse</span>(<span style="color: rgb(255,0,255)">substring</span>(<span style="color: rgb(0,128,0)">@re</span>,<span style="color: rgb(255,0,255)">charindex</span>(<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">\</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@re</span>)<span style="color: rgb(128,128,128)">+</span><span style="color: rgb(128,0,0); font-weight: bold">5</span>,<span style="color: rgb(128,0,0); font-weight: bold">260</span>))<span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">BACKUP</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@re</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">reverse</span>(<span style="color: rgb(255,0,255)">substring</span>(<span style="color: rgb(0,128,0)">@re</span>,<span style="color: rgb(255,0,255)">charindex</span>(<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">\</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@re</span>),<span style="color: rgb(128,0,0); font-weight: bold">260</span>)) <span style="color: rgb(0,0,255)">return</span>(<span style="color: rgb(0,128,0)">@re</span>) <span style="color: rgb(0,0,255)">end</span> <span style="color: rgb(0,0,255)">go</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">2.--备份数据库</span><span style="color: rgb(0,128,128)">*/</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">--调用示例--备份当前数据库 exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_db.bak'--差异备份当前数据库 exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_df.bak',@bktype='DF'--备份当前数据库日志 exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_log.bak',@bktype='LOG'--</span><span style="color: rgb(0,128,128)">*/</span><span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(128,128,128)">exists</span> (<span style="color: rgb(0,0,255)">select</span> <span style="color: rgb(128,128,128)">*</span> <span style="color: rgb(0,0,255)">from</span> dbo.sysobjects <span style="color: rgb(0,0,255)">where</span> id <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,255)">object_id</span>(N<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">[dbo].[p_backupdb]</span><span style="color: rgb(255,0,0)">'</span>) <span style="color: rgb(128,128,128)">and</span> <span style="color: rgb(255,0,255)">OBJECTPROPERTY</span>(id, N<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">IsProcedure</span><span style="color: rgb(255,0,0)">'</span>) <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(128,0,0); font-weight: bold">1</span>) <span style="color: rgb(0,0,255)">drop</span> <span style="color: rgb(0,0,255)">procedure</span> <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">dbo</span><span style="color: rgb(255,0,0)">]</span>.<span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">p_backupdb</span><span style="color: rgb(255,0,0)">]</span> <span style="color: rgb(0,0,255)">GO</span><span style="color: rgb(0,0,255)">create</span> <span style="color: rgb(0,0,255)">proc</span> p_backupdb <span style="color: rgb(0,128,0)">@dbname</span> sysname<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">要备份的数据库名称,不指定则备份当前数据库 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@bkpath</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">260</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">备份文件的存放目录,不指定则使用SQL默认的备份目录 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@bkfname</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">260</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@bktype</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">10</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">DB</span><span style="color: rgb(255,0,0)">'</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@appendfile</span> <span style="color: rgb(0,0,255)">bit</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">追加/覆盖备份文件 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">as</span> <span style="color: rgb(0,0,255)">declare</span> <span style="color: rgb(0,128,0)">@sql</span> <span style="color: rgb(0,0,255)">varchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">8000</span>) <span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(255,0,255)">isnull</span>(<span style="color: rgb(0,128,0)">@dbname</span>,<span style="color: rgb(255,0,0)">''</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span> <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@dbname</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">db_name</span>() <span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(255,0,255)">isnull</span>(<span style="color: rgb(0,128,0)">@bkpath</span>,<span style="color: rgb(255,0,0)">''</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span> <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@bkpath</span><span style="color: rgb(128,128,128)">=</span>dbo.f_getdbpath(<span style="color: rgb(0,0,255)">null</span>) <span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(255,0,255)">isnull</span>(<span style="color: rgb(0,128,0)">@bkfname</span>,<span style="color: rgb(255,0,0)">''</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span> <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@bkfname</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">\DBNAME\_\DATE\_\TIME\.BAK</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@bkfname</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">replace</span>(<span style="color: rgb(255,0,255)">replace</span>(<span style="color: rgb(255,0,255)">replace</span>(<span style="color: rgb(0,128,0)">@bkfname</span>,<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">\DBNAME\</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@dbname</span>) ,<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">\DATE\</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(255,0,255)">convert</span>(<span style="color: rgb(0,0,255)">varchar</span>,<span style="color: rgb(255,0,255)">getdate</span>(),<span style="color: rgb(128,0,0); font-weight: bold">112</span>)) ,<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">\TIME\</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(255,0,255)">replace</span>(<span style="color: rgb(255,0,255)">convert</span>(<span style="color: rgb(0,0,255)">varchar</span>,<span style="color: rgb(255,0,255)">getdate</span>(),<span style="color: rgb(128,0,0); font-weight: bold">108</span>),<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">:</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(255,0,0)">''</span>)) <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">backup </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">case</span> <span style="color: rgb(0,128,0)">@bktype</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">LOG</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">log </span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">database </span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">end</span> <span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@dbname</span> <span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)"> to disk=</span><span style="color: rgb(255,0,0)">'''</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@bkpath</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@bkfname</span> <span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'''</span><span style="color: rgb(255,0,0)"> with </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">case</span> <span style="color: rgb(0,128,0)">@bktype</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">DF</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">DIFFERENTIAL,</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(255,0,0)">''</span> <span style="color: rgb(0,0,255)">end</span> <span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">case</span> <span style="color: rgb(0,128,0)">@appendfile</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">NOINIT</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">INIT</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">end</span> <span style="color: rgb(0,0,255)">print</span> <span style="color: rgb(0,128,0)">@sql</span> <span style="color: rgb(0,0,255)">exec</span>(<span style="color: rgb(0,128,0)">@sql</span>) <span style="color: rgb(0,0,255)">go</span> <span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">3.--恢复数据库</span><span style="color: rgb(0,128,128)">*/</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">--调用示例 --完整恢复数据库 exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db'--差异备份恢复 exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db',@retype='DBNOR' exec p_backupdb @bkfile='c:\db_20031015_df.bak',@dbname='db',@retype='DF'--日志备份恢复 exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db',@retype='DBNOR' exec p_backupdb @bkfile='c:\db_20031015_log.bak',@dbname='db',@retype='LOG'--</span><span style="color: rgb(0,128,128)">*/</span><span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(128,128,128)">exists</span> (<span style="color: rgb(0,0,255)">select</span> <span style="color: rgb(128,128,128)">*</span> <span style="color: rgb(0,0,255)">from</span> dbo.sysobjects <span style="color: rgb(0,0,255)">where</span> id <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,255)">object_id</span>(N<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">[dbo].[p_RestoreDb]</span><span style="color: rgb(255,0,0)">'</span>) <span style="color: rgb(128,128,128)">and</span> <span style="color: rgb(255,0,255)">OBJECTPROPERTY</span>(id, N<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">IsProcedure</span><span style="color: rgb(255,0,0)">'</span>) <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(128,0,0); font-weight: bold">1</span>) <span style="color: rgb(0,0,255)">drop</span> <span style="color: rgb(0,0,255)">procedure</span> <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">dbo</span><span style="color: rgb(255,0,0)">]</span>.<span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">p_RestoreDb</span><span style="color: rgb(255,0,0)">]</span> <span style="color: rgb(0,0,255)">GO</span><span style="color: rgb(0,0,255)">create</span> <span style="color: rgb(0,0,255)">proc</span> p_RestoreDb <span style="color: rgb(0,128,0)">@bkfile</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">1000</span>), <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">定义要恢复的备份文件名 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@dbname</span> sysname<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">定义恢复后的数据库名,默认为备份的文件名 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@dbpath</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">260</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">恢复后的数据库存放目录,不指定则为SQL的默认数据目录 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@retype</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">10</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">DB</span><span style="color: rgb(255,0,0)">'</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">恢复类型:'DB'完事恢复数据库,'DBNOR' 为差异恢复,日志恢复进行完整恢复,'DF' 差异备份的恢复,'LOG' 日志恢复 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@filenumber</span> <span style="color: rgb(0,0,255)">int</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">1</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">恢复的文件号 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@overexist</span> <span style="color: rgb(0,0,255)">bit</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">1</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">是否覆盖已经存在的数据库,仅@retype为 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@killuser</span> <span style="color: rgb(0,0,255)">bit</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">是否关闭用户使用进程,仅@overexist=1时有效 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">as</span> <span style="color: rgb(0,0,255)">declare</span> <span style="color: rgb(0,128,0)">@sql</span> <span style="color: rgb(0,0,255)">varchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">8000</span>)<span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">得到恢复后的数据库名 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(255,0,255)">isnull</span>(<span style="color: rgb(0,128,0)">@dbname</span>,<span style="color: rgb(255,0,0)">''</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span> <span style="color: rgb(0,0,255)">select</span> <span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">reverse</span>(<span style="color: rgb(0,128,0)">@bkfile</span>) ,<span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">case</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(255,0,255)">charindex</span>(<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">.</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@sql</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">0</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(0,128,0)">@sql</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(255,0,255)">substring</span>(<span style="color: rgb(0,128,0)">@sql</span>,<span style="color: rgb(255,0,255)">charindex</span>(<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">.</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@sql</span>)<span style="color: rgb(128,128,128)">+</span><span style="color: rgb(128,0,0); font-weight: bold">1</span>,<span style="color: rgb(128,0,0); font-weight: bold">1000</span>) <span style="color: rgb(0,0,255)">end</span> ,<span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">case</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(255,0,255)">charindex</span>(<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">\</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@sql</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">0</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(0,128,0)">@sql</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(128,128,128)">left</span>(<span style="color: rgb(0,128,0)">@sql</span>,<span style="color: rgb(255,0,255)">charindex</span>(<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">\</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@sql</span>)<span style="color: rgb(128,128,128)">-</span><span style="color: rgb(128,0,0); font-weight: bold">1</span>) <span style="color: rgb(0,0,255)">end</span> ,<span style="color: rgb(0,128,0)">@dbname</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">reverse</span>(<span style="color: rgb(0,128,0)">@sql</span>)<span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">得到恢复后的数据库存放目录 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(255,0,255)">isnull</span>(<span style="color: rgb(0,128,0)">@dbpath</span>,<span style="color: rgb(255,0,0)">''</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span> <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@dbpath</span><span style="color: rgb(128,128,128)">=</span>dbo.f_getdbpath(<span style="color: rgb(255,0,0)">''</span>)<span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">生成数据库恢复语句 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">restore </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">case</span> <span style="color: rgb(0,128,0)">@retype</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">LOG</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">log </span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">database </span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">end</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@dbname</span> <span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)"> from disk=</span><span style="color: rgb(255,0,0)">'''</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@bkfile</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">''''</span> <span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)"> with file=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">cast</span>(<span style="color: rgb(0,128,0)">@filenumber</span> <span style="color: rgb(0,0,255)">as</span> <span style="color: rgb(0,0,255)">varchar</span>) <span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">case</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(0,128,0)">@overexist</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(128,128,128)">and</span> <span style="color: rgb(0,128,0)">@retype</span> <span style="color: rgb(128,128,128)">in</span>(<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">DB</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">DBNOR</span><span style="color: rgb(255,0,0)">'</span>) <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">,replace</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(255,0,0)">''</span> <span style="color: rgb(0,0,255)">end</span> <span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">case</span> <span style="color: rgb(0,128,0)">@retype</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">DBNOR</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">,NORECOVERY</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">,RECOVERY</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">end</span> <span style="color: rgb(0,0,255)">print</span> <span style="color: rgb(0,128,0)">@sql</span> <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">添加移动逻辑文件的处理 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(0,128,0)">@retype</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">DB</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(128,128,128)">or</span> <span style="color: rgb(0,128,0)">@retype</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">DBNOR</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">begin</span> <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">从备份文件中获取逻辑文件名 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">declare</span> <span style="color: rgb(0,128,0)">@lfn</span> <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">128</span>),<span style="color: rgb(0,128,0)">@tp</span> <span style="color: rgb(0,0,255)">char</span>(<span style="color: rgb(128,0,0); font-weight: bold">1</span>),<span style="color: rgb(0,128,0)">@i</span> <span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">创建临时表,保存获取的信息 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">create</span> <span style="color: rgb(0,0,255)">table</span> #tb(ln <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">128</span>),pn <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">260</span>),tp <span style="color: rgb(0,0,255)">char</span>(<span style="color: rgb(128,0,0); font-weight: bold">1</span>),fgn <span style="color: rgb(0,0,255)">nvarchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">128</span>),sz numeric(<span style="color: rgb(128,0,0); font-weight: bold">20</span>,<span style="color: rgb(128,0,0); font-weight: bold">0</span>),Msz numeric(<span style="color: rgb(128,0,0); font-weight: bold">20</span>,<span style="color: rgb(128,0,0); font-weight: bold">0</span>)) <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">从备份文件中获取信息 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">insert</span> <span style="color: rgb(0,0,255)">into</span> #tb <span style="color: rgb(0,0,255)">exec</span>(<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">restore filelistonly from disk=</span><span style="color: rgb(255,0,0)">'''</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@bkfile</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">''''</span>) <span style="color: rgb(0,0,255)">declare</span> #f <span style="color: rgb(0,0,255)">cursor</span> <span style="color: rgb(0,0,255)">for</span> <span style="color: rgb(0,0,255)">select</span> ln,tp <span style="color: rgb(0,0,255)">from</span> #tb <span style="color: rgb(0,0,255)">open</span> #f <span style="color: rgb(0,0,255)">fetch</span> <span style="color: rgb(0,0,255)">next</span> <span style="color: rgb(0,0,255)">from</span> #f <span style="color: rgb(0,0,255)">into</span> <span style="color: rgb(0,128,0)">@lfn</span>,<span style="color: rgb(0,128,0)">@tp</span> <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@i</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">0</span> <span style="color: rgb(0,0,255)">while</span> <span style="color: rgb(0,128,0); font-weight: bold">@@fetch_status</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">0</span> <span style="color: rgb(0,0,255)">begin</span> <span style="color: rgb(0,0,255)">select</span> <span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">,move </span><span style="color: rgb(255,0,0)">'''</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@lfn</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,0)">'''</span><span style="color: rgb(255,0,0)"> to </span><span style="color: rgb(255,0,0)">'''</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@dbpath</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@dbname</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">cast</span>(<span style="color: rgb(0,128,0)">@i</span> <span style="color: rgb(0,0,255)">as</span> <span style="color: rgb(0,0,255)">varchar</span>) <span style="color: rgb(128,128,128)">+</span><span style="color: rgb(255,0,255)">case</span> <span style="color: rgb(0,128,0)">@tp</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">D</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">.mdf</span><span style="color: rgb(255,0,0)">'''</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">.ldf</span><span style="color: rgb(255,0,0)">'''</span> <span style="color: rgb(0,0,255)">end</span> ,<span style="color: rgb(0,128,0)">@i</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,128,0)">@i</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(0,0,255)">fetch</span> <span style="color: rgb(0,0,255)">next</span> <span style="color: rgb(0,0,255)">from</span> #f <span style="color: rgb(0,0,255)">into</span> <span style="color: rgb(0,128,0)">@lfn</span>,<span style="color: rgb(0,128,0)">@tp</span> <span style="color: rgb(0,0,255)">end</span> <span style="color: rgb(0,0,255)">close</span> #f <span style="color: rgb(0,0,255)">deallocate</span> #f <span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">关闭用户进程处理 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(0,128,0)">@overexist</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(128,128,128)">and</span> <span style="color: rgb(0,128,0)">@killuser</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(0,0,255)">begin</span> <span style="color: rgb(0,0,255)">declare</span> <span style="color: rgb(0,128,0)">@spid</span> <span style="color: rgb(0,0,255)">varchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">20</span>) <span style="color: rgb(0,0,255)">declare</span> #spid <span style="color: rgb(0,0,255)">cursor</span> <span style="color: rgb(0,0,255)">for</span> <span style="color: rgb(0,0,255)">select</span> spid<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">cast</span>(spid <span style="color: rgb(0,0,255)">as</span> <span style="color: rgb(0,0,255)">varchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">20</span>)) <span style="color: rgb(0,0,255)">from</span> master..sysprocesses <span style="color: rgb(0,0,255)">where</span> dbid<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">db_id</span>(<span style="color: rgb(0,128,0)">@dbname</span>) <span style="color: rgb(0,0,255)">open</span> #spid <span style="color: rgb(0,0,255)">fetch</span> <span style="color: rgb(0,0,255)">next</span> <span style="color: rgb(0,0,255)">from</span> #spid <span style="color: rgb(0,0,255)">into</span> <span style="color: rgb(0,128,0)">@spid</span> <span style="color: rgb(0,0,255)">while</span> <span style="color: rgb(0,128,0); font-weight: bold">@@fetch_status</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">0</span> <span style="color: rgb(0,0,255)">begin</span> <span style="color: rgb(0,0,255)">exec</span>(<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">kill </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,128,0)">@spid</span>) <span style="color: rgb(0,0,255)">fetch</span> <span style="color: rgb(0,0,255)">next</span> <span style="color: rgb(0,0,255)">from</span> #spid <span style="color: rgb(0,0,255)">into</span> <span style="color: rgb(0,128,0)">@spid</span> <span style="color: rgb(0,0,255)">end</span> <span style="color: rgb(0,0,255)">close</span> #spid <span style="color: rgb(0,0,255)">deallocate</span> #spid <span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">恢复数据库 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">exec</span>(<span style="color: rgb(0,128,0)">@sql</span>)<span style="color: rgb(0,0,255)">go</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">4.--创建作业</span><span style="color: rgb(0,128,128)">*/</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">--调用示例--每月执行的作业 exec p_createjob @jobname='mm',@sql='select * from syscolumns',@freqtype='month'--每周执行的作业 exec p_createjob @jobname='ww',@sql='select * from syscolumns',@freqtype='week'--每日执行的作业 exec p_createjob @jobname='a',@sql='select * from syscolumns'--每日执行的作业,每天隔4小时重复的作业 exec p_createjob @jobname='b',@sql='select * from syscolumns',@fsinterval=4--</span><span style="color: rgb(0,128,128)">*/</span> <span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(128,128,128)">exists</span> (<span style="color: rgb(0,0,255)">select</span> <span style="color: rgb(128,128,128)">*</span> <span style="color: rgb(0,0,255)">from</span> dbo.sysobjects <span style="color: rgb(0,0,255)">where</span> id <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,255)">object_id</span>(N<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">[dbo].[p_createjob]</span><span style="color: rgb(255,0,0)">'</span>) <span style="color: rgb(128,128,128)">and</span> <span style="color: rgb(255,0,255)">OBJECTPROPERTY</span>(id, N<span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">IsProcedure</span><span style="color: rgb(255,0,0)">'</span>) <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(128,0,0); font-weight: bold">1</span>) <span style="color: rgb(0,0,255)">drop</span> <span style="color: rgb(0,0,255)">procedure</span> <span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">dbo</span><span style="color: rgb(255,0,0)">]</span>.<span style="color: rgb(255,0,0)">[</span><span style="color: rgb(255,0,0)">p_createjob</span><span style="color: rgb(255,0,0)">]</span> <span style="color: rgb(0,0,255)">GO</span><span style="color: rgb(0,0,255)">create</span> <span style="color: rgb(0,0,255)">proc</span> p_createjob <span style="color: rgb(0,128,0)">@jobname</span> <span style="color: rgb(0,0,255)">varchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">100</span>), <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">作业名称 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@sql</span> <span style="color: rgb(0,0,255)">varchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">8000</span>), <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">要执行的命令 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@dbname</span> sysname<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">默认为当前的数据库名 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@freqtype</span> <span style="color: rgb(0,0,255)">varchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">6</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">day</span><span style="color: rgb(255,0,0)">'</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">时间周期,month 月,week 周,day 日 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@fsinterval</span> <span style="color: rgb(0,0,255)">int</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">1</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">相对于每日的重复次数 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@time</span> <span style="color: rgb(0,0,255)">int</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">170000</span> <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">开始执行时间,对于重复执行的作业,将从0点到23:59分 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">as</span> <span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(255,0,255)">isnull</span>(<span style="color: rgb(0,128,0)">@dbname</span>,<span style="color: rgb(255,0,0)">''</span>)<span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">''</span> <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@dbname</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">db_name</span>()<span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">创建作业 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">exec</span> msdb..sp_add_job <span style="color: rgb(0,128,0)">@job_name</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,128,0)">@jobname</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">创建作业步骤 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">exec</span> msdb..sp_add_jobstep <span style="color: rgb(0,128,0)">@job_name</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,128,0)">@jobname</span>, <span style="color: rgb(0,128,0)">@step_name</span> <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">数据处理</span><span style="color: rgb(255,0,0)">'</span>, <span style="color: rgb(0,128,0)">@subsystem</span> <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">TSQL</span><span style="color: rgb(255,0,0)">'</span>, <span style="color: rgb(0,128,0)">@database_name</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,128,0)">@dbname</span>, <span style="color: rgb(0,128,0)">@command</span> <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(0,128,0)">@sql</span>, <span style="color: rgb(0,128,0)">@retry_attempts</span> <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(128,0,0); font-weight: bold">5</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">重试次数 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@retry_interval</span> <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(128,0,0); font-weight: bold">5</span> <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">重试间隔</span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">创建调度 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">declare</span> <span style="color: rgb(0,128,0)">@ftype</span> <span style="color: rgb(0,0,255)">int</span>,<span style="color: rgb(0,128,0)">@fstype</span> <span style="color: rgb(0,0,255)">int</span>,<span style="color: rgb(0,128,0)">@ffactor</span> <span style="color: rgb(0,0,255)">int</span> <span style="color: rgb(0,0,255)">select</span> <span style="color: rgb(0,128,0)">@ftype</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">case</span> <span style="color: rgb(0,128,0)">@freqtype</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">day</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(128,0,0); font-weight: bold">4</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">week</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(128,0,0); font-weight: bold">8</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">month</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(128,0,0); font-weight: bold">16</span> <span style="color: rgb(0,0,255)">end</span> ,<span style="color: rgb(0,128,0)">@fstype</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">case</span> <span style="color: rgb(0,128,0)">@fsinterval</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(128,0,0); font-weight: bold">0</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(128,0,0); font-weight: bold">8</span> <span style="color: rgb(0,0,255)">end</span> <span style="color: rgb(0,0,255)">if</span> <span style="color: rgb(0,128,0)">@fsinterval</span><span style="color: rgb(128,128,128)"><></span><span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@time</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">0</span> <span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@ffactor</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,255)">case</span> <span style="color: rgb(0,128,0)">@freqtype</span> <span style="color: rgb(0,0,255)">when</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">day</span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">then</span> <span style="color: rgb(128,0,0); font-weight: bold">0</span> <span style="color: rgb(0,0,255)">else</span> <span style="color: rgb(128,0,0); font-weight: bold">1</span> <span style="color: rgb(0,0,255)">end</span><span style="color: rgb(0,0,255)">EXEC</span> msdb..sp_add_jobschedule <span style="color: rgb(0,128,0)">@job_name</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,128,0)">@jobname</span>, <span style="color: rgb(0,128,0)">@name</span> <span style="color: rgb(128,128,128)">=</span> <span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">时间安排</span><span style="color: rgb(255,0,0)">'</span>, <span style="color: rgb(0,128,0)">@freq_type</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,128,0)">@ftype</span> , <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">每天,8 每周,16 每月 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@freq_interval</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">1</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">重复执行次数 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@freq_subday_type</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,128,0)">@fstype</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">是否重复执行 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@freq_subday_interval</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,128,0)">@fsinterval</span>, <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">重复周期 </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,128,0)">@freq_recurrence_factor</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,128,0)">@ffactor</span>, <span style="color: rgb(0,128,0)">@active_start_time</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,128,0)">@time</span> <span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">下午17:00:00分执行</span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">go</span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">--应用案例--备份方案: 完整备份（每个星期天一次）+差异备份（每天备份一次）+日志备份（每2小时备份一次）调用上面的存储过程来实现 --</span><span style="color: rgb(0,128,128)">*/</span><span style="text-decoration: line-through"><span style="color: rgb(0,0,255); text-decoration: line-through">declare</span> <span style="color: rgb(0,128,0); text-decoration: line-through">@sql</span> <span style="color: rgb(0,0,255); text-decoration: line-through">varchar</span>(<span style="color: rgb(128,0,0); font-weight: bold; text-decoration: line-through">8000</span>) </span><span style="text-decoration: line-through"><span style="color: rgb(0,128,128); text-decoration: line-through">--</span><span style="color: rgb(0,128,128); text-decoration: line-through">完整备份（每个星期天一次） </span><span style="color: rgb(0,128,128); text-decoration: line-through"></span><span style="color: rgb(0,0,255); text-decoration: line-through">set</span> <span style="color: rgb(0,128,0); text-decoration: line-through">@sql</span><span style="color: rgb(128,128,128); text-decoration: line-through">=</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span><span style="color: rgb(255,0,0); text-decoration: line-through">exec p_backupdb @dbname=</span><span style="color: rgb(255,0,0); text-decoration: line-through">''</span><span style="color: rgb(255,0,0); text-decoration: line-through">要备份的数据库名</span><span style="color: rgb(255,0,0); text-decoration: line-through">'''</span> </span><span style="text-decoration: line-through"><span style="color: rgb(0,0,255); text-decoration: line-through">exec</span> p_createjob <span style="color: rgb(0,128,0); text-decoration: line-through">@jobname</span><span style="color: rgb(128,128,128); text-decoration: line-through">=</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span><span style="color: rgb(255,0,0); text-decoration: line-through">每周备份</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span>,<span style="color: rgb(0,128,0); text-decoration: line-through">@sql</span>,<span style="color: rgb(0,128,0); text-decoration: line-through">@freqtype</span><span style="color: rgb(128,128,128); text-decoration: line-through">=</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span><span style="color: rgb(255,0,0); text-decoration: line-through">week</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span></span><span style="text-decoration: line-through"><span style="color: rgb(0,128,128); text-decoration: line-through">--</span><span style="color: rgb(0,128,128); text-decoration: line-through">差异备份（每天备份一次） </span><span style="color: rgb(0,128,128); text-decoration: line-through"></span><span style="color: rgb(0,0,255); text-decoration: line-through">set</span> <span style="color: rgb(0,128,0); text-decoration: line-through">@sql</span><span style="color: rgb(128,128,128); text-decoration: line-through">=</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span><span style="color: rgb(255,0,0); text-decoration: line-through">exec p_backupdb @dbname=</span><span style="color: rgb(255,0,0); text-decoration: line-through">''</span><span style="color: rgb(255,0,0); text-decoration: line-through">要备份的数据库名</span><span style="color: rgb(255,0,0); text-decoration: line-through">''</span><span style="color: rgb(255,0,0); text-decoration: line-through">,@bktype=</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span>DF<span style="color: rgb(255,0,0); text-decoration: line-through">''</span> </span><span style="text-decoration: line-through"><span style="color: rgb(0,0,255); text-decoration: line-through">exec</span> p_createjob <span style="color: rgb(0,128,0); text-decoration: line-through">@jobname</span><span style="color: rgb(128,128,128); text-decoration: line-through">=</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span><span style="color: rgb(255,0,0); text-decoration: line-through">每天差异备份</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span>,<span style="color: rgb(0,128,0); text-decoration: line-through">@sql</span>,<span style="color: rgb(0,128,0); text-decoration: line-through">@freqtype</span><span style="color: rgb(128,128,128); text-decoration: line-through">=</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span><span style="color: rgb(255,0,0); text-decoration: line-through">day</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span></span><span style="text-decoration: line-through"><span style="color: rgb(0,128,128); text-decoration: line-through">--</span><span style="color: rgb(0,128,128); text-decoration: line-through">日志备份（每2小时备份一次） </span><span style="color: rgb(0,128,128); text-decoration: line-through"></span><span style="color: rgb(0,0,255); text-decoration: line-through">set</span> <span style="color: rgb(0,128,0); text-decoration: line-through">@sql</span><span style="color: rgb(128,128,128); text-decoration: line-through">=</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span><span style="color: rgb(255,0,0); text-decoration: line-through">exec p_backupdb @dbname=</span><span style="color: rgb(255,0,0); text-decoration: line-through">''</span><span style="color: rgb(255,0,0); text-decoration: line-through">要备份的数据库名</span><span style="color: rgb(255,0,0); text-decoration: line-through">''</span><span style="color: rgb(255,0,0); text-decoration: line-through">,@bktype=</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span><span style="color: rgb(255,0,255); text-decoration: line-through">LOG</span><span style="color: rgb(255,0,0); text-decoration: line-through">''</span> </span><span style="text-decoration: line-through"><span style="color: rgb(0,0,255); text-decoration: line-through">exec</span> p_createjob <span style="color: rgb(0,128,0); text-decoration: line-through">@jobname</span><span style="color: rgb(128,128,128); text-decoration: line-through">=</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span><span style="color: rgb(255,0,0); text-decoration: line-through">每2小时日志备份</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span>,<span style="color: rgb(0,128,0); text-decoration: line-through">@sql</span>,<span style="color: rgb(0,128,0); text-decoration: line-through">@freqtype</span><span style="color: rgb(128,128,128); text-decoration: line-through">=</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span><span style="color: rgb(255,0,0); text-decoration: line-through">day</span><span style="color: rgb(255,0,0); text-decoration: line-through">'</span>,<span style="color: rgb(0,128,0); text-decoration: line-through">@fsinterval</span><span style="color: rgb(128,128,128); text-decoration: line-through">=</span></span><span style="text-decoration: line-through"><span style="color: rgb(128,0,0); font-weight: bold; text-decoration: line-through">2</span></span></pre>
<pre></pre>
<div class="cnblogs_code">
<pre><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">上面发现了错误，修改下贴出</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">完整备份（每个星期天一次） </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">exec</span> p_createjob <span style="color: rgb(0,128,0)">@jobname</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">每周备份</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">exec p_backupdb @dbname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">要备份的数据库名</span><span style="color: rgb(255,0,0)">'''</span>,<span style="color: rgb(0,128,0)">@freqtype</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">week</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">差异备份（每天备份一次） </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">exec</span> p_createjob <span style="color: rgb(0,128,0)">@jobname</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">每天差异备份</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">exec p_backupdb @dbname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">要备份的数据库名</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">,@bktype=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">DF</span><span style="color: rgb(255,0,0)">'''</span>,<span style="color: rgb(0,128,0)">@freqtype</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">day</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">日志备份（每2小时备份一次） </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">exec</span> p_createjob <span style="color: rgb(0,128,0)">@jobname</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">每2小时日志备份</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">exec p_backupdb @dbname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">要备份的数据库名</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">,@bktype=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">LOG</span><span style="color: rgb(255,0,0)">'''</span>,<span style="color: rgb(0,128,0)">@freqtype</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">day</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@fsinterval</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">2</span></pre>
</div>
<pre><span style="color: rgb(128,0,0)"><strong><span style="text-decoration: line-through"></span></strong></span><span style="color: rgb(0,128,128)">/*</span><span style="color: rgb(0,128,128)">--应用案例2生产数据核心库：PRODUCE备份方案如下： 1.设置三个作业,分别对PRODUCE库进行每日备份,每周备份,每月备份 2.新建三个新库,分别命名为:每日备份,每周备份,每月备份 3.建立三个作业，分别把三个备份库还原到以上的三个新库。目的:当用户在produce库中有任何的数据丢失时,均可以从上面的三个备份库中导入相应的TABLE数据。 --</span><span style="color: rgb(0,128,128)">*/</span><span style="color: rgb(0,0,255)">declare</span> <span style="color: rgb(0,128,0)">@sql</span> <span style="color: rgb(0,0,255)">varchar</span>(<span style="color: rgb(128,0,0); font-weight: bold">8000</span>)<span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">1.建立每月备份和生成月备份数据库的作业,每月每1天下午16:40分进行: </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)"> declare @path nvarchar(260),@fname nvarchar(100) set @fname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">PRODUCE_</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">+convert(varchar(10),getdate(),112)+</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">_m.bak</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)"> set @path=dbo.f_getdbpath(null)+@fname--备份 exec p_backupdb @dbname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">PRODUCE</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">,@bkfname=@fname--根据备份生成每月新库 exec p_RestoreDb @bkfile=@path,@dbname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">PRODUCE_月</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">--为周数据库恢复准备基础数据库 exec p_RestoreDb @bkfile=@path,@dbname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">PRODUCE_周</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">,@retype=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">DBNOR</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">--为日数据库恢复准备基础数据库 exec p_RestoreDb @bkfile=@path,@dbname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">PRODUCE_日</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">,@retype=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">DBNOR</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)"> </span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">exec</span> p_createjob <span style="color: rgb(0,128,0)">@jobname</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">每月备份</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@sql</span>,<span style="color: rgb(0,128,0)">@freqtype</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">month</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@time</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">164000</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">2.建立每周差异备份和生成周备份数据库的作业,每周日下午17:00分进行: </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)"> declare @path nvarchar(260),@fname nvarchar(100) set @fname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">PRODUCE_</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">+convert(varchar(10),getdate(),112)+</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">_w.bak</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)"> set @path=dbo.f_getdbpath(null)+@fname--差异备份 exec p_backupdb @dbname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">PRODUCE</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">,@bkfname=@fname,@bktype=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">DF</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">--差异恢复周数据库 exec p_backupdb @bkfile=@path,@dbname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">PRODUCE_周</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">,@retype=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">DF</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)"> </span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">exec</span> p_createjob <span style="color: rgb(0,128,0)">@jobname</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">每周差异备份</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@sql</span>,<span style="color: rgb(0,128,0)">@freqtype</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">week</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@time</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">170000</span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">3.建立每日日志备份和生成日备份数据库的作业,每周日下午17:15分进行: </span><span style="color: rgb(0,128,128)"></span><span style="color: rgb(0,0,255)">set</span> <span style="color: rgb(0,128,0)">@sql</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)"> declare @path nvarchar(260),@fname nvarchar(100) set @fname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">PRODUCE_</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">+convert(varchar(10),getdate(),112)+</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">_l.bak</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)"> set @path=dbo.f_getdbpath(null)+@fname--日志备份 exec p_backupdb @dbname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">PRODUCE</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">,@bkfname=@fname,@bktype=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">LOG</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">--日志恢复日数据库 exec p_backupdb @bkfile=@path,@dbname=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">PRODUCE_日</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">,@retype=</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)">LOG</span><span style="color: rgb(255,0,0)">''</span><span style="color: rgb(255,0,0)"> </span><span style="color: rgb(255,0,0)">'</span> <span style="color: rgb(0,0,255)">exec</span> p_createjob <span style="color: rgb(0,128,0)">@jobname</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">每周差异备份</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@sql</span>,<span style="color: rgb(0,128,0)">@freqtype</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">day</span><span style="color: rgb(255,0,0)">'</span>,<span style="color: rgb(0,128,0)">@time</span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(128,0,0); font-weight: bold">171500</span></pre>
</div>
试试其它关键字
　T-SQL　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)