sp_create_backup_job

for RuBoard

Sp_create_backup_job does what its name implies: It sets up a backup job for you. It uses SQL Server's SQLMAINT facility to set up a job that backs up the data and log files for a database you specify. The procedure takes six parameters, and these are summarized in Table 21-1.

Table 21-1. sp_create_backup_job PARAMETERS
Parameter Type Default Purpose
@dbname sysname None Specifies the name of the database to back up.
@OperatorNetSendAddress sysname None Specifies the NET SEND address of the operator.
@ScheduledStart int 200000 Specifies the start time of the job.
@PlanName sysname " Specifies the name of the maintenance plan to create.
@DataBackupName sysname " Names the data backup portion of the job.
@LogBackupName sysname " Names the log portion of the job.

Here's the source code (Listing 21-9):

Listing 21-9 sp_create_backup_job.
 USE master GO IF OBJECT_ID('dbo.sp_create_backup_job') IS NOT NULL   DROP PROC dbo.sp_create_backup_job GO CREATE PROC dbo.sp_create_backup_job @dbname sysname, @OperatorNetSendAddress sysname, @ScheduledStart int=200000, @PlanName sysname='', @DataBackupName sysname='', @LogBackupName sysname='' AS DECLARE @execstr varchar(8000), @JobID uniqueidentifier, @StepID int, @devname sysname DECLARE @PlanID uniqueidentifier, @DataCmd varchar(8000), @LogCmd varchar(8000) SET @PlanName='Daily Backup for '+ @dbname SET @DataBackupName='Data backup for '+@dbname SET @LogBackupName='Log backup for '+@dbname -- Delete the operator if it already exists IF EXISTS(SELECT * FROM msdb.dbo.sysoperators WHERE name = 'Oper') EXEC msdb.dbo.sp_delete_operator 'Oper' -- Add the operator EXEC msdb.dbo.sp_add_operator @name = 'Oper',     @enabled = 1,     @email_address ='',     @pager_address = '',     @weekday_pager_start_time = 090000,     @weekday_pager_end_time = 210000,     @pager_days = 127,     @netsend_address=@OperatorNetSendAddress -- Delete the job from sysdbmaintplans and related tables if it exists SELECT @PlanID = plan_id FROM msdb.dbo.sysdbmaintplans WHERE plan_name=@PlanName; IF @@ROWCOUNT<>0 BEGIN   DECLARE job CURSOR FOR   SELECT job_id FROM msdb.dbo.sysdbmaintplan_jobs   WHERE plan_id=@PlanID   OPEN job   FETCH job INTO @JobID   WHILE (@@FETCH_STATUS=0) BEGIN     EXEC msdb.dbo.sp_delete_job @JOBID     FETCH job INTO @JobID   END   DEALLOCATE job   DELETE msdb.dbo.sysdbmaintplan_history WHERE plan_id =@PlanID   DELETE msdb.dbo.sysdbmaintplan_jobs WHERE plan_id =@PlanID   DELETE msdb.dbo.sysdbmaintplan_databases WHERE plan_id =@PlanID   DELETE msdb.dbo.sysdbmaintplans WHERE plan_id =@PlanID END -- Gen a new GUID, then insert it into sysdbmaintplans SELECT @PlanID = NEWID() INSERT msdb.dbo.sysdbmaintplans (plan_id, plan_name, max_history_rows, remote_history_server, max_remote_history_rows) VALUES (@PlanID, @PlanName, 1000, N'', 0) DELETE msdb.dbo.sysdbmaintplan_jobs WHERE plan_id = @PlanID -- Setup generic Data and Log xp_sqlmaint calls that we'll use later SET @DataCmd='EXEC master.dbo.xp_sqlmaint ''-PlanID '+CAST(@PlanID AS varchar(36))+' -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir - BkExt "BAK" -DelBkUps 7days'''; SET @LogCmd='EXEC master.dbo.xp_sqlmaint ''-PlanID '+CAST(@PlanID AS varchar(36))+' -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog -UseDefDir - BkExt "TRN" -DelBkUps 7days'''; -- Delete the job if it already exists SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE name=@DataBackupName IF (@@ROWCOUNT>0) BEGIN         -- Don't delete if it's a multiserver job         IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers            WHERE (job_id=@JobID) AND (server_id <> 0))) BEGIN              RAISERROR ('Unable to create job because there is already a              multi-server job with the same name.',16,1)         END ELSE -- Delete the job             EXEC msdb.dbo.sp_delete_job @job_id=@JobID END -- Add the backup job EXEC msdb.dbo.sp_add_job     @job_name = @DataBackupName,     @enabled = 1,     @category_id=3,     @description = @DataBackupName,     @notify_level_eventlog = 2,     @notify_level_netsend = 2,     @notify_netsend_operator_name='Oper',     @delete_level = 0 SELECT @JobID=job_id FROM msdb.dbo.sysjobs WHERE name=@DataBackupName -- Add the job to sysdbmaintplan_jobs INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, @JobID) -- Schedule the job EXEC msdb.dbo.sp_add_jobschedule @job_id=@JobID,     @name = 'ScheduledBackup',     @freq_type = 4, -- everyday     @freq_interval = 1,     @active_start_time = @ScheduledStart -- Add the database to sysdbmaintplan_databases IF NOT EXISTS(SELECT * FROM msdb.dbo.sysdbmaintplan_databases WHERE plan_id = @PlanID AND database_name =   @dbname)   INSERT msdb.dbo.sysdbmaintplan_databases (plan_id, database_name) VALUES (@PlanID, @dbname) -- Add a job step to back up the database EXEC msdb.dbo.sp_add_jobstep @job_id=@JobID, @step_name='DataBackup', @subsystem='TSQL', @command=@DataCmd, @flags=4,@on_success_action=1 -- Associate the job with the job server EXEC msdb.dbo.sp_add_jobserver @job_id=@JobID -- Add a job and job step to back up its log IF (@dbname<>'master') AND (DATABASEPROPERTY(@dbname,'IsTruncLog')=0) BEGIN   -- Delete the job if it already exists   SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE name=@LogBackupName   IF (@@ROWCOUNT>0) BEGIN           -- Don't delete if it's a multiserver job           IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers               WHERE (job_id=@JobID) AND (server_id <> 0))) BEGIN               RAISERROR ('Unable to create job because there is already a               multi-server job with the same name.',16,1)           END ELSE -- Delete the job               EXEC msdb.dbo.sp_delete_job @job_id=@JobID   END   -- Add the backup job   EXEC msdb.dbo.sp_add_job       @job_name = @LogBackupName,       @enabled = 1,       @category_id=3,       @description = @LogBackupName,       @notify_level_eventlog = 2,       @notify_level_netsend = 2,       @notify_netsend_operator_name='Oper',       @delete_level = 0   SELECT @JobID=job_id FROM msdb.dbo.sysjobs WHERE name=@LogBackupName   -- Add the job to sysdbmaintplan_jobs   INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, @JobID)   -- Schedule the job   EXEC msdb.dbo.sp_add_jobschedule @job_id=@JobID,       @name = 'ScheduledLogBackup',       @freq_type = 4, -- everyday       @freq_interval = 1,       @active_start_time = @ScheduledStart       -- Add the database to sysdbmaintplan_databases       IF NOT EXISTS(SELECT * FROM msdb.dbo.sysdbmaintplan_databases                     WHERE plan_id = @PlanID AND database_name = @dbname)              INSERT msdb.dbo.sysdbmaintplan_databases (plan_id, database_name)              VALUES (@PlanID,   @dbname)       EXEC msdb.dbo.sp_add_jobstep @job_id=@JobID,       @step_name='LogBackup',       @subsystem='TSQL',       @command=@LogCmd,       @flags=4,@on_success_action=1       -- Associate the job with the job server       EXEC msdb.dbo.sp_add_jobserver @job_id=@JobID END 

As you can see, the routine is a bit involved, but a certain amount of complexity is unavoidable because of the steps required to set up and manage a SQL Server maintenance plan. From a usability standpoint, you can call the procedure and forget about the details for the most part.

for RuBoard


The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML
ISBN: 201700468
EAN: N/A
Year: 2005
Pages: 223

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net