Create a job executed on the last workday of each month and call the above stored procedure to automatically create a database

-- Create a job executed on the last workday of each month and call the above stored procedure to automatically create a database

Use master
Go

-- Set the SQL Agent service to automatically start
Exec MSDB .. sp_set_sqlagent_properties @ auto_start = 1
Go

-- Create a job
Exec MSDB .. sp_add_job @ job_name = n' automatic database creation Process'

-- Create a job
Declare @ SQL varchar (800), @ dbname varchar (250)
Select @ SQL = n' exec sp_proccopydb' -- call the stored procedure of the automatic database creation
, @ Dbname = db_name () -- Name of the database that executes automatic database creation

Exec MSDB .. sp_add_jobstep @ job_name = n 'automatic database creation process ',
@ Step_name = 'database creation process ',
@ Subsystem = 'tsql ',
@ Database_name = @ dbname,
@ Command = @ SQL,
@ Retry_attempts = 5, -- number of retries
@ Retry_interval = 5 -- Retry Interval

-- Add Job Scheduling (the last workday of each month)
Exec MSDB. DBO. sp_add_jobschedule @ job_name = n' automatic database creation process ',
@ Name = n' schedule ',
@ Freq_type = 32,
@ Active_start_time = 0, -- start execution at 0
@ Freq_interval = 9, -- last workday of each month
@ Freq_subday_type = 1,
@ Freq_subday_interval = 0,
@ Freq_relative_interval = 16,
@ Freq_recurrence_factor = 1

-- Add the target server
Exec MSDB. DBO. sp_add_jobserver
@ Job_name = n' automatic database creation process ',
@ SERVER_NAME = n' (local )'

/* -- Known issues

Due to the SQL script generation bug, some databases may not be able to use the script generation method for processing.

--*/