- Create a job that performed a monthly last working day, calling the above stored procedure to automatically create a database
Use mastergo
- Set the SQL Agent service to automatically start EXEC MSDB..SP_SET_SQLAGENT_PROPERTIES @ Auto_Start = 1GO
- Create Jobs EXEC MSDB..SP_ADD_JOB @ Job_Name = N 'Automatic Building Library Processive
- Creating a Job Step Declare @SQL VARCHAR (800), @ DBNAME VARCHAR (250) SELECT @ SQL = N'Exec sp_proccopydb '- Call the stored procedure of the auto-building library, @ dbname = db_name () - Execute Automatic Building Library Processing database name
EXEC MSDB..SP_ADD_JOBSTEP @ Job_name = N 'Automated Library Processing', @ step_name = 'Construction Library Processive', @ Subsystem = 'TSQL', @ database_name = @ DBNAME, @ Command = @ SQL, @ Retry_attempts = 5, - - Return the number of times @retry_interval = 5 - retry interval
- Add job scheduling (Monthly Work Day) EXEC MSDB.DBO.SP_ADD_JOBSCHEDULE @JOB_NAME = N 'Automated Library Processing', @Name = N 'Time Arranger', @freq_type = 32, @Active_Start_Time = 0, - -0 point to execute @freq_interval = 9, - Monthly last business day @freq_subday_type = 1, @freq_rlative_interval = 16, @freq_recurrence_factor = 1
- Add Target Server EXEC MSDB.DBO.SP_ADD_JOBSERVER @JOB_NAME = N 'Automated Library Processing', @ Server_Name = N '(Local)'
/ * - Known problem
Since the bug of SQL generates scripts, some databases may not be processed with methods of generating scripts.
- * /
