[sql] SQL 쿼리가 매일 실행되도록 작업을 예약하는 방법은 무엇입니까?

t-sql에서이를 수행하려면 다음 시스템 저장 프로 시저를 사용하여 일일 작업을 예약 할 수 있습니다. 이 예에서는 매일 오전 1시에 예약합니다. 개별 저장 프로 시저의 구문 및 유효한 매개 변수 범위에 대한 자세한 내용은 Microsoft 도움말을 참조하십시오.

DECLARE @job_name NVARCHAR(128), @description NVARCHAR(512), @owner_login_name NVARCHAR(128), @database_name NVARCHAR(128);

SET @job_name = N'Some Title';
SET @description = N'Periodically do something';
SET @owner_login_name = N'login';
SET @database_name = N'Database_Name';

-- Delete job if it already exists:
IF EXISTS(SELECT job_id FROM msdb.dbo.sysjobs WHERE (name = @job_name))
BEGIN
    EXEC msdb.dbo.sp_delete_job
        @job_name = @job_name;
END

-- Create the job:
EXEC  msdb.dbo.sp_add_job
    @job_name=@job_name,
    @enabled=1,
    @notify_level_eventlog=0,
    @notify_level_email=2,
    @notify_level_netsend=2,
    @notify_level_page=2,
    @delete_level=0,
    @description=@description,
    @category_name=N'[Uncategorized (Local)]',
    @owner_login_name=@owner_login_name;

-- Add server:
EXEC msdb.dbo.sp_add_jobserver @job_name=@job_name;

-- Add step to execute SQL:
EXEC msdb.dbo.sp_add_jobstep
    @job_name=@job_name,
    @step_name=N'Execute SQL',
    @step_id=1,
    @cmdexec_success_code=0,
    @on_success_action=1,
    @on_fail_action=2,
    @retry_attempts=0,
    @retry_interval=0,
    @os_run_priority=0,
    @subsystem=N'TSQL',
    @command=N'EXEC my_stored_procedure; -- OR ANY SQL STATEMENT',
    @database_name=@database_name,
    @flags=0;

-- Update job to set start step:
EXEC msdb.dbo.sp_update_job
    @job_name=@job_name,
    @enabled=1,
    @start_step_id=1,
    @notify_level_eventlog=0,
    @notify_level_email=2,
    @notify_level_netsend=2,
    @notify_level_page=2,
    @delete_level=0,
    @description=@description,
    @category_name=N'[Uncategorized (Local)]',
    @owner_login_name=@owner_login_name,
    @notify_email_operator_name=N'',
    @notify_netsend_operator_name=N'',
    @notify_page_operator_name=N'';

-- Schedule job:
EXEC msdb.dbo.sp_add_jobschedule
    @job_name=@job_name,
    @name=N'Daily',
    @enabled=1,
    @freq_type=4,
    @freq_interval=1,
    @freq_subday_type=1,
    @freq_subday_interval=0,
    @freq_relative_interval=0,
    @freq_recurrence_factor=1,
    @active_start_date=20170101, --YYYYMMDD
    @active_end_date=99991231, --YYYYMMDD (this represents no end date)
    @active_start_time=010000, --HHMMSS
    @active_end_time=235959; --HHMMSS

답변

EXEC msdb.dbo.sp_add_job
   @job_name = N'MakeDailyJob',
   @enabled = 1,
   @description = N'Procedure execution every day' ;

 EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'MakeDailyJob',
    @step_name = N'Run Procedure',
    @subsystem = N'TSQL',
    @command = 'exec BackupFromConfig';

 EXEC msdb.dbo.sp_add_schedule
    @schedule_name = N'Everyday schedule',
    @freq_type = 4,  -- daily start
    @freq_interval = 1,
    @active_start_time = '230000' ;   -- start time 23:00:00

 EXEC msdb.dbo.sp_attach_schedule
   @job_name = N'MakeDailyJob',
   @schedule_name = N'Everyday schedule' ;

 EXEC msdb.dbo.sp_add_jobserver
   @job_name = N'MakeDailyJob',
   @server_name = @@servername ;

Exec sp_add_schedule
    @schedule_name = N'SchedulName'
    @freq_type = 1
    @active_start_time = 08300

DECLARE @pathName NVARCHAR(512),
 @databaseName NVARCHAR(512) SET @databaseName = 'Databasename' SET @pathName = 'C:\DBBackup\DBData\DBBackUp' + Convert(varchar(8), GETDATE(), 112) + '_' + Replace((Convert(varchar(8), GETDATE(), 108)),':','-')+ '.bak' BACKUP DATABASE @databaseName TO DISK = @pathName WITH NOFORMAT,
INIT,
NAME = N'',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO