| You can automate your data transfer using SQL Server Agent and SSIS. You can use Agent alone to schedule the various data movement techniques described above. SSIS can be used as well to graphically link various tasks and take advantage of the workflow engine that is built into it. In this section, we will walk through setting up a step for a backup procedure and then scheduling it. Scheduling a Backup Operation Using SQL Server Agent 1. | In SQL Server Management Studio, open the SQL Server Agent node in Object Explorer and select the Jobs folder.
| 2. | Right-click the Jobs folder and select New Job from the context menu to open the New Job dialog box, shown below. This dialog box, like many SQL Server 2005 features, has been updated.
 | 3. | Next, set up the Steps required to execute the backup. Select the Steps page from the Select A Page pane to bring up a blank list of job steps. Click the New button to open up the New Job Step dialog box. Accept the default type, which is Transact-SQL Script (T-SQL) and the default database which is master. In the Command textbox, enter the backup script that we developed in the earlier section titled "Full Backup Only." This script is also included in the sample files as FullBackupScript.sql. Enter a name for the step, and click the OK button to close the New Job Step dialog box.
| 4. | After the step or steps have been created, you will need to set the schedule. Go to the Schedules page and click the New button. This will open the New Job Schedule dialog box, shown below. In this dialog box, you can set the schedule to suit your needs. Choose the settings you want and click the OK button to close the New Job Schedule dialog box.
 Tip  | You can also click the Pick button on the Schedules page and select an existing schedule. If you have multiple jobs that need to run on the same or similar schedules, you can pick the schedule from a list of existing schedules on the server. |
| 5. | To finish, use the Alerts and Notifications pages to set up alerts or notifications to let you know whether the job or job steps were successful. Most commonly, you will use Notifications for this purpose. The Notifications page will also allow you to automatically delete the job when it runs if you want to run the job once only. Make appropriate Alerts and Notifications changes, and then click the OK button to close the New Job Schedule dialog box.
|
You can also schedule SSIS packages with SQL Server Agent. Agent jobs support deployed packages directly. (Deployed packages are packages that can be installed on a different server.) You can pick SQL Server Integration Services Package as the type in the New Job Step dialog box (shown below) and select the various package options, including configurations and execution options.  SQL Server Agent is a great tool for scheduling various tasks and jobs. However, SSIS can be used to create multiple workflows with various data sources and destinations. Using both Agent and SSIS together provides a powerful combination for moving data. |
