SQL Server Agent allows you to automate various administrative tasks. One of these tasks involves using SQL Server Agent to create and schedule a task that automates database management.
- Start the SQL Server Agent service
- Open SQL Server Management Studio and expand the SQL Server Agent folder.
- Create a new SQL Server Agent job
- Go to the SQL Server Agent Operation Steps screen
- Add SQL Server Agent Task Steps
- Schedule a SQL Server Agent job
Start the SQL Server Agent service
Open the Microsoft SQL Server Configuration Manager and locate the SQL Server Agent service. If the status of this service is “RUNNING”, you don’t need to do anything. Otherwise, right click on the SQL Server Agent service and select Get started in the pop-up menu to open the Start Service window.
This article applies to SQL Server 2008. If you are using a newer version of SQL Server, read Configure SQL Server Agent in SQL Server 2012.
Open SQL Server Management Studio and expand the SQL Server Agent folder.
Close SQL Server Configuration Manager and open SQL Server Management Studio. In SSMS, expand the SQL Server Agent folder.
Create a new SQL Server Agent job
Right click on folder Tasks and select New task in the start menu. Fill in the field Name a unique name for your work (descriptive text helps you better manage your work in the future). In the text box, enter the account you want to take ownership of the task Owner † The task runs with the permissions of this account and can only be modified by members with the Owner or System Administrator role.
After specifying the name and owner, select one of the predefined task categories from the drop-down list. For example, you can select the Database Maintenance category for regular maintenance tasks.
Use a large text field Description to get a detailed description of the job’s purpose. Write it so that someone (including you) can look at it in a few years and understand the purpose of the work.
Finally, make sure the field Including installed. checked.
Go to the SQL Server Agent Operation Steps screen
On the left side of the window New job you see an icon Steps under the heading “Choose a page”. Click this icon to see an empty list of steps.
Add SQL Server Agent Task Steps
Add individual steps to work. Press the button New to create a new task step and you will see the New task step window.
In a text field step name Enter a descriptive name for the step.
Use a drop-down list Database to select the database that the job will work with.
Finally, use a text field Team † Specify the Transact-SQL syntax that corresponds to the desired action for this task step. After completing the command, press the button parse to check the syntax.
After a successful syntax check, press Okay to take a step. Repeat this process as many times as necessary to determine the desired SQL Server Agent job.
Schedule a SQL Server Agent job
Set a work schedule by clicking the icon Scheme in Select a page in a window New job † You see a window New work schedule †
Enter a name for the schedule in the text box Name and select the schedule type – one-time, recurring, Run when SQL Server Agent starts, or Run when processors become idle – from the drop-down list. Use the frequency and duration sections of the window to specify task options. When you’re done, click Okay to close the Schedule window, and Okay to create a job.