Maintenance Plan Wizard in SQL Server Management Studio, SQ: Server 2008

The Maintenance Plan Wizard creates a maintenance plan that Microsoft SQL Server Agent can run on a regular basis. This allows you to perform various database administration tasks, including backups, run database integrity checks, or update database statistics at specified intervals.

To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Object Explorer only displays the Maintenance Plans node for users who are members of the sysadmin fixed server role.

To start the Maintenance Plan Wizard, do the followings:

• Expand the server.
• Expand the Management folder.
• Right-click Maintenance Plans and select Maintenance Plan Wizard. This launches the wizard and you can now step through and create a plan customized to meet your maintenance requirements.

Database Maintenance Plan for Microsoft Dynamics GP on Microsoft SQL Server 2008

For setting up a database maintenance plan on Microsoft SQL Server 2008, for Microsoft Dynamics GP.

You can set up a weekly maintenance plan for databases in Microsoft Dynamics GP.  However, the frequency that you select depends on the environment and on how much activity occurs within the databases. We recommend that you examine the output for warning signs of possible problems after you set up a maintenance plan.

To set up a database maintenance follow these steps:

1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
2. In the Connect to Server window, click SQL Server Authentication in the Authentication list, type the sa user name and password, and then click Connect.
3. In the Object Explorer area, double-click Management, right-click Maintenance Plans, and then click Maintenance Plan Wizard.
4. On the Maintenance Plan Wizard starting page, click Next.
5. In the Select Plan Properties window, type a name for the maintenance plan in the Name field, type a descriptive comment in the Description field, and then select Single schedule for the entire plan or No schedule.
6. In the Job Schedule Properties window, click the Change button for Schedule, set up a schedule type of "Recurring as Enabled" that has the frequency parameters that meet your maintenance needs, and then click OK.
7. Click Next to move to the Select Maintenance Tasks window.
8. In the Select Maintenance Tasks window, click to select the check box for each of the following tasks:
   • Check Database Integrity
   • Reorganize Index
   • Update Statistics
   • Back Up Database (Full)
9. Click Next.
10. In the Select Maintenance Task Order window, accept the default order, and then click Next.
11. In the Databases list, click to select the check box for each Microsoft Dynamics database. To do this, select These databases, click the check box next to each desired database, click OK, and then click Next. Use the default setup for the remaining options.
12. In the Define Reorganize Index Task window, click to select the check box for each Microsoft Dynamics database. To do this, select These databases, click the check box next to each desired database, click OK, and then click Next. Use the default setup for the remaining options.
13. In the Define Update Statistics Task window, click to select the check box for each Microsoft Dynamics database. To do this, select These databases, click the check box next to each desired database, click OK, and then click Next. Use the default setup for the remaining options.
14. In the Define Back Up Database (Full) Task window, click to select the check box for each Microsoft Dynamics database. To do this, select These databases, click the check box next to each desired database, click OK, review the remaining backup parameters, and then click Next.
15. In the Select Report Options window, accept the default entries, and then click Next.
16. In the Complete the Wizard window, click Finish. After the maintenance plan is created, click Close.
17. If you want to configure the maintenance plan to delete backup files that are older than a specific date, follow these steps:
    • In the Object Explorer area, double-click Management, double-click Maintenance Plans, right-click the maintenance plan that you created, and then click Modify.
    • In the Toolbox area at the bottom of the Object Explorer, drag Maintenance Cleanup Task to the right pane.
    • In the right pane, double-click Maintenance Cleanup Task.
    • In the File Location area, click Delete Specific File.
    • In the File name field, type the path of the location where the backup file is located.
      Note If you have backup files for each database and these files are located in separate folders, you must create a Maintenance Plan Cleanup Task for each database folder.
    • To delete full backup files, type BAK in the File Extension field. To delete Transaction Log backup files, type TRN in the File Extension field. To delete both types of files, you must create a Maintenance Plan Cleanup Task for each type of file.
    • In the File Age area, click the appropriate option to set the schedule according to which the files will be deleted, and then click OK.
    • In the right pane, click Back Up Database (Full). A green arrow now appears at the bottom of the window.
    • Drag the green arrow to Maintenance Cleanup Task. A green arrow will connect Back Up Database (Full) to Maintenance Cleanup Task.
    • Click File, and then click Save Selected Item.