The wizard helps you create a maintenance plan that SQL Server Agent can run on a regular basis. With this wizard you can perform routine database administration tasks such as;
• Check database integrity
• Perform index maintenance
• Update database statistics
• Peform database backups
The wizard will create maintenance plans that can be edited in SQL Server Management Studio. Edit maintenance plans to add new tasks or define workflow among the tasks.
1. Open & login to the Microsoft SQL Server Management Studio. Expand the desire database and navigate to the following Management > Maintenance Plans. Right click on Maintenance Plans and select Maintenance Plan Wizard.
2. Click Next.
3. Select the plan properties. Put the name and description. For scheduling, click on Change button.
4. From below screen, you can set the schedule type, frequency (daily, weekly, monthly), time / date start and etc. Click OK.
5. You will route back to the screen from step number 3. Click Next.
6. Select the maintenance tasks. In this example, we are going to select Back Up Database (Full). Click Next.
7. If you have multiple task, you can re-order the sequence on this screen. If only one, just click Next.
8. Define the backup database task – backup expiry, database location, verification and etc. If you choose other maintenance task, the screen will be different as below screen is for database backup respectively. Click Next.
9. Finally, the report (logs, trace files) of the maintenance task. You can set the log to write onto text file or email it to the respective person. Click Next.
10. Click Finish.