The Gurus Guide to SQL Server Stored Procedures, XML, and HTML [Electronic resources] نسخه متنی

sp_create_backup_job

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.

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.