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