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 
