T-SQL Maintenance Plan Creation

,

Use this script ot be able to create DB Maintenance Plans fast.
If you need to create the plans on servers similarly set up, edit the required parameters once, and when you create a new plan, you will only need to specify a database name and one task start time, run the script and the plan is created in a second.
The script also changes the owner of the generated jobs to 'sa' or other login.

set nocount on

BEGIN TRANSACTION 

declare @ServerName varchar(22), @DBName varchar(128), @ReportPath varchar(128), @BackupPath varchar(128) 
declare @ReportsRetention varchar(11), @FullBckRetention varchar(11), @TranBckRetention varchar(11), @HistRows int, @Owner varchar(128) 
declare @FullBckStartTime varchar(6), @TranBckStartTime varchar(6), @IntegriStartTime varchar(6), @OptimalStartTime varchar(6)

DECLARE @ReturnCode INT 
DECLARE @JobID0 nchar(36), @JobID1 nchar(36), @JobID2 nchar(36), @JobID3 nchar(36), @JobID4 nchar(36) 
DECLARE @JobID5 nchar(36), @JobID6 nchar(36), @JobID7 nchar(36), @JobIDD nchar(36) 

DECLARE @FullBckCommand nvarchar(400), @TranBckCommand nvarchar(400), @IntegriCommand nvarchar(400), @OptimalCommand nvarchar(400)
declare @JobName0 varchar(128), @JobName2 varchar(128), @JobName4 varchar(128), @JobName6 varchar(128) 
DECLARE @PlanID nchar(36)  
declare @Date char(8)

-------------------------------------------------------------
--   Set the parameters: BEGIN
-------------------------------------------------------------

SELECT @ServerName = @@ServerName
SELECT @DBName = 'PUBS'
SELECT @ReportPath = 'E:\egdata\errorlog'
SELECT @BackupPath = 'E:\egdata\BACKUP'
SELECT @ReportsRetention = '1WEEKS'
SELECT @FullBckRetention = '47HOURS'
SELECT @TranBckRetention = '47HOURS'
SELECT @Owner = 'sa'

-- Create tasks' schedules: 1. Tranlog backup,2. Integrity, 3. Optimization,4. Backup(Full)
-- set the 1st and it will add 1 hour for the 2nd, anotherhour for the 3rd, 1 hour more for the 4th task
SELECT @TranBckStartTime = '200000'

SELECT @FullBckStartTime = case when @TranBckStartTime + 30000 >= 240000 then @TranBckStartTime + 30000 - 240000 else @TranBckStartTime + 30000 end
SELECT @IntegriStartTime = case when @TranBckStartTime + 10000 >= 240000 then @TranBckStartTime + 10000 - 240000 else @TranBckStartTime + 10000 end
SELECT @OptimalStartTime = case when @TranBckStartTime + 20000 >= 240000 then @TranBckStartTime + 20000 - 240000 else @TranBckStartTime + 20000 end 

-- or set the schedules manually 
/*
SELECT @FullBckStartTime = '230000' 
SELECT @IntegriStartTime = '210000' 
SELECT @OptimalStartTime = '220000' 
*/

SELECT 
@Date = datepart(yy, getdate())*10000 + datepart(mm, getdate())*100 + datepart(dd, getdate())  
-- or set the start date manually
-- @Date = '2006xxxx'

-- set the number of rows for maint. plan history
SELECT @HistRows = 10000

SELECT  @PlanID = NEWID()                             
INSERT  msdb.dbo.sysdbmaintplans (plan_id, plan_name,  owner, max_history_rows, remote_history_server, max_remote_history_rows) 
VALUES (@PlanID, @DBName + N' DB Maintenance Plan', @Owner, @HistRows, N'', 0)              

-------------------------------------------------------------
--   Set the parameters: END
-------------------------------------------------------------


print '================================================================================================'  
print 'PLAN CREATED: '
print '------------------------------------------------------------------------------------------------' 
print 'Name: ' + CHAR(9) + CHAR(9) + @DBName + N' DB Maintenance Plan' 
print 'ID: ' + CHAR(9) + CHAR(9) + @planid      
print '================================================================================================'  

select @FullBckCommand = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID ' + @PlanID + ' -Rpt "' + @ReportPath + '\' + @DBName + ' DB Maintenance Plan4.txt" -DelTxtRpt ' + @ReportsRetention + ' -WriteHistory  -VrfyBackup -BkUpOnlyIfClean -CkDB  -BkUpMedia DISK -BkUpDB "' + @BackupPath + '" -DelBkUps ' + @FullBckRetention + ' -CrBkSubDir -BkExt "BAK"'''
select @TranBckCommand = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID ' + @PlanID + ' -Rpt "' + @ReportPath + '\' + @DBName + ' DB Maintenance Plan6.txt" -DelTxtRpt ' + @ReportsRetention + ' -WriteHistory  -VrfyBackup -BkUpOnlyIfClean -CkDB  -BkUpMedia DISK -BkUpLog "' + @BackupPath + '" -DelBkUps ' + @TranBckRetention + ' -CrBkSubDir -BkExt "TRN"'''
select @IntegriCommand = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID ' + @PlanID + ' -Rpt "' + @ReportPath + '\' + @DBName + ' DB Maintenance Plan2.txt" -DelTxtRpt ' + @ReportsRetention + ' -WriteHistory  -CkDB  '''
select @OptimalCommand = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID ' + @PlanID + ' -Rpt "' + @ReportPath + '\' + @DBName + ' DB Maintenance Plan0.txt" -DelTxtRpt ' + @ReportsRetention + ' -WriteHistory  -RebldIdx 100 '''

