In order to avoid accidental loss
/
Damaged hard creation
Stored Procedures
Or want to recover to previous versions
Stored Procedures
This provides an effective method that automatically will automatically specify the database.
Stored Procedures
Back up.
1. Create a data table StoredProcedureduResBackup on a specific database (recommended for the Master database for SQL Server) to save the backup storedures.
IF Object_ID ('StoredProceduResBackup') Is Not Null
Drop Table StoredProceduResBackup
Go
Create Table StoredProcedureduResBackup
(
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 called USP_PROCEDURESVERSION, which is used to back up the Stored Procedures backup to the data table created above.
The main accesses the Sysobjects and SysComments system tables:
(1) sysobjects system table
CREATED WITHIN A DATABASE. IN TEMPDB ONLY, this 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 User Defined Stored-Procedures
Author: rickie
Modification log: No
Description Date Changed By
Created procedure 8/27/2004 rickie
* /
Create Procedure USP_PROCEDURESVERSION @Databasesvename nvarchar (50)
AS
Set nocount on
--This Will Hold The Dynamic String.
Declare @strsql nvarchar (4000)
--Set the string
--Nly stored procedures
Set @strsql = 'Insert Into Master.dbo.StoredProceduResBackup (DatabaseName, ProcedureName, Proceduretext)
SELECT '' ' @DatabaseNamename ' ', So.name, sc.text
From ' @DatabaseName ' .dbo.sysObjects so
Inner Join ' @DatabaseNamename ' .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 Job to perform the above Stored Procedure
Create Job on SQL Server and set a run plan so that the Stored Procedures that specifies the database can be automatically backed up to the above data tables.
Ok. That's all. Any Questions about it, please contact me at rickieleemail@yahoo.com. Have a Good Luck.
