Automatically back up user-created Stored Procedures in a SQL Server database

In order to avoid accidentally losing/damaging the Stored Procedures that you have worked so hard to create, or if you want to restore to a previous version of the Stored Procedures, this provides an effective method to automatically back up the Stored Procedures in the specified database.

1. Create the data table StoredProceduresBackup on a specific database (recommended to be the master database of SQL Server) to save the backup 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. Create a Stored Procedure named usp_ProceduresVersion. This Stored Procedure is used to back up the Stored Procedures that need to be backed up into the data table created above.

Among them, the sysobjects and syscomments system tables are mainly accessed:

(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. Create the Job and execute the above Stored Procedure

to create the Job on SQL Server and set the running plan so that the Stored Procedures of the specified database can be automatically backed up to the above data table.