Automatically generate next month database based on monthly database--3

Data | database


--Create a job for the last day of the month, invoke the stored procedure above to automatically create the database

Use Mastergo

--Set the SQL Agent service to automatically start exec msdb. Sp_set_sqlagent_properties @auto_start =1go

--Create job exec msdb ... Sp_add_job @job_name =n ' automatic database processing '

--Create job step declare @sql varchar, @dbname varchar Select @sql =n ' exec sp_proccopydb '-Invoke stored procedures for automated libraries, @dbname =db_ Name ()--database name to perform automatic library processing

EXEC msdb.. Sp_add_jobstep @job_name =n ' automatic database processing ', @step_name = ' database processing ', @subsystem = ' TSQL ', @database_name = @dbname, @command = @sql, @ Retry_attempts = 5,--Retries @retry_interval = 5--Retry interval

--Add job schedule (last weekday per month) EXEC msdb.dbo.sp_add_jobschedule @job_name = n ' Automatic database processing ', @name = n ' time schedule ', @freq_type = +, @active_ start_time = 0,--0 points begin execution @freq_interval = 9,--the last weekday of the month @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relat Ive_interval = @freq_recurrence_factor = 1

--Add target server exec msdb.dbo.sp_add_jobserver @job_name = n ' Automatic database processing ', @server_name = N ' (local) '

/*--Known Issues

Some databases may not be able to be processed using the method of generating scripts due to SQL generated script bugs

--*/