Backup script for all stored procedures for a particular Database

Hi everyone,

I was not able to post anything in the last week as I did not got time.This post is regarding

a very useful script which will give you the backup of all the stored procedures,views and functions

at the specific path on your local system.Often we forget to take backup of these objects in our database

and this script can even become very handy if you put this in a job and schedule it accordingly.

Following is the script which will create an SP and when executed by passing the destination path

and DB name it will take backup of all these objects.By default the destination path is kept as

‘C:\Backup\StoredProcedure\’.

 

GO
IF OBJECT_ID(‘USP_BackupAllStoredProcedures’) IS NOT NULL
DROP PROC USP_BackupAllStoredProcedures
GO
 
/*==========================================================================================   
Name:  Export all stored procedures for all user databases to particular location   
Author:  Mohammad Shahed   
Parameters:  
@ExportDataPath specifies location to where backup of sp needs to store.  eg. ”C:\Backup\StoredProcedure\”  
Returns:     
Description: It creates main folder in @ExportDataPath which contains current
   date and time, in that folder it creates  folder for the database and  
creates stored procedure related to database.  
==========================================================================================*/   
   
CREATE PROCEDURE [dbo].[USP_BackupAllStoredProcedures]
    (
      @ExportDataPath NVARCHAR(1000) ,
      @DBName    varchar(max)
    )
AS
    BEGIN   
        SET QUOTED_IDENTIFIER OFF 
        SET NOCOUNT ON 
        BEGIN TRY 

            DECLARE @ExportPath AS NVARCHAR(1000) 
            SET @ExportPath = @ExportDataPath 
            IF ( ISNULL(@ExportPath, ”) = ”)
                BEGIN 
                    SET @ExportPath = ‘C:\Backup\StoredProcedure\’ 
                END 
            SET @ExportPath += ( SELECT CONVERT(VARCHAR(100), GETDATE(), 102)+ ‘_’+ REPLACE(CONVERT(VARCHAR(100), GETDATE(), 108),’:’, ‘.’)) + ‘\’ 
            — variables for first while loop 
            DECLARE @DatabaseName AS NVARCHAR(1000) 
            — variables for second while loop 
            DECLARE @ExportFilePath NVARCHAR(1000)       
            DECLARE @ServerName NVARCHAR(100)       
            SELECT  @ServerName = CONVERT(SYSNAME, SERVERPROPERTY(N’servername’))    
            DECLARE @GetProcedureNames NVARCHAR(MAX) 
      
          
            –IF OBJECT_ID(‘tempdb..#Databases’) IS NOT NULL
            —    DROP TABLE #Databases  
            –SELECT  name ,
            —        ROW_NUMBER() OVER ( ORDER BY name ) AS RowNum
            –INTO    #Databases
            –FROM    sys.databases
            –WHERE   database_id > 4 
            –DECLARE @DatabaseCurrentPosition INT = 1 
            –WHILE @DatabaseCurrentPosition <= ( SELECT  COUNT(1)
            —                                    FROM    #Databases
            —                                  )
            —    BEGIN 
                    SELECT  @DatabaseName = @DBName
                    
                    SET @ExportFilePath = @ExportPath + @DatabaseName      
                    EXECUTE master.dbo.xp_create_subdir @ExportFilePath  
                   
                    IF OBJECT_ID(‘tempdb..#Procedures’) IS NOT NULL
                        DROP TABLE #Procedures  
                    CREATE TABLE #Procedures
                        (
                          RoutineName NVARCHAR(MAX) ,
                          RowNum INT ,
                          ObjectID INT
                        ) 
                   
                      
                     SET @GetProcedureNames = ‘ INSERT INTO #Procedures
                         SELECT QUOTENAME(s.[name]) + ”.” + QUOTENAME(o.[name]) AS RoutineName 
                     ,ROW_NUMBER() OVER ( ORDER BY s.[name],o.[name]) AS RowNum,sm.object_id as ObjectID
                     FROM ‘
                        + @DatabaseName + ‘.sys.objects AS o  INNER JOIN ‘
                        + @DatabaseName
                        + ‘.sys.schemas AS s ON s.[schema_id] = o.[schema_id] INNER JOIN ‘
                        + @DatabaseName
                        + ‘.sys.sql_modules sm ON o.[object_id]=sm.[object_id]           
                        WHERE type IN (”p”,”v”,”fn”) AND o.is_ms_shipped = 0′ 
                       
                   
                    EXEC(@GetProcedureNames)
                
                    IF ( ( SELECT   COUNT(1) FROM     #Procedures ) > =1 )
                      
                        BEGIN
                  
                            DECLARE @ProcedureCurrentPosition INT = 1 
                            WHILE @ProcedureCurrentPosition <= ( SELECT
                                                              COUNT(1)
                                                              FROM
                                                              #Procedures
                                                              )
                                BEGIN 
                                    DECLARE @ProcedureContent NVARCHAR(MAX)    
                                    DECLARE @ProcedureName NVARCHAR(MAX)  
                                    DECLARE @ObjectID INT
                                   
                                    Select  @ProcedureName = RoutineName ,
                                            @ObjectID = ObjectID
                                    FROM    #Procedures
                                    WHERE   RowNum = @ProcedureCurrentPosition
                                    SET @ExportFilePath = @ExportPath + @DatabaseName + ‘\’ + @ProcedureName+ ‘.sql’ 
                                    DECLARE @Que NVARCHAR(MAX)= ‘Select Definition from ‘
                                        + @dataBaseName
                                        + ‘.sys.sql_modules sm where sm.[object_id]=’
                                        + CAST (@objectID AS NVARCHAR)
                         
                                    DECLARE @sql NVARCHAR(4000)       
                                    SELECT  @sql = ‘bcp ‘ + ‘”‘ + @Que +'”‘
                                            + ‘ queryout ‘ + ‘”‘+@ExportFilePath+'”‘
                                            + ‘ -c -t -T -S ‘
                                            + @ServerName
                                            print @sql
                                    EXEC xp_cmdshell @sql  
                                    SET @ProcedureCurrentPosition = @ProcedureCurrentPosition+ 1 
                                END   
                        END     
                    –SET @DatabaseCurrentPosition = @DatabaseCurrentPosition
                    —    + 1 
           —     END    
        END TRY       
        BEGIN CATCH       
   — Raise an error with the details of the exception  
            DECLARE @ErrMsg NVARCHAR(4000) ,
                @ErrSeverity INT       
            SELECT  @ErrMsg = ERROR_MESSAGE() ,
                    @ErrSeverity = ERROR_SEVERITY()       
            RAISERROR(@ErrMsg, @ErrSeverity,1)       
            RETURN       
        END CATCH ;   
    END

    

I would like to have feedback from my blog readers.

Please post your feedback, question, or comments about this article.

Advertisements

5 thoughts on “Backup script for all stored procedures for a particular Database

  1. Can not understand how this can save time.. too many syntax error, I have to sanitize so much error and still got error…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s