Feb 7, 2013

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

2 comments:

  1. Anonymous3/19/2013

    i have one store procedure that perform deletion operation now i want to automate that but how can i do without using master..it's throwing error that permission is denied i m using sql server 2008 R2...plz suggest appropriate solution...thanks

    ReplyDelete