calendar Jan 1, 0001 · 9 min read  ·
Share on: linkedin copy

sql-server-log-backup-restore-chain-script-guide.md

title: "SQL Server Log Backup Restore Chain Guide" description: "Learn how to apply SQL Server full, differential, and transaction log backups in the correct restore chain sequence using NORECOVERY and RECOVERY options." keywords:

  • sql server restore chain
  • transaction log backup restore
  • NORECOVERY RECOVERY sql server
  • restore database sql server
  • full differential log restore
  • msdb backupset query
  • sql server backup restore sequence
  • sql server log backup apply tags:
  • sql server
  • restore
  • backup
  • transaction log
  • NORECOVERY
  • RECOVERY
  • msdb
  • database recovery date: 2026-04-07 lastmod: 2026-04-11 showLastmod: true categories:
  • Scripts
  • SQL Server Backup Recovery series:
  • admin-guide

SQL Server Restore Chain: Full, Differential, and Log Backups

Restoring a SQL Server database to a specific point in time requires applying backups in the correct sequence. Getting the order wrong — or using the wrong recovery option — will break the restore chain and leave the database unusable.

Purpose and Overview

This guide explains how to restore a SQL Server database using a full backup, an optional differential backup, and one or more transaction log backups. It also shows how to query msdb to identify the available backup chain before you begin.

The restore chain must be applied in strict order: full first, then differential (if one exists), then transaction logs in chronological order. Every step except the final one uses WITH NORECOVERY, which keeps the database in a restoring state and allows additional backups to be applied. The final step uses WITH RECOVERY to bring the database online.

Code Breakdown

The full script below covers two parts: querying backup history from msdb, and applying the restore chain.

 1-- =============================================
 2-- SQL Server Restore Chain: Full + Diff + Log
 3-- Step 1: Query msdb to find the restore chain
 4-- =============================================
 5
 6-- Find the most recent Full, Differential, and Log backups for a database
 7DECLARE @DatabaseName SYSNAME = N'YourDatabaseName'; -- Set your database name
 8
 9SELECT
10    bs.database_name,
11    CASE bs.type
12        WHEN 'D' THEN 'Full'
13        WHEN 'I' THEN 'Differential'
14        WHEN 'L' THEN 'Transaction Log'
15    END AS backup_type,
16    bs.backup_start_date,
17    bs.backup_finish_date,
18    bmf.physical_device_name AS backup_file,
19    bs.first_lsn,
20    bs.last_lsn
21FROM msdb.dbo.backupset bs
22JOIN msdb.dbo.backupmediafamily bmf
23    ON bs.media_set_id = bmf.media_set_id
24WHERE bs.database_name = @DatabaseName
25  AND bs.type IN ('D', 'I', 'L')
26ORDER BY bs.backup_start_date DESC;
27
28-- =============================================
29-- Step 2: Apply the restore chain
30-- Replace file paths with your actual backup file locations
31-- =============================================
32
33-- Restore Full backup (NORECOVERY keeps DB in restoring state)
34RESTORE DATABASE [YourDatabaseName]
35FROM DISK = N'C:\Backups\YourDatabaseName_Full.bak'
36WITH NORECOVERY, STATS = 10;
37
38-- Restore Differential backup (if one exists — use NORECOVERY)
39RESTORE DATABASE [YourDatabaseName]
40FROM DISK = N'C:\Backups\YourDatabaseName_Diff.bak'
41WITH NORECOVERY, STATS = 10;
42
43-- Restore Transaction Log backups in order (use NORECOVERY for each except the last)
44RESTORE LOG [YourDatabaseName]
45FROM DISK = N'C:\Backups\YourDatabaseName_Log1.bak'
46WITH NORECOVERY;
47
48RESTORE LOG [YourDatabaseName]
49FROM DISK = N'C:\Backups\YourDatabaseName_Log2.bak'
50WITH NORECOVERY;
51
52-- Final log backup: use RECOVERY to bring the database online
53RESTORE LOG [YourDatabaseName]
54FROM DISK = N'C:\Backups\YourDatabaseName_LogFinal.bak'
55WITH RECOVERY;
56
57-- =============================================
58-- Alternative: If no differential exists,
59-- apply log backups directly after full backup
60-- =============================================
61
62-- Restore Full (NORECOVERY)
63RESTORE DATABASE [YourDatabaseName]
64FROM DISK = N'C:\Backups\YourDatabaseName_Full.bak'
65WITH NORECOVERY;
66
67-- Apply each log in sequence, RECOVERY on the last one
68RESTORE LOG [YourDatabaseName]
69FROM DISK = N'C:\Backups\YourDatabaseName_Log1.bak'
70WITH RECOVERY;

Step 1: Query Backup History from msdb

Before restoring, it is good practice to confirm which backups are available. The query joins msdb.dbo.backupset with msdb.dbo.backupmediafamily to return the backup type, start and finish times, physical file path, and the LSN (Log Sequence Number) range for each backup.

The CASE expression translates the single-character type column (D, I, L) into readable labels: Full, Differential, and Transaction Log. Results are ordered by backup_start_date DESC so the most recent backups appear first.

The first_lsn and last_lsn columns are important for verifying that the log chain is unbroken. Each log backup's first_lsn should be less than or equal to the previous backup's last_lsn.

Step 2: Restore the Full Backup with NORECOVERY

1RESTORE DATABASE [YourDatabaseName]
2FROM DISK = N'C:\Backups\YourDatabaseName_Full.bak'
3WITH NORECOVERY, STATS = 10;

