I need to know how to make a SQL query run daily using a SQL Server Agent job, with minimum required configuration settings.
This question is related to
sql
sql-server
database
sql-server-2008
sql-job
The answer is
Expand the SQL Server Agent node and right click the Jobs node in SQL Server Agent and select
'New Job'In the
'New Job'window enter the name of the job and a description on the'General'tab.Select
'Steps'on the left hand side of the window and click'New'at the bottom.In the
'Steps'window enter a step name and select the database you want the query to run against.Paste in the T-SQL command you want to run into the Command window and click
'OK'.Click on the
'Schedule'menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).Click
'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)
Here's a sample code:
Exec sp_add_schedule
@schedule_name = N'SchedulName'
@freq_type = 1
@active_start_time = 08300
Using T-SQL:
My job is executing stored procedure. You can easy change @command to run your sql.
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 ;
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
I made an animated GIF of the steps in the accepted answer. This is from MSSQL Server 2012
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
enjoy it.............
Source: Stackoverflow.com