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

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

create table [procbacktableinfo](
[keyid [int] identity(1,1) not null primary key ,--编号
[dbname] sysname,--数据库名
[procsqlinfo] [ntext] not null,--存储过程的sql
[procname] sysname,--存储过程名字
[writedate] [datetime] default getdate(),--修改时间
[writeuser] sysname null --写入人
)

 

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

create trigger [tr_procchange]
on all server 
for create_procedure,alter_procedure

as 
--获取事件数据
declare @data xml
set @data = eventdata()

declare @dbname sysname
declare @procname sysname
declare @procsqlinfo nvarchar(max)

--获取新建存储过程的数据库名
set @dbname = @data.value('(/event_instance/databasename)[1]', 'sysname')
--存储过程的名字
set @procname = @data.value('(/event_instance/objectname)[1]', 'sysname')
--获取新建存储过程的内容
set @procsqlinfo = @data.value('(/event_instance/tsqlcommand/commandtext)[1]', 'sysname')

--将数据库名、存储过程名以及存储过程内容插入procsqltable表
insert into [master].[dbo].[procbacktableinfo]([dbname],[procname],[procsql]) 
values(@dbname,@procname,@procsqlinfo)

go

enable trigger [tr_procchange] on all server