Database Operations and Data Maintenance
Domain 4 covers backup and recovery design, routine maintenance tasks, and elementary SQL. The exam favours questions about which backup chain restores to a given moment, and about the difference between similar SQL statements.
Backup and recovery
Full backup copies everything; differential copies changes since the last full; incremental copies changes since the last backup of any type; transaction log backups capture every committed change and enable point-in-time recovery. In the simple recovery model the log is truncated at checkpoint, so point-in-time restore is impossible. Restore order is always full, then latest differential, then each log in sequence, then a tail-log backup if the database is still available. Verify by restoring onto a separate instance.
Routine maintenance
Run consistency checks (for example DBCC CHECKDB), rebuild or reorganise fragmented indexes, update statistics, monitor data and log file growth and autogrow settings, review scheduled jobs and their history, review accounts and permissions, archive aged data, and keep an operations log of every maintenance action.
Basic SQL
DML: SELECT with WHERE, JOIN, GROUP BY, HAVING, ORDER BY; INSERT, UPDATE and DELETE - the last two always with a WHERE clause. DDL: CREATE, ALTER, DROP. DCL: GRANT, REVOKE. Wrap multi-statement changes in a transaction with explicit COMMIT or ROLLBACK, and test the WHERE clause as a SELECT first.
Worked example
Full backup Sunday 01:00, differential Monday 01:00, log backup hourly, failure Tuesday 10:20. Restore path: full backup, then the Monday differential, then logs for Monday 02:00 through Tuesday 10:00, then the tail log. Data loss is limited to changes after 10:00 - 20 minutes, so RPO is about 20 minutes, not zero. The differential is used because it is the latest one after the full; earlier differentials are not applied.
Traps
- TRUNCATE removes all rows without a WHERE clause, resets identity values and is not row-by-row recoverable in many engines.
- Mixing up differential (since last full) and incremental (since last any backup).
- Assuming a full backup protects data created after it - without logs it does not.
- Restoring logs out of sequence or skipping the differential.
- Percentage autogrow on a busy file causing repeated small growth stalls.