The process of returning a SQL Server database to a usable state from a backup, recovering data after accidental deletion, corruption, hardware failure, or disaster.
- Restore scenarios: SQL Server supports multiple restore options, including complete database restore (full recovery), point-in-time restore (requires a transaction log chain), page restore (online repair of specific corrupted pages), and object-level restore using third-party tools.
- Restore sequence: For databases using the Full recovery model, the correct restore order is a full backup, followed by a differential backup (if available), and then all subsequent transaction log backups up to the desired recovery point.
-
Restore testing: DBAs should regularly test restores, not just backups. The only reliable way to verify a backup is to restore it and confirm database consistency using
DBCC CHECKDB. -
Recovery options: SQL Server provides three restore modes:
RECOVERY(brings the database online),NORECOVERY(keeps the database in a restoring state so additional backups can be applied), andSTANDBY(allows read-only access while the log chain remains open). - Recovery Time Objective (RTO): RTO defines the maximum acceptable time to complete a database restore. DBAs should understand the expected restore duration for every critical database.
- Relevant Idera tools: SQL Safe Backup supports Instant Recovery, allowing backup files to be mounted as live databases immediately while the remaining data is restored in the background.
- Related terms: Backup, RTO, RPO, Object Level Recovery, Full Recovery Model.
