当前位置：Gxlcms > 数据库问题 > mssql sqlserver 自动备份存储过程的方法分享

mssql sqlserver 自动备份存储过程的方法分享

时间：2021-07-01 10:21:17 帮助过：21人阅读


摘要:
为了更好的记录数据库中存储过程脚本的变化情况，下文采用数据库触发器来自动记载每次“存储过程”的变化(新增或修改)，如下所示:
实验环境:sql server 2008 R2
<hr />
处理方法:
1 .master数据库下建立数据表:
procBackTableInfo,包含列
 

  1. <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">procBackTableInfo</span><span style="color: #ff0000;">]</span><span style="color: #000000;">(
  2. </span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">keyId [int</span><span style="color: #ff0000;">]</span> <span style="color: #ff00ff;">IDENTITY</span>(<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span> <span style="color: #0000ff;">PRIMARY</span> <span style="color: #0000ff;">KEY</span> ,<span style="color: #008080;">--</span><span style="color: #008080;">编号</span>
  3. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbName</span><span style="color: #ff0000;">]</span> sysname,<span style="color: #008080;">--</span><span style="color: #008080;">数据库名</span>
  4. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">procSqlInfo</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">ntext</span><span style="color: #ff0000;">]</span> <span style="color: #808080;">NOT</span> <span style="color: #0000ff;">NULL</span>,<span style="color: #008080;">--</span><span style="color: #008080;">存储过程的SQL</span>
  5. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">ProcName</span><span style="color: #ff0000;">]</span> sysname,<span style="color: #008080;">--</span><span style="color: #008080;">存储过程名字</span>
  6. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">writeDate</span><span style="color: #ff0000;">]</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">datetime</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">default</span> <span style="color: #ff00ff;">getdate</span>(),<span style="color: #008080;">--</span><span style="color: #008080;">修改时间</span>
  7. <span style="color: #ff0000;">[</span><span style="color: #ff0000;">writeUser</span><span style="color: #ff0000;">]</span> sysname <span style="color: #0000ff;">null</span> <span style="color: #008080;">--</span><span style="color: #008080;">写入人</span>
  8. )

 


2. 建立数据库触发器，tr_procChange
 

  1. <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">trigger</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">tr_procChange</span><span style="color: #ff0000;">]</span>
  2. <span style="color: #0000ff;">on</span> <span style="color: #808080;">all</span><span style="color: #000000;"> server 
  3. </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> CREATE_PROCEDURE,ALTER_PROCEDURE
  4. </span><span style="color: #0000ff;">as</span> 
  5. <span style="color: #008080;">--</span><span style="color: #008080;">获取事件数据</span>
  6. <span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@data</span><span style="color: #000000;"> XML
  7. </span><span style="color: #0000ff;">SET</span> <span style="color: #008000;">@data</span> <span style="color: #808080;">=</span><span style="color: #000000;"> EVENTDATA()
  8. </span><span style="color: #0000ff;">declare</span> <span style="color: #008000;">@dbName</span><span style="color: #000000;"> sysname
  9. </span><span style="color: #0000ff;">declare</span> <span style="color: #008000;">@procName</span><span style="color: #000000;"> sysname
  10. </span><span style="color: #0000ff;">declare</span> <span style="color: #008000;">@procSqlInfo</span> <span style="color: #0000ff;">nvarchar</span>(<span style="color: #ff00ff;">max</span><span style="color: #000000;">)
  11. </span><span style="color: #008080;">--</span><span style="color: #008080;">获取新建存储过程的数据库名</span>
  12. <span style="color: #0000ff;">SET</span> <span style="color: #008000;">@dbName</span> <span style="color: #808080;">=</span> <span style="color: #008000;">@data</span>.value(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">(/EVENT_INSTANCE/DatabaseName)[1]</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">sysname</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  13. </span><span style="color: #008080;">--</span><span style="color: #008080;">存储过程的名字</span>
  14. <span style="color: #0000ff;">set</span> <span style="color: #008000;">@procName</span> <span style="color: #808080;">=</span> <span style="color: #008000;">@data</span>.value(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">(/EVENT_INSTANCE/ObjectName)[1]</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">sysname</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  15. </span><span style="color: #008080;">--</span><span style="color: #008080;">获取新建存储过程的内容</span>
  16. <span style="color: #0000ff;">set</span> <span style="color: #008000;">@procSqlInfo</span> <span style="color: #808080;">=</span> <span style="color: #008000;">@data</span>.value(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">sysname</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">)
  17. </span><span style="color: #008080;">--</span><span style="color: #008080;">将数据库名、存储过程名以及存储过程内容插入ProcSqlTable表</span>
  18. <span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">master</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">procBackTableInfo</span><span style="color: #ff0000;">]</span>(<span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbName</span><span style="color: #ff0000;">]</span>,<span style="color: #ff0000;">[</span><span style="color: #ff0000;">ProcName</span><span style="color: #ff0000;">]</span>,<span style="color: #ff0000;">[</span><span style="color: #ff0000;">ProcSQL</span><span style="color: #ff0000;">]</span><span style="color: #000000;">) 
  19. </span><span style="color: #0000ff;">values</span>(<span style="color: #008000;">@dbName</span>,<span style="color: #008000;">@procName</span>,<span style="color: #008000;">@procSqlInfo</span><span style="color: #000000;">)
  20. </span><span style="color: #0000ff;">GO</span><span style="color: #000000;">
  21. ENABLE </span><span style="color: #0000ff;">TRIGGER</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">tr_procChange</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">ON</span> <span style="color: #808080;">ALL</span> SERVER

 

mssql sqlserver 自动备份存储过程的方法分享

标签：text   实验   数据   mssql   database   处理   values   col   for   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！