The full backup is always the first step. WITH NORECOVERY leaves the database in a restoring state, meaning it cannot be accessed yet but can still accept additional backups. STATS = 10 prints progress messages every 10 percent, which is useful for large files.

Step 3: Restore the Differential Backup (Optional)

1RESTORE DATABASE [YourDatabaseName]
2FROM DISK = N'C:\Backups\YourDatabaseName_Diff.bak'
3WITH NORECOVERY, STATS = 10;

A differential backup contains all changes since the last full backup. If one exists, apply only the most recent differential — applying an older differential and then a newer one is not valid. Use NORECOVERY here as well because log backups still need to be applied.

If no differential backup exists, skip this step and proceed directly to applying transaction log backups after the full restore.

Step 4: Restore Transaction Log Backups in Order

1RESTORE LOG [YourDatabaseName]
2FROM DISK = N'C:\Backups\YourDatabaseName_Log1.bak'
3WITH NORECOVERY;
4
5RESTORE LOG [YourDatabaseName]
6FROM DISK = N'C:\Backups\YourDatabaseName_Log2.bak'
7WITH NORECOVERY;

Transaction log backups must be applied in the exact order they were created. Each log backup must follow the previous one without gaps. Use WITH NORECOVERY for every log backup except the final one. Applying them out of order or skipping one will break the chain and produce an error.

Use RESTORE LOG (not RESTORE DATABASE) for log backup files.

Step 5: Final Log Backup with RECOVERY

1RESTORE LOG [YourDatabaseName]
2FROM DISK = N'C:\Backups\YourDatabaseName_LogFinal.bak'
3WITH RECOVERY;

WITH RECOVERY is used on the last restore step only. It rolls back any uncommitted transactions and brings the database online. After this command completes, the database is accessible. You cannot apply any more backups after using RECOVERY without starting the restore chain over from the full backup.

Alternative: Full Backup Followed Directly by Log Backups

1RESTORE DATABASE [YourDatabaseName]
2FROM DISK = N'C:\Backups\YourDatabaseName_Full.bak'
3WITH NORECOVERY;
4
5RESTORE LOG [YourDatabaseName]
6FROM DISK = N'C:\Backups\YourDatabaseName_Log1.bak'
7WITH RECOVERY;

When no differential backup exists, transaction log backups are applied directly after the full backup. This is the simpler two-step pattern: full with NORECOVERY, then logs in sequence ending with RECOVERY.

Key Benefits and Use Cases

Point-in-time recovery. Applying transaction log backups in sequence allows you to restore a database to any specific point in time within the backup window. This is essential for recovering from accidental data deletion or corruption.

Disaster recovery procedures. The full-plus-log restore chain is the standard approach for recovering a database after hardware failure or data loss on a production server.

Database migration. The same restore sequence is used when migrating a database to a new server. Restore with NORECOVERY, bring the source database offline, apply a final log backup with RECOVERY, and the migrated database will have minimal data loss.

Database refresh for testing. Development and QA teams use this pattern to refresh test environments from production backups, restoring to a known point in time.

Log shipping. Log shipping automates this exact restore chain continuously — applying transaction log backups to a secondary server on a schedule using NORECOVERY until a failover is triggered.

Performance Considerations

Large backups and STATS. Use STATS = 10 or STATS = 5 on full and differential restores to monitor progress. Without it, a large restore gives no feedback and appears to hang.

Restore speed is I/O-bound. The bottleneck for most restores is disk read speed on the backup source and disk write speed on the destination. Restoring from network shares is significantly slower than restoring from local disk.

Parallelism does not apply. Unlike backups, SQL Server does not parallelize restore operations across multiple files in the same way. Striped backup sets (backups split across multiple files) can improve restore throughput, but only if the original backup was written to multiple devices.

Tail-log backup before final restore. In a live disaster recovery scenario, take a tail-log backup of the damaged source database before starting the restore chain on the target. This captures any transactions that occurred after the last scheduled log backup and minimizes data loss.

Avoid restoring to the same database name on the same server unless you are certain the existing database is no longer needed. Use WITH MOVE in the RESTORE DATABASE statement to redirect data and log files to new paths if needed.

Practical Tips

Always query msdb first. Before starting a restore, run the msdb query in Step 1 to confirm the backup chain is complete and the file paths are correct. Trying to restore a missing or corrupt backup file mid-chain will fail and you will need to start over.

Use database snapshots for testing. Before performing a restore that overwrites a live database, consider taking a database snapshot so you can revert quickly if the restore does not produce the expected result.

Do not mix RECOVERY and NORECOVERY. Using RECOVERY on any step before the last one ends the restore chain. The database goes online but you lose the ability to apply remaining backups. If this happens, you must start the entire restore sequence again from the full backup.

Verify your LSN chain. Use the first_lsn and last_lsn values from the msdb query to confirm there are no gaps in the log chain. SQL Server will also produce an error if you attempt to apply a log backup out of sequence.

Test your restore process regularly. A backup that has never been tested is not a reliable backup. Schedule periodic restore drills on a non-production server to verify that the full chain can be applied successfully and that recovery time meets business requirements.

Use STOPAT for point-in-time recovery. If you need to stop recovery at a specific moment, add STOPAT = '2026-04-11T14:30:00' to the final RESTORE LOG statement. SQL Server will apply transactions up to that timestamp and then recover the database.

Conclusion

The SQL Server restore chain — full, optional differential, then transaction logs in order — is a fundamental DBA skill. The critical rule is straightforward: use WITH NORECOVERY on every step except the last, and use WITH RECOVERY only on the final restore to bring the database online. Querying msdb.dbo.backupset before you begin ensures the chain is complete and the file paths are correct, which prevents surprises mid-restore.

References