Writing a Disaster Recovery Runbook: RPO, RTO and a Restore Test

A backup file cannot decide who restores it or when the application can reopen. A disaster recovery runbook connects the recovery objective to the people, files, scripts, and checks needed to meet it.

Start the Disaster Recovery Runbook With RPO and RTO

Recovery point objective, or RPO, describes the acceptable amount of lost work expressed as time. Recovery time objective, or RTO, describes the acceptable time to restore the service. Agree on the affected service and the incident starting point. Database recovery alone does not account for application routing, authentication, downstream dependencies, or business validation.

Specify which data and operations are covered. A payment process can have a different objective from a reporting copy. Identify who can authorize a recovery that loses committed work and who can accept reopening a degraded service. I put those decisions near the start of the runbook because they determine the permitted recovery sequence.

Write a target and a measured result in separate fields. A desired recovery time is not evidence that the current restore path achieves it. Include detection, escalation, access, transfer, restore, consistency checks, and application acceptance in the timing exercise. A stopwatch cannot negotiate a better objective after the outage begins.

Inventory Each Database and Its Backup Chain

The backup history helps locate recent backups, but the history is not the backup itself. List the recovery model, encryption requirements, media location, access method, and responsible owner for every required database. Preserve a copy of this inventory outside the failed instance's storage boundary.

SELECT d.name,d.recovery_model_desc,b.type,b.backup_start_date,
       b.backup_finish_date,b.first_lsn,b.last_lsn,
       b.database_backup_lsn,m.family_sequence_number,
       m.physical_device_name
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
    ON b.database_name=d.name
LEFT JOIN msdb.dbo.backupmediafamily AS m
    ON m.media_set_id=b.media_set_id
WHERE d.database_id>4
ORDER BY d.name,b.backup_finish_date DESC,m.family_sequence_number;

Document the selected full backup, any matching differential, and every required log backup in restore order. Capture all media families for striped backups. A physical_device_name can describe a device or an old path, so verify the actual accessible media. Confirm that backup encryption certificates and their protected private keys can be recovered on the destination.

History cleanup, renamed databases, and copied files can complicate interpretation. Check backup headers and logical file names on the recovery server. A recent completion timestamp indicates a backup operation finished, not that the complete chain remains accessible and restorable. The disaster recovery runbook must identify these dependencies explicitly rather than assume they survived with the production server.

Write the Restore Sequence Before an Incident

Use a reviewed example with exact destination paths and database names. The following sequence assumes a compatible full backup, one required log backup, and verified logical file names. It restores a separate test database and does not overwrite an existing one.

RESTORE FILELISTONLY
FROM DISK=N'C:\RecoveryMedia\ServiceDb_full.bak';
RESTORE DATABASE RecoveryTest
FROM DISK=N'C:\RecoveryMedia\ServiceDb_full.bak'
WITH FILE=1,MOVE N'ServiceDb_Data' TO N'C:\RecoveryData\RecoveryTest.mdf',
     MOVE N'ServiceDb_Log' TO N'C:\RecoveryData\RecoveryTest_log.ldf',
     NORECOVERY,CHECKSUM;
RESTORE LOG RecoveryTest
FROM DISK=N'C:\RecoveryMedia\ServiceDb_log.trn'
WITH FILE=1,RECOVERY,CHECKSUM;
DBCC CHECKDB(N'RecoveryTest') WITH NO_INFOMSGS;

For a point-in-time objective, document the approved STOPAT boundary and the chain that reaches it. Tail-log recovery depends on the failure and the availability of a usable log; do not promise it for every incident. Decide when a restored copy is preferable to recovery over the original files. Record storage capacity and destination service-account permissions as prerequisites.

I keep scripts and their prerequisites together so a responder can identify a missing condition before running the first restore. Test the current scripts after changes to file layout, encryption, engine version, or the backup process. A technically valid command with the wrong logical file name still stops the recovery.

Restore Authentication and Instance Dependencies

A user database does not bring every instance-level dependency with it. Inventory SQL logins and their SIDs, Windows principals, Agent jobs, operators, credentials, linked servers, configuration, and relevant permissions. Keep secrets in the approved protected recovery store rather than placing them in a broadly readable runbook.

SELECT name,type_desc,sid,is_disabled,default_database_name
FROM sys.server_principals
WHERE type IN ('S','U','G') AND name NOT LIKE N'##%';
SELECT name,type_desc,sid,authentication_type_desc
FROM RecoveryTest.sys.database_principals
WHERE principal_id>4 AND type IN ('S','U','G');
SELECT name,enabled,SUSER_SNAME(owner_sid) AS JobOwner
FROM msdb.dbo.sysjobs;
SELECT name,product,provider,data_source,is_linked
FROM sys.servers WHERE server_id>0;

For SQL logins, preserve the expected SID when recreating the approved login from securely retained information. For Windows identities, verify the directory identity and destination access. Reconcile database-user mappings deliberately; contained users have a different authentication scope. Do not remap every user to one convenient administrator just to make the first connection succeed.

USE RecoveryTest;
GO
ALTER USER [RecoveryAppUser] WITH LOGIN=[RecoveryAppLogin];
GO

That mapping example requires the reviewed user and login to exist and to represent the intended identity. Recreate jobs with the correct owner, schedules, proxies, and notification paths. Restore linked-server mappings with least privilege and verify encryption and certificates for the destination provider. Dependency configuration needs a functioning access test, not merely a successful creation statement.

Assign Roles and an Escalation Path

Name the incident coordinator, restore operator, security contact, application validator, and business approver by role with current contact details in the protected operating copy. Assign a backup for each role. State who can approve data loss, stop the restore, or choose an alternate destination when the original plan cannot proceed.

Include an incident log template containing timestamps, decisions, selected media, executed steps, failures, and validation outcomes. Record where responders obtain elevated access and how that access is audited. Which dependency requires another team to act before the application reconnects? Practice that handoff instead of discovering it during the emergency.

Exercise the Whole Disaster Recovery Runbook Quarterly

A quarterly drill should use actual retained backups and an isolated recovery destination. Measure the complete service recovery, including authentication and representative application operations. Record the achieved recovery point and the point where the service was accepted. Keep test routing separate so restored jobs cannot send production notifications or change external systems.

Run consistency checks and verify meaningful business invariants. A database can be online while a required job, login, or external dependency is unusable. Capture failures and update the reviewed runbook, then repeat the failed step. A calendar entry marked completed cannot replace evidence of a completed restore.

Keep the Disaster Recovery Runbook Package Current

Maintain the runbook with the backup process, protected access instructions, dependency scripts, validation checklist, and the latest measured drill result. Review it after material changes and when responsible roles change. Confirm that responders can access the package from the alternate location during the assumed failure.

A disaster recovery runbook is useful when another qualified responder can execute it and explain its limits. Keep the objectives, supported scenarios, untested conditions, and escalation decisions visible. The final acceptance belongs to the recovered service and its owners, not only to the database restore command.

Related reading on this blog: Check Backup Reliability and Undo Human Errors in SQL Server: SQL in Sixty Seconds #109: Point in Time Restore.

A recovery runbook is not a list of backup files, it is a tested route from an incident to an accepted service.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.