KBA-01473: Best Practices: SQL Maintenance

Question:

How should I set up my SQL Maintenance Plan?

Answer:

The overall strategy is to use SQL‘s maintenance tool.  No additional investment in backup agent software is required unless you have absolutely no spare hard disk space.  Since SQL backups are restored to test environments prior to upgrades, having SQL backups only on tape is a real downer.So, here‘s the overall plan:

Recovery Model 

Your production databases should be in FULL recovery mode.  Test databases should be in SIMPLE.  The difference is whether or not you can lose all work since your last full backup.  Wouldn‘t you prefer to lose some portion of 3 hours work instead of some portion of 23?

Target

Designate a target folder on a drive that does not hold the main database files (MDF).  If your edition of SQL does not include backup compression, great—in any case, do not enable NTFS compression on the target folder. (Once the backups are large enough, NTFS compression causes failures.)  This target is actually the root of a subtree—SQL will create a folder per database. Have your tape backup software backup this target subtree and not the SQL MDF and LDF files.

Full Backups

We recommend full backups of all databases daily, kept for as many days as you have spare disk space.

• We recommend keeping at least 3 days.
• If you do not have enough space, you can consider demoting to weekly full backups and daily differentials.  You can also move some catalog data into archive databases that need to be backed up less often.
• Switching the number of days is a simple change to your maintenance plan.

Transaction Logs

We recommend every 3 hours from 6AM to 9PM.  Use the same target folder.

• We recommend you keep them for 2-5 days.
• It never makes sense to keep transaction logs older than your oldest full backup.

Performance

Spitfire automatically reviews indexes and statistics and rebuilds (or reorganizes) them as necessary.

Maintenance Plans

1. Create DailyFullBackups plan.
   1. Add Check Database Integrity Task for all databases including indexes.
   2. Add Backup Database Task
      1. Choose Backup Type: Full.
      2. Choose: All databases.
      3. Check: Backup set will expire and specify after 5 days (or your number).
      4. Select: Backup to disk.
         1. Select: Create a backup file for every database.
         2. Check: Create a sub-directory for each database.
         3. Specify the path to your target folder.
         4. Specify the extension BAK.
      5. Optional: Check verify backup.
      6. If offered: Compress Backup.
   3. Add History Cleanup Task
      1. Check to Delete Backup and Restore history.
      2. Check to Delete SQL Server Agent job history.
      3. Check to Delete Maintenance Plan History.
      4. Older than 8 weeks
   4. Add Maintenance Cleanup Task for old Full Backups
      1. Choose: Delete files of type: Backup.
      2. Choose: Search folder and delete based upon extension.
      3. Specify your target folder.
      4. Specify extension BAK.
      5. Check: Include first level subfolders.
      6. Check: Delete based on age.
      7. Specify: a number of days greater than the expire after days set above, often around 7.
   5. Add Maintenance Cleanup Task for old TX Backups
      1. Choose: Delete files of type: Backup.
      2. Choose: Search folder and delete based upon extension.
      3. Specify your target folder.
      4. Specify extension TRN.
      5. Check: Include first level subfolders.
      6. Check: Delete based on age.
      7. Specify: a number of days less than the number in 4.7 above, often around 3.

1. Create TXLOGBackup plan
   1. Add Backup Database Task
      1. Choose Backup Type: Transaction Logs.
      2. Choose: All databases.
      3. Check: Backup set will expire and specify after 3 days (or your number).
      4. Select: Backup to disk
         1. Select: Create a backup file for every database.
         2. Check: Create a sub-directory for each database.
         3. Specify the path to your target folder.
         4. Specify the extension BAK.
      5. Optional: Check verify backup.
      6. If offered: Consider Compress Backup – since these backups are performed while the server is often under load, if your limiting resource is CPU, do not compress; if your limiting resource is I/O, compress.
2. Create MonthlyMaintenance plan
   1. Add Check Database Integrity Task for all databases including indexes.

Additional Comments:

Your SQL maintenance plan is your responsibility, not Spitfire’s.

Hints:

• Be sure SQL Agent is running or your maintenance plan will have no effect.
• Set up SQL Operators and notifications for job alerts.
• Monitor the target folder to verify that files are being written (so you are safe) and deleted (so you won‘t eventually fill the disk).
• Spitfire does not recommend autoshrink for production databases. It leads to fragmentation!
• Be certain that your LDF files are not growing.  If the file grows, then your full backups are not resetting the transaction log rollback pointer. Resolve this or your disk space will eventually become full and SQL corruption may result.  Ask for assistance if necessary, or just set your recovery plan to SIMPLE and live without intra-day recovery.

---

KBA-01473; Last updated: September 19, 2017 at 12:01 pm;
Keywords:  SQL Backups