存储过程代码自动备份

  首先在master库下建立表procsqltable和backprocsqltable。procsqltable存放存储过程当前版本代码，backprocsqltable存放历史版本代码。

use [master]
go
create table [dbo].[backprocsqltable](
    [id] [int] identity(1,1) not null primary key ,--编号
    [dbname] [nvarchar](150) not null,--数据库名
    [procsql] [ntext] not null,--存储过程的sql
    [procname] [nvarchar](150) not null,--存储过程名字
    [alterdate] [datetime] not null,--修改时间
    [alteruser] [nvarchar](150) null--修改人
)
go



use [master]
go
create table [dbo].[procsqltable](
    [id] [int] identity(1,1) not null primary key,--编号
    [dbname] [nvarchar](150) not null,--数据名
    [procsql] [ntext] not null,--存储过程sql
    [procname] [nvarchar](150) not null--存储过程名字
)
go

　 接下来需要建立两个库级(ddl)触发器：tr_saveprocsql和tr_savebackprocsql。tr_saveprocsql将新建存储过程代码写入procsqltable，tr_savebackprocsql将procsqltable表中保存的代码写到backprocsqltable中作为历史版本代码，同时将存储过程当前代码更新到procsqltable。

use [master]
go
create trigger [tr_saveprocsql]
on all server --作用于sql server实例下所有库
for create_procedure
as 
  --获取事件数据
  declare @data xml
  set @data = eventdata()

  declare @dbname nvarchar(50)
  declare @procname nvarchar(150)
  declare @procsql nvarchar(max)

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

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

go

enable trigger [tr_saveprocsql] on all server
go

use [master]
go
create trigger [tr_savebackprocsql]
on all server  --作用于sql server实例下所有库
for alter_procedure
as 
  --获取事件数据
  declare @data xml
  set @data = eventdata()

  declare @dbname nvarchar(50)
  declare @procname nvarchar(150)
  declare @loginname nvarchar(150)
  declare @procsql nvarchar(max) --存储过程内容
  declare @oldprocsql nvarchar(max)--修改前的存储过程内容

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

  if exists(select 1 from [master].[dbo].[procsqltable] where [dbname]=@dbname and [procname]=@procname)
    begin 
        --如果系统里有该存储过程的记录，获取修改前的存储过程内容
        select @oldprocsql=[procsql]
        from [master].[dbo].[procsqltable]
        where [dbname]=@dbname and [procname]=@procname
    end
  else
    begin
         --如果没有将数据库名、存储过程名以及存储过程内容插入procsqltable表
         insert into [master].[dbo].[procsqltable]([dbname],[procname],[procsql]) 
         values(@dbname,@procname,@procsql)

         --退出
         return
    end

  --更新procsqltable表存储过程的内容供下次使用
  update [master].[dbo].[procsqltable]
  set [procsql]=@procsql
  where [dbname]=@dbname and [procname]=@procname 

  --将数据库名、存储过程名以及修改前的存储过程内容插入backprocsqltable表
  insert into [master].[dbo].[backprocsqltable]([dbname],[procname],[procsql],[alterdate],[alteruser]) 
  values(@dbname,@procname,@oldprocsql,getdate(),@loginname)
  
go

enable trigger [tr_savebackprocsql] on all server
go

 

=====================================================================================
本文只代表本人的见解，可能存在错误，仅用于技术交流。如果你喜欢该文，可以扫下面的二维码打赏我（打赏敬请备注“博客园打赏”五字）。