How to Configure Maintenance Plan Wizard in MSSQL

Share this article :

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.

Maintenance Plan Wizard

2. Click Next.

SQL Server Maintenance Plan Wizard Welcome

3. Select the plan properties. Put the name and description. For scheduling, click on Change button.

Select Plan Properties

4. From below screen, you can set the schedule type, frequency (daily, weekly, monthly), time / date start and etc. Click OK.

New Job Schedule

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.

Select Maintenance Tasks

7. If you have multiple task, you can re-order the sequence on this screen. If only one, just click Next.

Select Maintenance Task Order

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.

Define Back Up Database (Full) Task

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.

Select Report Options

10. Click Finish.

Complete the Wizard

Related posts

MSSQL Server Database Backup & Restore vis SQL Management Studio
Add & Remove SQL Server tempdb
Enable & Disable Auto Shrink in MSSQL Server
SAP on SQL Server Whitepaper
© 2018 ITsiti. All Rights Reserved
Powered by KEEM