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:
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
Use server agent job
Post a Comment