自动备份SQL Server数据库中用户创建的Stored Procedures

为了避免意外丢失 / 损坏辛苦创建的 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),

   ProcedureText VARCHAR(4000)

)

GO

 

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 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/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. 创建 J ob 执行上述 Stored Procedure

在 SQL Server 上创建 Job ，并设定运行计划，这样指定数据库的 Stored Procedures 就可以自动备份到上述数据表中。