This topic describes how to implement a database maintenance plan (DBMP)
with T-SQL commands. DBMP is better than a simple backup because it checks database
integrity, corrects small errors and optimizes performance.
Creating a DBMP
 |
With this query you just need to change global variables
and it's ready to run. |
use master
begin transaction
declare
@sPlanID nchar(36),
@sPlanName varchar(100),
@sLogDir varchar(100),
@sBackupDir varchar(100),
@sJobName varchar(255),
@sParam varchar(1000),
@sDBName varchar(100),
@iStartDate int,
@iBackupTime int,
@iOptiTime int,
@ReturnCode INT ,
@JobID0 nchar(36),
@JobID1 nchar(36),
@JobID2 nchar(36),
@JobID3 nchar(36),
@JobID4 nchar(36),
@JobID5 nchar(36),
@JobID6 nchar(36),
@JobID7 nchar(36),
@JobIDD nchar(36)
-- declare global variables
set @sDBName = 'BigDB' -- database name / change as required
set @sPlanName = 'BigDB Maintenance Plan' -- maintenance plan name / change as required
set @sLogDir = 'C:\MSSQL7\LOG' -- path to the Server log / change as required
set @sBackupDir = 'C:\MSSQL7\BACKUP' -- path to the backup / change as required
set @iStartDate = 20020425 -- date when the maintenance plan starts to apply (YYYYMMDD)
set @iBackupTime = 20000 -- time to start with making backup (2am)
set @iOptiTime = 10000 -- time to start optimization (1am)
-- get plan ID and insert it into the PLANS table
select @sPlanID = NEWID()
insert msdb.dbo.sysdbmaintplans (plan_id, plan_name, max_history_rows, remote_history_server, max_remote_history_rows)
values (@sPlanID, @sPlanName, 1000, '', 0)
-- start jobs
--!! select @PlanID = N'1403015E-AAC4-4481-81EF-84BCAF0CBC91'
set @sJobName = 'DB Backup Job for DB Maintenance Plan '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID4 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sParam = 'exec master.dbo.xp_sqlmaint N''-PlanID '+@sPlanID+' -Rpt "'+@sLogDir+'\DBMP (Backup) '+@sDBName+'.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpOnlyifClean -CkDBRepair -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1 -BkExt ""'''
exec @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID4,
@step_id = 1,
@step_name = N'Step 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID4,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = @iStartDate,
@active_start_time = @iBackupTime,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID4,
@server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sJobName = 'Transaction Log Backup Job for DB Maintenance Plan '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID6 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sParam = 'exec master.dbo.xp_sqlmaint N''-PlanID '+@sPlanID+' -Rpt "'+@sLogDir+'\DBMP (Log Backup) '+@sDBName+'.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpOnlyifClean -CkDBRepair -BkUpMedia DISK -BkUpLog -UseDefDir -DelBkUps 1 -BkExt ""'''
exec @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID6,
@step_id = 1,
@step_name = N'Step 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID6,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 8,
@active_start_date = 0,
@active_start_time = 0,
@freq_interval = 126,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID6,
@server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sJobName = 'Integrity Checks Job for DB Maintenance Plan '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID2 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sParam = 'exec master.dbo.xp_sqlmaint N''-PlanID '+@sPlanID+' -Rpt "'+@sLogDir+'\DBMP (Integrity) '+@sDBName+'.txt" -DelTxtRpt 1WEEKS -WriteHistory -CkDBRepair '''
exec @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID2,
@step_id = 1,
@step_name = N'Step 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID2,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = @iStartDate,
@active_start_time = 0,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID2,
@server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sJobName = 'Optimizations Job for DB Maintenance Plan '''+@sPlanName+''''
exec @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID0 output,
@job_name = @sJobName,
@enabled = 1,
@category_id = 3
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
set @sParam = 'exec master.dbo.xp_sqlmaint N''-PlanID '+@sPlanID+' -Rpt "'+@sLogDir+'\DBMP (Optimisations) '+@sDBName+'.txt" -DelTxtRpt 1WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '''
exec @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID0,
@step_id = 1,
@step_name = N'Step 1',
@subsystem = N'TSQL',
@on_success_action = 1,
@on_fail_action = 2,
@command = @sParam,
@flags = 4
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID0,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = @iStartDate,
@active_start_time = @iOptiTime,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_end_date = 99991231,
@active_end_time = 235959
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
exec @ReturnCode = msdb.dbo.sp_add_jobserver
@job_id = @JobID0,
@server_name = N'(local)'
if (@@ERROR <> 0 OR @ReturnCode <> 0) goto QuitWithRollback
if (select Count(*)
from msdb.dbo.sysdbmaintplan_databases
where plan_id = @sPlanID AND database_name = @sDBName) < 1
insert msdb.dbo.sysdbmaintplan_databases (plan_id, database_name) values (@sPlanID, @sDBName)
delete msdb.dbo.sysdbmaintplan_jobs where plan_id = @sPlanID
insert msdb.dbo.sysdbmaintplan_jobs
values (@sPlanID, @JobID0)
insert msdb.dbo.sysdbmaintplan_jobs
values (@sPlanID, @JobID2)
insert msdb.dbo.sysdbmaintplan_jobs
values (@sPlanID, @JobID4)
insert msdb.dbo.sysdbmaintplan_jobs
values (@sPlanID, @JobID6)
commit transaction
goto EndSave
QuitWithRollback: if (@@TRANCOUNT > 0) rollback transaction
EndSave:
View DBMPs
To see a list of active maintenance plans run the following query:
select *
from msdb.dbo.sysdbmaintplans