為了避免意外遺失/損壞辛苦建立的Stored Procedures,或是想要還原到先前版本的Stored Procedures,這樣提供了一個有效方法,可以自動將指定資料庫中的Stored Procedures進行備份。
1. 在特定的資料庫(建議為SQL Server的master資料庫)上建立資料表StoredProceduresBackup,用來保存備份的Stored 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), ProcedurebaseName VARCHAR(50), ProcedureName
VARCHAR(50),ProcedurebaseName VARCHAR(4000
)
2. 建立Stored Procedure名為usp_ProceduresVersion,該Stored Procedure用來將需要備份Stored Procedures的備份到上述所建立的資料表中。
其中主要存取sysobjects和syscomments系統表:
(1) sysobjects system table
Contains 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 table
Contains 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 4finition statements, which are limited to a maximum size 45. .
(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/2004 Rickie
*/
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. 建立Job執行上述Stored Procedure
在SQL Server上建立Job,並設定運行計劃,這樣指定資料庫的Stored Procedures就可以自動備份到上述資料表中。
OK. That's all. Any questions about it, please contact me at [email protected] . Have a good luck.