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