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.
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):
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 |