Creating SQL Maintenance Plans for SQL Databases

Creating SQL Maintenance Plans for SQL Databases

Complete the Maintenance Plan Wizard steps in this article to create the recommended SQL Maintenance plans for products using SQL Server databases listed below.

  • Full Database Backup
  • Maintenance
  • Transaction Log Backup

Once complete, right-click each plan then select Execute and verify that each runs successfully.

After one week of running, verify the old BAK and TRN files are being deleted correctly.


The steps below are specific to SQL Server 2008 and newer.

Full Database Backup

  1. Log into Microsoft SQL Server Management Studio.
  2. Expand Server Name > Management.
  3. Right-click Maintenance Plans > Maintenance Plan Wizard > Next.
  4. Change the name "MaintenancePlan" to "Full Database Backup".
  5. Click the Change button for Schedule.
  6. Change the Frequency Occurs from Weekly to Daily.
  7. Change 12:00 AM time to the desired time.
  8. Click OK > Next.
  9. Select the following tasks:
  • Back Up Database (Full)
  • Maintenance Cleanup Task
  1. Click Next > Next.
  2. For Databases, select the database for the application > OK.
  3. Make a note of the values for the following fields to be used in step 14 below.
  • Folder
  • Backup file extension (Recommend bak file extension)
  1. Click Next.
  2. Use the values from step 12 above to complete both Folder and File Extension.
  3. Change 4 Weeks to 1 Week.
  4. Click Next > Next > Finish > Close.

Maintenance

  1. Log in to Microsoft SQL Server Management Studio.
  2. Expand Server Name > Management.
  3. Right-click Maintenance Plans > Maintenance Plan Wizard > Next.
  4. Change the name "MaintenancePlan" to "Maintenance".
  5. Click the Change button for Schedule.
  6. Change the Frequency Occurs from Weekly to Daily.
  7. Change 12:00 AM time to the desired time after the Full Backup has completed.
  8. Click OK > Next.
  9. Select the following tasks:
  • Check Database Integrity
  • Rebuild Index
  • Update Statistics
  1. Click Next > Next.
  2. For Databases, select the database for the application then click OK > Next.
  3. For Databases, select the database for the application then click OK > Next.
  4. For Databases, select the database for the application then click OK > Next > Next > Finish > Close.

Transaction Log Backup

  1. Log in to Microsoft SQL Server Management Studio.
  2. Expand Server Name > Management.
  3. Right-click Maintenance Plans > Maintenance Plan Wizard > Next.
  4. Change the name "MaintenancePlan" to "Transaction Log Backup".
  5. Click the Change button for Schedule.
  6. Change the Frequency Occurs from Weekly to Daily.
  7. Select Occurs every 1 hour.
  8. Click OK > Next.
  9. Select the following tasks:
  • Back Up Database (Transaction Log)
  • Maintenance Cleanup Task
  1. Click Next > Next.
  2. For Databases, select the database for the application then click OK.
  3. Make a note of the values for the following fields to be used in step 14 below.
  • Folder
  • Backup file extension (Recommend trn file extension)
  1. Click Next.
  2. Use the values from step 12 above to complete both Folder and File Extension.
  3. Click Next > Next > Finish > Close.

ArticleNumber:

000048627

    • Related Articles

    • Environment performance review recommendations

      Verify the environment is configured per the knowledge article recommendations listed below. Review System Requirements for Web, Application, and SQL Server Hardware including Software articles. Enter the keyword "System Requirements for <product>" ...

    • Creating a Servicing Director test server

      You want to create a Servicing Director test server with a copy of your production server data, and after making all the required changes, use the test server for testing and training. The procedure below is for your local IT or system administrator ...

    • Creating a Servicing Director Custom Report

      To create your own report using data from the Servicing Director database. Reports are created in the program Crystal Reports XI, combined with creating a database view to query your Servicing Director database. Prerequisite: Licensed copy of Crystal ...

    • Creating a New EZTeller Role

      In EZTeller Enterprise, EZTeller Roles are created in Superview. In order to add, edit, or remove EZTeller Roles, your current working group needs to be at the branch level, and your current role’s authority level needs to be equal to or greater than ...

    • Changing the Servicing Director SQL Login Passwords

      You must use the LSCnfg application to change the SQL server login passwords used by the Servicing Director applications to connect to the Servicing SQL server. All Servicing Director applications on all desktops use primarily one SQL server login ...