DB Maintenance Plan

This topic describes how to implement a Database Maintenance Plan (DBMP) using T-SQL commands. A DBMP provides more functionality than a simple backup because it checks database integrity, repairs minor errors, and optimizes database performance.

Creating a DBMP

Using the query below, you only need to modify the global variables, and the script is 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 location / change as required
  set @iStartDate = 20020425       -- Date when the maintenance plan becomes active (YYYYMMDD)
  set @iBackupTime = 20000         -- Time to start backups (2:00 AM)
  set @iOptiTime = 10000           -- Time to start optimization (1:00 AM)

  -- 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+''''
  ...

Viewing DBMPs

To display a list of active maintenance plans, run the following query:

select *
from msdb.dbo.sysdbmaintplans