Anyone know how to make it say All System Databases in maintenance plan with TSQL

Question

Post reply

Anyone know how to make it say All System Databases in maintenance plan with TSQL

imSQrLy

Ten Centuries

Points: 1335
More actions
April 22, 2008 at 11:25 am

#189399

I have created a maintenance plan with T-SQL. I know how to add databases one by one with sp_add_maintenance_plan_db. however when i add all and only the system databases I want it to say All System Databases like it does when you use the wizard, rather than it simply listing the system databases I added. Anyone know how to have it say this. Is there a special sys_name for all system databases. I tried System_Databases.
Code as follows:
use msdb
declare @new_plan_id uniqueidentifier
declare @new_command nvarchar(1000)
exec msdb.dbo.sp_add_maintenance_plan 'MY System Maintenance Plan',@plan_id=@new_plan_id output
--HERE IS WHERE I WANT TO SAY SOMETHING LIKE 'ALL SYSTEM DATABASES'
exec msdb.dbo.sp_add_maintenance_plan_db @new_plan_id,'Master'
exec msdb.dbo.sp_add_maintenance_plan_db @new_plan_id,'Model'
exec msdb.dbo.sp_add_maintenance_plan_db @new_plan_id,'MSDB'
------------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Optimizations Job for DB Maintenance Plan ''MY System Maintenance Plan''')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Optimizations Job for DB Maintenance Plan ''MY System Maintenance Plan'''' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback1
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Optimizations Job for DB Maintenance Plan ''MY System Maintenance Plan'''
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Optimizations Job for DB Maintenance Plan ''MY System Maintenance Plan''', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
-- Add the job steps
set @new_command = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID ' + convert(nvarchar(40),@new_plan_id) + ' -WriteHistory -UpdOptiStats 10 -RmUnusedSpace 50 20 '''
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = @new_command, @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 20080422, @active_start_time = 20000, @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 QuitWithRollback1
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback1
END
COMMIT TRANSACTION
GOTO EndSave1
QuitWithRollback1:
print 'rolled back'
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave1:
exec sp_add_maintenance_plan_job @new_plan_id,@JobID
------------------------------------------------------------------------------------------------------------------------------------
set @JobID = null
BEGIN TRANSACTION
--DECLARE @JobID BINARY(16)
--DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Integrity Checks Job for DB Maintenance Plan ''MY System Maintenance Plan''')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Integrity Checks Job for DB Maintenance Plan ''MY System Maintenance Plan'''' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback2
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Integrity Checks Job for DB Maintenance Plan ''MY System Maintenance Plan'''
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Integrity Checks Job for DB Maintenance Plan ''MY System Maintenance Plan''', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback2
-- Add the job steps
set @new_command = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID ' + convert(nvarchar(40),@new_plan_id) + ' -WriteHistory -CkDB '''
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = @new_command, @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback2
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback2
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 20080422, @active_start_time = 10000, @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 QuitWithRollback2
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback2
END
COMMIT TRANSACTION
GOTO EndSave2
QuitWithRollback2:
print 'rolled back'
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave2:
exec sp_add_maintenance_plan_job @new_plan_id,@JobID
---------------------------------------------------------------------------------------------------------------------------------------------------
set @JobID = null
BEGIN TRANSACTION
--DECLARE @JobID BINARY(16)
--DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'DB Backup Job for DB Maintenance Plan ''MY System Maintenance Plan''')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''DB Backup Job for DB Maintenance Plan ''MY System Maintenance Plan'''' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback3
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'DB Backup Job for DB Maintenance Plan ''MY System Maintenance Plan'''
SELECT @JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'DB Backup Job for DB Maintenance Plan ''MY System Maintenance Plan''', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'Database Maintenance', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback3
-- Add the job steps
set @new_command = N'EXECUTE master.dbo.xp_sqlmaint N''-PlanID ' + convert(nvarchar(40),@new_plan_id) + ' -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 14DAYS -CrBkSubDir -BkExt "BAK"'''
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Step 1', @command = @new_command, @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 4, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback3
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback3
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1', @enabled = 1, @freq_type = 8, @active_start_date = 20080422, @active_start_time = 30000, @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 QuitWithRollback3
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback3
END
COMMIT TRANSACTION
GOTO EndSave3
QuitWithRollback3:
print 'rolled back'
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave3:
exec sp_add_maintenance_plan_job @new_plan_id,@JobID
Jimmy
"I'm still learning the things i thought i knew!"

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Matija Lah SSCertifiable Points: 6429 More actions · Answer 1

Selecting "All databases" in GUI is translated into as many queries/statements as there are databases.

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com