select @JobName4 = N'DB Backup Job for DB Maintenance Plan ''' + @DBName + ' DB Maintenance Plan'''
select @JobName6 = N'Transaction Log Backup Job for DB Maintenance Plan ''' + @DBName + ' DB Maintenance Plan'''
select @JobName2 = N'Integrity Checks Job for DB Maintenance Plan ''' + @DBName + ' DB Maintenance Plan'''
select @JobName0 = N'Optimizations Job for DB Maintenance Plan ''' + @DBName + ' DB Maintenance Plan'''

  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID4 OUTPUT, @job_name = @JobName4, @enabled = 1, @category_id = 3
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @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 = @FullBckCommand, @flags = 4
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID4, @name = N'Schedule 1', @enabled = 1, @freq_type = 4, @active_start_date = @Date, @active_start_time = @FullBckStartTime, @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 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID4, @server_name = @ServerName 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

print 'Full Backup Job: '
print '------------------------------------------------------------------------------------------------' 
print 'Name: ' + CHAR(9) + CHAR(9) + @JobName4 
print 'ID: ' + CHAR(9) + CHAR(9) + @JobID4
print '================================================================================================'  

  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID6 OUTPUT, @job_name = @JobName6, @enabled = 1, @category_id = 3
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @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 = @TranBckCommand, @flags = 4
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID6, @name = N'Schedule 1', @enabled = 1, @freq_type = 4, @active_start_date = @Date, @active_start_time = @TranBckStartTime, @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 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID6, @server_name = @ServerName 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

print 'Tran Backup Job: '
print '------------------------------------------------------------------------------------------------' 
print 'Name: ' + CHAR(9) + CHAR(9) + @JobName6  
print 'ID: ' + CHAR(9) + CHAR(9) + @JobID6 
print '================================================================================================'  

  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID2 OUTPUT, @job_name = @JobName2, @enabled = 1, @category_id = 3
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @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 = @IntegriCommand, @flags = 4
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID2, @name = N'Schedule 1', @enabled = 1, @freq_type = 4, @active_start_date = @Date, @active_start_time = @IntegriStartTime, @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 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID2, @server_name = @ServerName 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

print 'Integrity C Job: '
print '------------------------------------------------------------------------------------------------' 
print 'Name: ' + CHAR(9) + CHAR(9) + @JobName2 
print 'ID: ' + CHAR(9) + CHAR(9) + @JobID2 
print '================================================================================================'  

  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID0 OUTPUT, @job_name = @JobName0, @enabled = 1, @category_id = 3
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @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 = @OptimalCommand, @flags = 4
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID0, @name = N'Schedule 1', @enabled = 1, @freq_type = 4, @active_start_date = @Date, @active_start_time = @OptimalStartTime, @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 
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID0, @server_name = @ServerName 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
--  SELECT @JobID0, @JobID1, @JobID2, @JobID3, @JobID4, @JobID5, @JobID6, @JobID7

print 'Optimalizat Job: '
print '------------------------------------------------------------------------------------------------' 
print 'Name: ' + CHAR(9) + CHAR(9) + @JobName0 
print 'ID: ' + CHAR(9) + CHAR(9) + @JobID0 
print '================================================================================================'  

print 'JOBS CREATED' 
print '================================================================================================'  

--  DECLARE  @ReturnCode INT  
--  BEGIN TRANSACTION         
  IF (SELECT COUNT(*) FROM msdb.dbo.sysdbmaintplan_databases WHERE plan_id = @PlanID AND database_name = @DBName) < 1 
INSERT msdb.dbo.sysdbmaintplan_databases (plan_id, database_name) VALUES (@PlanID, @DBName)

  DELETE FROM msdb.dbo.sysdbmaintplan_jobs WHERE plan_id = @PlanID

  INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, @JobID4)
  INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, @JobID6)
  INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, @JobID2)
  INSERT msdb.dbo.sysdbmaintplan_jobs VALUES (@PlanID, @JobID0)
IF (@@ERROR <> 0) GOTO QuitWithRollback 

print 'JOBS and DATABASE has been linked to PLAN.' 
print '================================================================================================'  

COMMIT TRANSACTION

GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
print '================================================================================================'  
  print CHAR(10) + CHAR(13) + 'Failed'  + CHAR(10) + CHAR(13)
print '================================================================================================'  

EndSave: 

BEGIN TRANSACTION 
           
  SELECT @ReturnCode = 0     
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID4, @automatic_post = 0 , @owner_login_name = @Owner
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback2 

  SELECT @ReturnCode = 0     
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID6, @automatic_post = 0 , @owner_login_name = @Owner
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback2 

  SELECT @ReturnCode = 0     
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID2, @automatic_post = 0 , @owner_login_name = @Owner
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback2 

  SELECT @ReturnCode = 0     
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID0, @automatic_post = 0 , @owner_login_name = @Owner
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback2 

print 'OWNER changed to ''' + @Owner + '''.'
print '================================================================================================'  

COMMIT TRANSACTION
          
GOTO   EndSave2              
QuitWithRollback2:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
print '================================================================================================'  
  print CHAR(10) + CHAR(13) + 'Failed'
print '================================================================================================'  
EndSave2: 

set nocount off

Rate

Share

Share

Rate