為了避免意外丟失/損壞辛苦創(chuàng)建的Stored Procedures，或者想恢復(fù)到以前版本的Stored Procedures，這樣提供了一個(gè)有效方法，可以自動(dòng)將指定數(shù)據(jù)庫(kù)中的Stored Procedures進(jìn)行備份。

1. 在特定的數(shù)據(jù)庫(kù)（建議為SQL Server的master數(shù)據(jù)庫(kù)）上創(chuàng)建數(shù)據(jù)表StoredProceduresBackup，用來(lái)保存?zhèn)浞莸腟tored Procedures。

IF OBJECT_ID('StoredProceduresBackup') IS NOT NULL

DROP TABLE StoredProceduresBackup

GO

CREATE TABLE StoredProceduresBackup

(

AutoID INTEGER IDENTITY(1,1) PRIMARY KEY,

InsertDate DATETIME DEFAULT GETDATE(),

DatabaseName VARCHAR(50),

ProcedureName VARCHAR(50),

ProcedureText VARCHAR(4000)

)

GO

2. 創(chuàng)建Stored Procedure名為usp_ProceduresVersion，該Stored Procedure用來(lái)將需要備份Stored Procedures的備份到上述創(chuàng)建的數(shù)據(jù)表中。

其中主要訪問(wèn)sysobjects和syscomments系統(tǒng)表：

(1) sysobjects system tableContains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. In tempdb only, this table includes a row for each temporary object.

(2) syscomments system tableContains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The text column contains the original SQL definition statements, which are limited to a maximum size of 4 MB. This table is stored in each database.

(3) source script of stored procedure.

/*

Name:; usp_ProceduresVersion

Description:; Back up user defined stored-procedures

Author:; Rickie

Modification Log: NO

Description;;;;;Date;;Changed By

Created procedure;8/27/2004Rickie

*/

CREATE PROCEDURE usp_ProceduresVersion @DatabaseName NVARCHAR(50)

AS

SET NOCOUNT ON

--This will hold the dynamic string.

DECLARE @strSQL NVARCHAR(4000)

--Set the string

--Only stored procedures

SET @strSQL = 'INSERT INTO master.dbo.StoredProceduresBackup(

DatabaseName,ProcedureName,ProcedureText )

SELECT ''' + @DatabaseName + ''', so.name, sc.text

FROM ' + @DatabaseName + '.dbo.sysobjects so

INNER JOIN ' + @DatabaseName + '.dbo.syscomments sc

ON so.id = sc.id

WHERE so.type = ''p''' + ' and so.status>0

Order By so.id '

--Execute the string

EXEC dbo.sp_executesql @strSQL

GO

3. 創(chuàng)建Job執(zhí)行上述Stored Procedure

在SQL Server上創(chuàng)建Job，并設(shè)定運(yùn)行計(jì)劃，這樣指定數(shù)據(jù)庫(kù)的Stored Procedures就可以自動(dòng)備份到上述數(shù)據(jù)表中。

OK. That’s all. Any questions about it, please contact me at rickieleemail@yahoo.com. Have a good luck.