ERP Database Maintenance Plan Best Practices

Backup Strategy and Recovery Point Objectives

ERP database backup strategy must align with business recovery objectives. Full backups provide the baseline, differential backups capture changes since the last full, and transaction log backups enable point-in-time recovery. For ERP systems processing financial transactions, a 15-minute RPO (recovery point objective) requires transaction log backups every 15 minutes.

The backup chain must be tested regularly through restore drills--an untested backup is not a backup. Compression reduces backup sizes by 70-80% for ERP databases and should always be enabled.

• Schedule full backups nightly during the maintenance window with COMPRESSION and CHECKSUM options enabled
• Run differential backups every 4-6 hours to reduce restore time versus relying solely on transaction log backups
• Configure transaction log backups every 15 minutes for RPO compliance; more frequently for high-transaction ERP environments
• Implement 3-2-1 backup retention: 3 copies, 2 different media types, 1 offsite (cloud storage or tape rotation)
• Perform quarterly restore drills to a test server to validate backup integrity and document the actual RTO achieved

Integrity Checks and Corruption Detection

DBCC CHECKDB is the only way to detect database corruption before it causes data loss or application errors. Corruption can result from storage hardware failures, firmware bugs, driver issues, or unexpected shutdowns. For large ERP databases where full CHECKDB takes hours, a phased approach runs CHECKDB WITH PHYSICAL_ONLY daily (fast check for physical corruption) and full CHECKDB weekly.

DBCC CHECKTABLE on critical ERP tables (inventory, financial, orders) can run during shorter maintenance windows when a full CHECKDB cannot complete.

• Run DBCC CHECKDB WITH PHYSICAL_ONLY daily on ERP databases as a fast corruption scan completing in minutes
• Schedule full DBCC CHECKDB weekly during the longest maintenance window with results logged to a history table
• Run DBCC CHECKTABLE on the 20 most critical ERP tables (inventory transactions, GL journal, sales orders) daily
• Enable page verification with CHECKSUM (database option PAGE_VERIFY) to detect corruption at the storage I/O level
• Configure SQL Server Agent alerts on DBCC errors (severity 21-25) to notify DBAs immediately when corruption is detected

Automated Maintenance Job Scheduling

All maintenance tasks should be automated through SQL Server Agent jobs with monitoring and alerting. The Ola Hallengren maintenance solution provides production-tested stored procedures for index maintenance, statistics updates, integrity checks, and backup management that handle edge cases and logging. Schedule maintenance jobs to avoid conflicts with ERP batch processing windows (MRP runs, financial close processes, EDI processing) and monitor job durations to detect when maintenance is taking longer than expected--a sign of growing database size or degrading hardware.

• Deploy Ola Hallengren maintenance solution for index optimization, statistics updates, integrity checks, and backup management
• Schedule index maintenance Sunday nights, statistics updates after index maintenance, and integrity checks on alternating nights
• Configure SQL Server Agent job alerts that notify on failure, duration exceeding 2x average, or unexpected completion times
• Coordinate maintenance schedules with ERP batch processing: never overlap index rebuilds with MRP runs or financial posting jobs
• Log maintenance job results to a central monitoring table and trend durations weekly to detect database growth impacting maintenance windows