Until now you have performed our backups through a Query Window in SQL Server Management Studio. Of course, in production systems, backups need to be scheduled to run automatically, without any user input. This can be done using any scheduling software. SQL Server has a built-in scheduler as part of the SQL Server Agent Service. With SQL Server Agent Jobs, it is possible to define T-SQL batches to run on given schedules. This feature provides the functionality you need to automate your recovery strategy.
Have a look at another easy way to schedule backups by using a wizard. The SQL Maintenance Plan Wizard is a tool that helps perform standard tasks, like Backup, Index Defragmentation, and Check Database Integrity. It relies on the SQL Server Agent Services because it schedules jobs that are started by SQL Server Agent at requested times. Therefore, the SQL Server Agent Service has to be running at all times. You can configure the SQL Agent Service using SQL Server Configuration Manager. Configure it to start up automatically and start it if necessary by using the following steps:
Starting SQL Server Agent Automatically
Open SQL Server Configuration Manager using the steps provided in the previous section.
In the lefthand pane, select SQL Server 2005 Services. In the righthand pane, right-click the SQL Server Agent icon and select Properties from the context menu.
On the Service tab, find the Start Mode item and change its setting to Automatic. Click the Apply button, then click the OK button.
You can now start the Agent by right-clicking the SQL Server Agent icon and selecting Start from the context menu.
Using the SQL Maintenance Plan Wizard
Open Object Explorer in SQL Server Management Studio by selecting Object Explorer in the View menu or by pressing the F8 key.
In the tree view shown, expand your SQL Server instance, open the Management folder, and right-click the Maintenance Plans folder. Select Maintenance Plan Wizard.
A welcome page apears. Click the Next button.
Type a name for your Maintenance Plan. Use Daily AdventureWorks Backup for this example and click the Next button.
Select Back Up Database (Full) and click the Next button twice.
Select AdventureWorks at the Databases dropdown list, and click the OK button.
Select the Create A Backup File For Every Database option, check the Create A Sub-Directory For Each Database checkbox, and specify a file path for the folder where your backups will be stored. Click the Next button.
Click the Change button to create your schedule. When you have finished, click the OK button, and then click the Next button.
On this page you can define whether a report should be written to a directory and whether an e-mail notification should be sent. Make your selections and click the Next button.
Click the Finish button.
Database Maintenance Plans can be changed through SQL Server Management Studio. Simply navigate in Object Explorer to Maintenance Plans, which is a subfolder of Management, and right-click the maintenance plan you want to change. The history log can also be opened through this menu.