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


摘要:
为了更好的记录数据库中存储过程脚本的变化情况,下文采用数据库触发器来自动记载每次“存储过程”的变化(新增或修改),如下所示:
实验环境:sql server 2008 R2


处理方法:
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