Sql server backup stored procedures and auto schedule

When we create database backup it also backup the all the stored procedures in the database. So there is no need to separately backup the stored procedure. If you want to save the script of all or specific stored procedure you can generate the script of procedure by using sql server management studio (SSMS) or by writing following stored procedure:

SELECT ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = 'PROCEDURE'

     AND ROUTINE_NAME IN ('uspProc1','uspProc2')

Note: Modify WHERE clause according to your requirements and save its query result in the specific file.

How to auto generate the script of stored procedure and after some regular interval and save in some file:

Schedule the auto save the script of stored procedure after some regular interval:

Step 1: Creating stored procedure to generate the scrip of all stored procedures:

CREATE PROC uspBackupProc(

     @vcFilePath AS VARCHAR(500) = 'C:\MFS\',

     @ntBackupExpireDay AS TINYINT = 0

)

AS

BEGIN

     DECLARE @vcBackupQuery AS VARCHAR(8000)

     DECLARE @vcRemoveOldBackupQuery AS VARCHAR(8000)

    

     BEGIN TRY

    

          IF @ntBackupExpireDay = 0 BEGIN

         

              SET @vcFilePath += 'Proc_Backup.txt'

             

          END ELSE BEGIN

         

              SET @vcFilePath += 'Proc_'+ REPLACE(LOWER(REPLACE(

                   CONVERT(VARCHAR,GETDATE(),113),SPACE(1),'_')),':','_') + '.txt'

         

          END

         

          SET @vcBackupQuery = 'BCP "SELECT ROUTINE_DEFINITION

              FROM INFORMATION_SCHEMA.ROUTINES" QUERYOUT ' + @vcFilePath + ' -T -c -t'

         

          EXECUTE xp_cmdshell @vcBackupQuery

         

          IF @ntBackupExpireDay > 0 BEGIN

              SET @vcRemoveOldBackupQuery = ''

              EXECUTE xp_cmdshell @vcRemoveOldBackupQuery

          END

    

     END TRY

     BEGIN CATCH

    

          SELECT

              ERROR_MESSAGE() AS ErrMsg,

              ERROR_LINE() AS ErrLine

               

     END CATCH

END

Step 2: Creating the sql job to schedule it:

You can create a sql server agent job to execute the stored procedure uspBackupProc after some regular interval by using sql server management studio (SSMS) or you can execute the following sql scrip to create agent job. You have to modify the value variables in script as follow:

@vcFilePath: Write the path where you want to save the backup file. You have to manually create the folder if it is not present.

@ntBackupExpireDay:

    0: If you want create only one backup file by overwriting the same file next backup schedule.

    1: If you want to create the new backup file in each schedule.

@database_name: Name of the database where your stored procedures is.

@freq_subday_interval: After how many hours in each day backup schedule will occur. 

USE [msdb]

GO

DECLARE @vcFilePath AS NVARCHAR(200) = 'C:\SqlBackup\'

DECLARE @ntBackupExpireDay AS TINYINT = 0

DECLARE @database_name AS NVARCHAR(800) = N'Exact_Help'

DECLARE @freq_subday_interval AS INT = 24

DECLARE @command AS NVARCHAR(4000)

SET @command = N'EXECUTE ' + @database_name + '.[dbo].uspBackupProc @vcFilePath = '''+ @vcFilePath +''', @ntBackupExpireDay = ' + CAST(@ntBackupExpireDay AS VARCHAR) + ''

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Backup_Procedure',

          @enabled=1,

          @notify_level_eventlog=0,

          @notify_level_email=0,

          @notify_level_netsend=0,

          @notify_level_page=0,

          @delete_level=0,

          @description=N'No description available.',

          @category_name=N'[Uncategorized (Local)]',

          @owner_login_name=N'RITESHK7\riteshk', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute Procedure',

          @step_id=1,

          @cmdexec_success_code=0,

          @on_success_action=1,

          @on_success_step_id=0,

          @on_fail_action=2,

          @on_fail_step_id=0,

          @retry_attempts=0,

          @retry_interval=0,

          @os_run_priority=0, @subsystem=N'TSQL',

          @command= @command,

          @database_name=@database_name,

          @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'BackupSchedule',

          @enabled=1,

          @freq_type=4,

          @freq_interval=1,

          @freq_subday_type=8,

          @freq_subday_interval=@freq_subday_interval,

          @freq_relative_interval=0,

          @freq_recurrence_factor=0,

          @active_start_date=20130207,

          @active_end_date=99991231,

          @active_start_time=0,

          @active_end_time=235959,

          @schedule_uid=N'0ece793f-3087-441b-a3f5-1d070daae7be'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:


How to recover deleted data from table without using database backup in sql server 
Set up auto only schema or script backup in sql server
Sql server export stored procedures