Note : don’t login using ‘sa’, please login using windows authentification for creating schedule/maintenance plan
Creating a SQL Server 2005 Maintenance Plan
If you are going to use new Maintenance Plan features, I strongly recommend installing SQL Server 2005 Service Pack 1 first. Among the new features and improvements that are included in SQL Server 2005 Service Pack 1, there is a fix for the previously existing discrepancy between Back up database task, which allowed storing database backups in separate folders, and Maintenance Cleanup Task, which could not delete backup files from those subfolders.
In order to create a new maintenance plan in Management Studio, first connect to the target server using Windows Authentication, then right-click on the Maintenance Plan folder in Object Explorer, select New Maintenance Plan, and enter the plan name. As a result, a Maintenance Plan design panel will appear on the right, and a toolbox with available Maintenance Plan Tasks will be displayed on the left. Click on the Connection button to verify that the current connection uses Windows Authentication (recommended by Microsoft). Currently connected Windows user will become the owner of the job created by this maintenance plan.
The first step in creating a database or transaction log backup is to drag and drop Back up database task from the toolbox to the design panel. Then double-click on that item to set the following necessary properties:
- Figure 1: Database selection window.
- Backup type: choose Full.
- Back up to — choose Disk.Make sure that the Create a backup file for every database option is selected and the Create a sub-directory for each database box is checked.
- You can use the default destination folder or specify your own. For this example, the network folder \server02dbbackupsql2005 has been selected.
- Backup file extension — make sure that its value is bak without a leading dot.
- Check the Verify backup integrity box.