The goal of this post is to show how you can create a schedule and attach an existing job to that schedule.
1) Open SQL Server Manament Studio (SSMS), connect to SQL Server, and right click on the Sql Server Agent \ Jobs \ Your Job.
Select Properties and click.
If the job already has a schedule, you can edit it or remove it.
3) Enter a name for the schedule, then select the type of schedule you want.
Here is some information:
Schedule Type: the option are straightforward. For “Start whenever the CPUs become idle” you need to set up an idle CPU condition (see http://msdn.microsoft.com/en-us/library/ms189065.aspx)
Enabled: here you can disable a schedule without deleting it.
You can select what type of occurrence, the days of the week and/or how many days/weeks/months to skip between executions.
For daily schedules, you can specify how many seconds/minutes or hours between executions.
Note: By default, Sql Agent will not execute a job more often than four times a minute (every 15 seconds). In general, the scheduler cannot be used for “real time” events. The job start can be delayed depending on the system load.
After creating or updating a schedule, you can check the next execution date and time.
Preconditions: Sql Agent has to be running for the next execution date/time to be calculated.
In SSMS, expand the Sql Server Agent node under Object Explorer, then double-click on Job Activity Monitor. Look for your job name and check the column “Next Run”.
You can check the job history at any time to confirm that the schedule was triggered correctly and the job succeeded.
Just right click on the job and select “View History”.
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm
Is the Job History cleared whenever the log is truncated? "BACKUP * WITH ... TRUNCATE" Where I work they've decided to truncate logs instead of backing them up. I have noticed that the agent job I setup here to run over the weekend doesn't show any job history. It does however indicate that it was run in the "Last Executed" field.
hi,thanks for your Instructions
but when i want to backing up from a DB every day automaticly,The second will overwrite the previous backup.
i want to have one Separated back up every day
please help me
Hi sahar. This will help you
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory
SET @path = 'G:\DATA BACKUP\'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
What to do if there is no SQL server agent folder at all? The last one is Management. :(
It is an SQL Server 2008 R2 and management studio: 10.50.4000.0
Can we call a stored procedure daily at a fixed time for a fixed duration throuh scheduler. if it so, then please tell me the steps.