To do this in t-sql, you can use the following system stored procedures to schedule a daily job. This example schedules daily at 1:00 AM. See Microsoft help for details on syntax of the individual stored procedures and valid range of parameters.
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
if You want daily backup // following sql script store in C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql
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
open the Task scheduler
create task-> select Triggers tab Select New .
Button Select Daily Radio button
click Ok Button
then click Action tab Select New.
Button Put "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S ADMIN-PC -i "C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql" in the program/script text box(make sure Match your files path and Put the double quoted path in start-> search box and if it find then click it and see the backup is there or not)
-- the above path may be insted 100 write 90 "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S ADMIN-PC -i"C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql"
then click ok button
the Script will execute on time which you select on Trigger tab on daily basis
6条答案
按热度按时间rqmkfv5c1#
'New Job'
'New Job'
window enter the name of the job and a description on the'General'
tab.'Steps'
on the left hand side of the window and click'New'
at the bottom.'Steps'
window enter a step name and select the database you want the query to run against.'OK'
.'Schedule'
menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).'OK'
- and that should be it.(There are of course other options you can add - but I would say that is the bare minimum you need to get a job set up and scheduled)
chy5wohz2#
I made an animated GIF of the steps in the accepted answer. This is from MSSQL Server 2012
6ss1mwsb3#
To do this in t-sql, you can use the following system stored procedures to schedule a daily job. This example schedules daily at 1:00 AM. See Microsoft help for details on syntax of the individual stored procedures and valid range of parameters.
9gm1akwq4#
Using T-SQL: My job is executing stored procedure. You can easy change
@command
to run your sql.cczfrluj5#
if You want daily backup // following sql script store in C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql
open the Task scheduler
create task-> select
Triggers
tab SelectNew
.Button Select Daily Radio button
click
Ok
Buttonthen click
Action
tab Select New.Button Put
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
-S ADMIN-PC -i"C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql"
in the program/script text box(make sure Match your files path and Put the double quoted path in start-> search box and if it find then click it and see the backup is there or not)-- the above path may be insted 100 write 90
"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S ADMIN-PC -i
"C:\Users\admin\Desktop\DBScript\DBBackUpSQL.sql"
then click ok button
the Script will execute on time which you select on Trigger tab on daily basis
enjoy it.............
qacovj5a6#
Here's a sample code: