Portrait of Mihaly KerteszMihaly KerteszSQL Server consulting

sql server hub / recovery guide

SQL Server recovery guide

Recovery means restoring the right database to the right point, fast enough for the business to keep working.

Use this guide to validate restore paths, point-in-time choices, tail-log decisions, restore history, dependencies, runbook quality, and recovery drill output before the incident is live.

restore sequence

tail log

recovery drill

Guide

Guide~10 min readUpdated 13 Aug 2026

Share

LinkedInXEmail

Use this when

Recovery needs to be validated, not discussed

1

Agree the recovery point before building the restore sequence.

2

Apply full, differential, and log backups in the right state and order.

3

Check tail-log backup decisions before changing the damaged database.

4

Validate logins, jobs, permissions, linked servers, apps, and timing after restore.

1 / Recovery result

What SQL Server recovery must prove

Recovery must show that the right database can be restored to the right point, on a trusted target, with required dependencies working, inside the business window.

That means the work is bigger than a backup file. It includes the restore sequence, target recovery point, tail-log choice, file paths, permissions, jobs, logins, applications, and validation results.

LayerWhat it answers
TargetWhich database state is needed and how much data loss is acceptable.
SequenceWhich backups must be restored, in which order, and with which recovery state.
DependenciesWhich jobs, logins, permissions, linked servers, certificates, and apps must work afterward.
Drill outputWhether timing and validation match the promised recovery result.

2 / Target

Set the recovery target

Decide the target before touching restore commands: database name, point in time, acceptable data loss, target host, target file paths, and whether the original database should stay untouched.

Recovery model and recent backup coverage show whether the target is realistic. They also help avoid promising point-in-time recovery when the log backups do not support it.

Use the backup RPO and RTO calculator to turn measured backup sizes and restore throughput into a transparent planning estimate before the drill.

Shows recovery model, state, read-only flag, and latest full, differential, and log backup for each database.

SQL

Recovery model and latest backup coverage

T-SQL · 23 lines

WITH LastBackups AS (    SELECT        bs.database_name,        bs.type,        MAX(bs.backup_finish_date) AS last_backup_finish_date    FROM msdb.dbo.backupset AS bs    WHERE bs.type IN ('D', 'I', 'L')    GROUP BY bs.database_name, bs.type)SELECT    d.name AS database_name,    d.recovery_model_desc,    d.state_desc,    d.is_read_only,    MAX(CASE WHEN lb.type = 'D' THEN lb.last_backup_finish_date END) AS last_full_backup,    MAX(CASE WHEN lb.type = 'I' THEN lb.last_backup_finish_date END) AS last_differential_backup,    MAX(CASE WHEN lb.type = 'L' THEN lb.last_backup_finish_date END) AS last_log_backupFROM sys.databases AS dLEFT JOIN LastBackups AS lb    ON lb.database_name = d.nameWHERE d.name <> N'tempdb'GROUP BY d.name, d.recovery_model_desc, d.state_desc, d.is_read_onlyORDER BY d.name;
Review before runningT-SQLUTF-823 lines

recovery_model_desc tells you whether log backups are part of the recovery posture. last_full_backup is the baseline for any restore plan. last_log_backup matters for point-in-time recovery in full or bulk-logged recovery model. If backup coverage does not fit the target, reset expectations before restoring.

3 / Restore path

Choose the restore path

Hardware loss, storage failure, corruption, ransomware, bad data change, failed release, and accidental deletion can all require different restore paths. For storage incidents, compare SQL Server errors 823, 824, and 825 before deciding whether the source database can still be trusted. The newest backup may not be the safest target.

Choose whether this is a full recovery, point-in-time restore, alternate-host recovery, staged validation restore, or partial recovery workflow.

Restore pathUse it when
Standard restore chainThe target is the newest usable database state and the backup chain is trusted.
Point-in-time restoreThe target is before a bad write, deployment, deletion, or corruption point.
Alternate-host restoreThe source host, storage, security state, or production database should not be touched yet.
Staged validation restoreThe restored state must be checked before users or applications are pointed at it.

4 / Sequence

Build the restore sequence

Build the sequence from backup history before running restore commands. A common pattern is full backup with `NORECOVERY`, optional differential with `NORECOVERY`, log backups in order, then the final step with `RECOVERY`.

Keep the database in restoring state until every required backup has been applied. Using `WITH RECOVERY` too early ends the restore chain.

Lists full, differential, and log backups for a selected database with LSNs and physical device names.

SQL

Required backup chain for a selected database

T-SQL · 28 lines

DECLARE @database_name sysname = N'YourDatabase';DECLARE @restore_start_time datetime2 = DATEADD(day, -7, SYSDATETIME()); SELECT    bs.database_name,    CASE bs.type        WHEN 'D' THEN 'Full'        WHEN 'I' THEN 'Differential'        WHEN 'L' THEN 'Log'        ELSE bs.type    END AS backup_type,    bs.backup_start_date,    bs.backup_finish_date,    bs.first_lsn,    bs.last_lsn,    bs.database_backup_lsn,    bs.checkpoint_lsn,    bs.is_copy_only,    bmf.physical_device_nameFROM msdb.dbo.backupset AS bsLEFT JOIN msdb.dbo.backupmediafamily AS bmf    ON bmf.media_set_id = bs.media_set_idWHERE bs.database_name = @database_name  AND bs.backup_finish_date >= @restore_start_time  AND bs.type IN ('D', 'I', 'L')ORDER BY    bs.backup_finish_date,    CASE bs.type WHEN 'D' THEN 1 WHEN 'I' THEN 2 WHEN 'L' THEN 3 ELSE 4 END;
Review before runningT-SQLUTF-828 lines

backup_finish_date helps order the restore inputs. backup_type shows where full, differential, and log backups fit. physical_device_name is the file or device that must still be accessible. Use this to build the restore sequence before writing restore commands.

Non-executing template for full, differential, log, NORECOVERY, and final RECOVERY steps.

SQL

Restore sequence template

T-SQL · 20 lines

-- Template only. Replace database names, file paths, STOPAT, and MOVE paths.-- Keep NORECOVERY until every required backup has been applied. RESTORE DATABASE YourDatabase_RestoreFROM DISK = N'X:\SqlBackups\YourDatabase_full.bak'WITH NORECOVERY,     MOVE N'YourDatabase' TO N'D:\SQLData\YourDatabase_Restore.mdf',     MOVE N'YourDatabase_log' TO N'L:\SQLLogs\YourDatabase_Restore.ldf'; RESTORE DATABASE YourDatabase_RestoreFROM DISK = N'X:\SqlBackups\YourDatabase_diff.bak'WITH NORECOVERY; RESTORE LOG YourDatabase_RestoreFROM DISK = N'X:\SqlBackups\YourDatabase_log_001.trn'WITH NORECOVERY; RESTORE LOG YourDatabase_RestoreFROM DISK = N'X:\SqlBackups\YourDatabase_log_002.trn'WITH RECOVERY;
Review before runningT-SQLUTF-820 lines

NORECOVERY keeps the database ready for the next backup in the chain. RECOVERY should appear only on the final restore step. MOVE paths must match the target server layout. Template commands need the actual backup chain before use.

Non-executing template showing STOPAT on the log restore that contains the target time.

SQL

Point-in-time restore template

T-SQL · 11 lines

-- Template only. Choose the target time from the incident timeline.-- STOPAT applies to the log restore where the target time falls. RESTORE DATABASE YourDatabase_RestoreFROM DISK = N'X:\SqlBackups\YourDatabase_full.bak'WITH NORECOVERY; RESTORE LOG YourDatabase_RestoreFROM DISK = N'X:\SqlBackups\YourDatabase_log_001.trn'WITH STOPAT = '2026-05-20T14:35:00',     RECOVERY;
Review before runningT-SQLUTF-811 lines

STOPAT must match the incident timeline and business target. The log backup must contain the target time. Do not assume the newest backup is the right target for bad data or corruption. Validate the restored state before replacing production use.

5 / Tail log

Check tail-log backup decisions

In full or bulk-logged recovery model incidents, the tail-log decision matters. A tail-log backup can capture log records not yet backed up, but touching the damaged source database may be unsafe or impossible in some incidents.

Decide deliberately. The answer depends on database state, incident type, storage trust, business target, and whether the source can be accessed safely.

Shows recent log backup LSNs for a selected database, including the previous last LSN for continuity review.

SQL

Recent log backup continuity

T-SQL · 18 lines

DECLARE @database_name sysname = N'YourDatabase'; SELECT TOP (100)    bs.database_name,    bs.backup_start_date,    bs.backup_finish_date,    bs.first_lsn,    bs.last_lsn,    bs.database_backup_lsn,    LAG(bs.last_lsn) OVER (        PARTITION BY bs.database_name        ORDER BY bs.backup_start_date    ) AS previous_last_lsn,    bs.is_copy_onlyFROM msdb.dbo.backupset AS bsWHERE bs.database_name = @database_name  AND bs.type = 'L'ORDER BY bs.backup_start_date;
Review before runningT-SQLUTF-818 lines

first_lsn and last_lsn show each log backup range. previous_last_lsn helps review continuity. Copy-only or unusual backups need interpretation before restore planning. If continuity is unclear, validate with a restore drill before promising recovery.

Conditional incident template for capturing the tail of the log before restore work.

SQL

Tail-log backup template

T-SQL · 7 lines

-- Conditional incident template.-- Use only after deciding the damaged source database should be touched. BACKUP LOG YourDatabaseTO DISK = N'X:\SqlBackups\YourDatabase_tail_20260520.trn'WITH NORECOVERY,     CHECKSUM;
Review before runningT-SQLUTF-87 lines

Use only after deciding the source database should be touched. WITH NORECOVERY leaves the database in restoring state. This can be wrong for incidents where the source is untrusted or unavailable. Record the decision in the runbook output.

6 / History

Validate backup and restore history

Restore history shows what has actually been restored on the instance. Backup media history shows where the required backup files were written and whether the restore inputs can be found.

`msdb` history can be incomplete if history cleanup has removed old rows, but it is still the first useful place to check restore behavior.

Reads restore operations with source backup timing, restore type, recovery flag, and destination file path.

SQL

Recent restore history

T-SQL · 22 lines

SELECT TOP (100)    rh.destination_database_name,    rh.restore_date,    rh.restore_type,    rh.replace,    rh.recovery,    bs.database_name AS source_database_name,    bs.backup_start_date,    bs.backup_finish_date,    CASE bs.type        WHEN 'D' THEN 'Full'        WHEN 'I' THEN 'Differential'        WHEN 'L' THEN 'Log'        ELSE bs.type    END AS backup_type,    rf.destination_phys_nameFROM msdb.dbo.restorehistory AS rhLEFT JOIN msdb.dbo.backupset AS bs    ON bs.backup_set_id = rh.backup_set_idLEFT JOIN msdb.dbo.restorefile AS rf    ON rf.restore_history_id = rh.restore_history_idORDER BY rh.restore_date DESC;
Review before runningT-SQLUTF-822 lines

restore_date shows whether recovery drills or real restores happened recently. destination_database_name distinguishes test restores from production restores. restore_type and recovery help explain the restore sequence. No restore history means the runbook may still be untested on this instance.

Finds physical device names and backup metadata for a selected database.

SQL

Backup file and device lookup

T-SQL · 23 lines

DECLARE @database_name sysname = N'YourDatabase'; SELECT TOP (100)    bs.backup_set_id,    bs.database_name,    CASE bs.type        WHEN 'D' THEN 'Full'        WHEN 'I' THEN 'Differential'        WHEN 'L' THEN 'Log'        ELSE bs.type    END AS backup_type,    bs.backup_finish_date,    bs.first_lsn,    bs.last_lsn,    bs.is_copy_only,    bs.has_backup_checksums,    bmf.physical_device_name,    bmf.device_typeFROM msdb.dbo.backupset AS bsJOIN msdb.dbo.backupmediafamily AS bmf    ON bmf.media_set_id = bs.media_set_idWHERE bs.database_name = @database_nameORDER BY bs.backup_finish_date DESC;
Review before runningT-SQLUTF-823 lines

physical_device_name is the path or device the restore process needs. has_backup_checksums helps judge backup validation options. is_copy_only needs attention when selecting restore inputs. Confirm files still exist and SQL Server can read them.

Shows restore records with source backup size and source backup duration where msdb has the backup set.

SQL

Restore history and source backup timing

T-SQL · 14 lines

SELECT TOP (100)    rh.destination_database_name,    rh.restore_date,    rh.restore_type,    bs.database_name AS source_database_name,    bs.backup_start_date,    bs.backup_finish_date,    DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) AS source_backup_duration_seconds,    CONVERT(decimal(18,2), bs.backup_size / 1024.0 / 1024.0) AS backup_size_mb,    CONVERT(decimal(18,2), bs.compressed_backup_size / 1024.0 / 1024.0) AS compressed_size_mbFROM msdb.dbo.restorehistory AS rhLEFT JOIN msdb.dbo.backupset AS bs    ON bs.backup_set_id = rh.backup_set_idORDER BY rh.restore_date DESC;
Review before runningT-SQLUTF-814 lines

restore_date shows when restore work happened. backup_size_mb helps compare drill size with production size. source_backup_duration_seconds is not restore duration, but it adds context. Measure real restore duration through drill jobs or runbook timing.

7 / Dependencies

Check dependencies after restore

A restored database is not necessarily a usable service. Jobs, logins, permissions, credentials, linked servers, certificates, file paths, application connection strings, and downstream systems may all need validation.

Dependency checks should be part of the runbook, not a surprise after the database is online.

Checks common server and database dependencies that often affect post-restore usability.

SQL

Dependency inventory starting point

T-SQL · 40 lines

DECLARE @database_name sysname = N'YourDatabase'; SELECT    N'SQL Agent jobs' AS dependency_type,    COUNT(*) AS item_count,    STRING_AGG(CONVERT(nvarchar(max), j.name), N'; ') AS sample_itemsFROM msdb.dbo.sysjobs AS jWHERE j.enabled = 1 UNION ALL SELECT    N'Linked servers' AS dependency_type,    COUNT(*) AS item_count,    STRING_AGG(CONVERT(nvarchar(max), s.name), N'; ') AS sample_itemsFROM sys.servers AS sWHERE s.is_linked = 1 UNION ALL SELECT    N'Credentials' AS dependency_type,    COUNT(*) AS item_count,    STRING_AGG(CONVERT(nvarchar(max), c.name), N'; ') AS sample_itemsFROM sys.credentials AS c UNION ALL SELECT    N'Orphaned database users in selected database' AS dependency_type,    COUNT(*) AS item_count,    STRING_AGG(CONVERT(nvarchar(max), dp.name), N'; ') AS sample_itemsFROM sys.database_principals AS dpLEFT JOIN sys.server_principals AS sp    ON sp.sid = dp.sidWHERE DB_NAME() = @database_name  AND dp.type IN ('S', 'U', 'G')  AND dp.sid IS NOT NULL  AND sp.sid IS NULL  AND dp.name NOT IN (N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys');
Review before runningT-SQLUTF-840 lines

SQL Agent jobs may need to exist and run on the target instance. Linked servers and credentials can break even when the database restore succeeds. Orphaned users can block application access after a restore. Use this as a starting point; application and certificate checks may need separate validation.

8 / Drill

Run a recovery drill

A recovery drill should produce output: chosen target, restore sequence, file list, timings, validation checks, dependency results, problems found, and changes made to the runbook.

A tabletop run is useful for roles and decisions. It is not the same as restoring data and validating the result.

Compare the measured drill timing with the RPO and RTO planning estimate, then replace estimated inputs with the real numbers.

Reads recent SQL Agent history for backup, restore, recovery, and drill-related jobs.

SQL

Backup, restore, and recovery drill job history

T-SQL · 30 lines

SELECT TOP (100)    j.name AS job_name,    h.step_id,    h.step_name,    h.run_status,    DATETIMEFROMPARTS(        h.run_date / 10000,        (h.run_date % 10000) / 100,        h.run_date % 100,        h.run_time / 10000,        (h.run_time % 10000) / 100,        h.run_time % 100,        0    ) AS run_start_time,    ((h.run_duration / 10000) * 3600)      + (((h.run_duration % 10000) / 100) * 60)      + (h.run_duration % 100) AS run_duration_seconds,    h.messageFROM msdb.dbo.sysjobhistory AS hJOIN msdb.dbo.sysjobs AS j    ON j.job_id = h.job_idWHERE h.run_date >= CONVERT(int, CONVERT(char(8), DATEADD(day, -30, SYSDATETIME()), 112))  AND (      j.name LIKE N'%backup%'      OR j.name LIKE N'%restore%'      OR j.name LIKE N'%recovery%'      OR j.name LIKE N'%drill%'      OR h.step_name LIKE N'%restore%'  )ORDER BY run_start_time DESC;
Review before runningT-SQLUTF-830 lines

run_duration_seconds helps compare drill timing with RTO. message contains job output, errors, and path problems. Failed or long-running steps should feed back into the runbook. Job timing is useful only when the drill also validates the recovered service.

Drill outputWhy it matters
Restore sequenceShows the exact commands and files used.
Measured timingSeparates real RTO from estimate.
Validation resultsConfirms whether the restored database is usable.
Runbook fixesTurns drill findings into better incident behavior.

9 / Testing

SQL Server recovery testing

SQL Server recovery testing means restoring the database to a safe target, applying the required backup sequence, timing the work, and checking that the restored service is usable. It is broader than checking backup job success.

Useful recovery testing records the recovery point, restore commands, files used, timings, validation results, missing dependencies, and runbook fixes. Run it before upgrades, migrations, major application releases, storage changes, and after any backup process redesign.

TestWhat it checks
File restoreCan a full backup be restored to a safe SQL Server instance?
Point-in-time restoreCan full, differential, and log backups be applied to the chosen recovery point?
Dependency checkDo logins, users, jobs, linked servers, credentials, certificates, and app settings still work?
Timing checkDoes restore time fit the recovery target or outage expectation?
Application validationCan the application connect and pass the workflows that matter after restore?

9 / Do not guess

What not to change blindly

Recovery actions can destroy options. Slow down long enough to confirm target, sequence, source state, and rollback path before irreversible steps.

1

Do not start restoring before the target recovery point is agreed.

2

Do not use WITH RECOVERY until all required backups have been applied.

3

Do not skip the tail-log decision in full or bulk-logged recovery model incidents.

4

Do not assume the newest backup is the correct backup for a point-in-time restore.

5

Do not overwrite a production database without confirming target, files, and rollback path.

6

Do not treat a restored database as usable until logins, jobs, permissions, linked servers, apps, and validation checks pass.

7

Do not promise RTO from backup size alone; measure platform prep, restore time, validation, and cutover.

8

Do not treat a tabletop runbook as equal to a real restore drill.

10 / Recovery readiness

When to request recovery readiness review

A recovery readiness review makes sense when the restore sequence is unclear, point-in-time recovery has not been tested, tail-log decisions are not documented, dependencies are missing from the runbook, or drill timing does not match the business expectation.

Send backup history, restore history, restore output, runbook, job output, error logs, file paths, validation results, RPO/RTO targets, and what changed recently.

Request recovery readiness reviewRead the backup guide

Next step

If the restore sequence, drill timing, or dependency checks are not proven yet, use the SQL Server recovery readiness page or request the review above.

Compare the best SQL Server backup tools when recovery depends on choosing the right backup utility, SQL-specific tool, or wider platform.

Next useful reads: the SQL Server backup guide for recovery inputs, the SQL Server failover guide for alternate-path decisions, the SQL Server migration guide for planned moves, and the SQL Server monitoring guide for recovery alerting.