Restoring SQL Server Databases: Full, Differential, and Log Backup Chains Including Always On AG

---

Restoring under pressure is when preparation shows. This guide covers restoring a database with a full backup, a recent differential, and anywhere from 10 to 60 transaction log backups, using three approaches: SSMS GUI, T-SQL, and dbatools PowerShell. It also covers what changes when the database is part of an Always On Availability Group, including the correct sequence for removing, restoring, and rejoining.

1 Why the Restore Sequence Matters Beginner

Under the Full or Bulk-Logged recovery model, SQL Server enforces a strict restore sequence. Each backup depends on the one before it forming an unbroken chain from the full backup to the target point in time. Applying backups out of order or skipping any link in the chain causes the restore to fail.

The required sequence is:

1. Full backup restored WITH NORECOVERY
2. Most recent applicable differential backup restored WITH NORECOVERY
3. Each log backup in chronological order restored WITH NORECOVERY (except the last)
4. Final restore applied WITH RECOVERY to bring the database online, or WITH STOPAT for point-in-time recovery

Restore Tool	Best For	When to Avoid
SSMS GUI	One-off restores, small log chains, audit screenshots	20+ log files: manual ordering is tedious and error-prone
T-SQL	Repeatable scripts, full control over sequence and file placement	Very long log chains where scripting all file paths is impractical
dbatools	Long log chains, automation, AG reseed, test refresh workflows	Environments where PowerShell is restricted by policy

2 Pre-Restore Checklist Beginner

• Confirm the target instance name, edition, and available storage for data and log files
• Collect the full backup, the most recent differential, and all log backups covering the gap from the differential to the target time
• Verify the backup chain using msdb..backupset and msdb..backupmediafamily before starting
• Take a tail-log backup if restoring a live database to its latest state
• Plan file relocation paths before issuing the first RESTORE command
• If the database is part of an AG: read Section 6 before touching anything

-- Verify the backup chain before restoring
-- Shows all backups for a database in chronological order
SELECT
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.type                                     AS BackupType,
    -- D=Full, I=Differential, L=Log
    bmf.physical_device_name                    AS BackupFile,
    bs.first_lsn,
    bs.last_lsn,
    bs.database_backup_lsn
FROM msdb.dbo.backupset                         bs
JOIN msdb.dbo.backupmediafamily                 bmf
    ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = N'MyDb'
ORDER BY bs.backup_start_date;

3 Option A: SSMS GUI Beginner

Right-click the Databases node in Object Explorer and select Restore Database. On the General page, select Device and browse to the full backup file. SSMS reads the backup header and pre-populates the restore plan. On the Files page, verify or change the data and log file paths. On the Options page, confirm NORECOVERY if more backups follow, or RECOVERY if this is the final step.

For multiple log files, SSMS allows adding them in the restore plan but requires careful verification of the order. The Timeline button opens a visual point-in-time selector that is useful for identifying the correct STOPAT value.

4 Option B: T-SQL Intermediate

T-SQL gives complete control over the restore sequence, file placement, and recovery options. Scripts are repeatable and can be stored in source control. The pattern is identical every time: full with NORECOVERY and MOVE, differential with NORECOVERY, logs in order with NORECOVERY, final log with RECOVERY or STOPAT.

-- Step 1: Tail-log backup (if source database is online)
BACKUP LOG [MyDb]
TO DISK = N'\\backups\MyDb\MyDb_taillog.trn'
WITH NORECOVERY, STATS = 10;

-- Step 2: Full backup WITH MOVE to relocate files
RESTORE DATABASE [MyDb]
FROM DISK = N'\\backups\MyDb\MyDb_full.bak'
WITH NORECOVERY,
     REPLACE,
     MOVE N'MyDb'     TO N'E:\SQLData\MyDb.mdf',
     MOVE N'MyDb_log' TO N'F:\SQLLogs\MyDb_log.ldf',
     STATS = 10;

-- Step 3: Differential backup
RESTORE DATABASE [MyDb]
FROM DISK = N'\\backups\MyDb\MyDb_diff.bak'
WITH NORECOVERY,
     STATS = 10;

-- Step 4: Log backups in chronological order (all WITH NORECOVERY except the last)
RESTORE LOG [MyDb]
FROM DISK = N'\\backups\MyDb\MyDb_log_20251024_1200.trn'
WITH NORECOVERY;

RESTORE LOG [MyDb]
FROM DISK = N'\\backups\MyDb\MyDb_log_20251024_1300.trn'
WITH NORECOVERY;

-- Step 5: Final log with RECOVERY (bring online) or STOPAT (point-in-time)
-- Option A: Restore to latest (bring online immediately)
RESTORE LOG [MyDb]
FROM DISK = N'\\backups\MyDb\MyDb_log_20251024_1400.trn'
WITH RECOVERY;

-- Option B: Point-in-time restore (use WITH STOPAT on the log containing the target time)
RESTORE LOG [MyDb]
FROM DISK = N'\\backups\MyDb\MyDb_log_20251024_1400.trn'
WITH STOPAT = '2025-10-24T12:54:30', RECOVERY;

-- Check backup file metadata before restoring
RESTORE HEADERONLY
FROM DISK = N'\\backups\MyDb\MyDb_full.bak';

-- Verify file logical names for MOVE clauses
RESTORE FILELISTONLY
FROM DISK = N'\\backups\MyDb\MyDb_full.bak';

5 Option C: dbatools PowerShell Intermediate

dbatools handles long log chains reliably by automatically discovering, ordering, and applying all backups in a path. It manages file relocation, chain validation, and recovery state. For 10 to 60 log files, dbatools reduces a complex multi-step process to a few lines and eliminates the risk of applying files in the wrong order.

# Install dbatools if not already available
# Install-Module dbatools -Scope AllUsers

$server     = 'TargetSQL01'
$dbName     = 'MyDb'
$backupPath = '\\backups\MyDb'
$dataPath   = 'E:\SQLData'
$logPath    = 'F:\SQLLogs'

# Step 1: Validate the backup chain before restoring
Get-DbaBackupInformation -Path $backupPath -SqlInstance $server -Database $dbName |
    Test-DbaBackupInformation

# Step 2: Restore all backups in the path automatically ordered
Restore-DbaDatabase `
    -SqlInstance               $server `
    -DatabaseName              $dbName `
    -Path                      $backupPath `
    -WithReplace `
    -AutoRelocateFile `
    -DestinationDataDirectory  $dataPath `
    -DestinationLogDirectory   $logPath `
    -TrustDbBackupHistory

# For point-in-time: add -StopAt parameter
# Restore-DbaDatabase ... -StopAt '2025-10-24 12:54:30'

6 Why AG Restores Are Different Intermediate

Restoring a database that is joined to an Always On Availability Group is not the same as a standalone restore. Attempting to apply backups while the database is synchronized with the AG will fail or corrupt the log chain. Three things in the AG environment interfere with a manual restore:

• AG manages log flow. The AG synchronization mechanism owns the log chain for enrolled databases. Manual RESTORE LOG commands conflict directly with this mechanism.
• Log backup jobs may run during the restore. If Ola Hallengren or any other backup solution is running scheduled log backups, those jobs will generate new log files and break the chain mid-restore.
• Readable secondaries depend on the log chain. Any readable secondary must receive the same log records in the same order. A manual restore that applies a different set of logs breaks the secondary’s ability to rejoin the AG.

7 Manual AG Restore Step by Step Intermediate

Step 1: Remove the database from the AG

-- Remove from AG on the primary replica
ALTER DATABASE [MyDb] SET HADR OFF;

-- Or in SSMS: Right-click Always On High Availability
-- > Availability Databases > Remove Database from Availability Group

Step 2: Disable backup and ETL Agent jobs

-- Disable all Agent jobs that touch this database
-- Replace 'MyDb' with the actual database name pattern used in job names
USE msdb;
GO

EXEC sp_update_job @job_name = N'DatabaseIntegrityCheck - MyDb', @enabled = 0;
EXEC sp_update_job @job_name = N'DatabaseBackup - MyDb - LOG',   @enabled = 0;
-- Repeat for each relevant job

-- Or use a pattern to find and disable all at once
SELECT name, enabled FROM sysjobs WHERE name LIKE N'%MyDb%';

Step 3: Restore on the primary replica

Apply the full chain using T-SQL (Section 4) or dbatools (Section 5). The restore sequence is identical to a standalone restore.

Step 4: Restore on each secondary WITH NORECOVERY

Each secondary must receive the same backup chain applied with NORECOVERY so the database is left in a state ready to accept log records from the AG synchronization engine when it rejoins.

-- On each secondary: apply the same chain WITH NORECOVERY
-- Do NOT apply WITH RECOVERY on secondaries before rejoining the AG
RESTORE DATABASE [MyDb]
FROM DISK = N'\\backups\MyDb\MyDb_full.bak'
WITH NORECOVERY, REPLACE,
     MOVE N'MyDb'     TO N'E:\SQLData\MyDb.mdf',
     MOVE N'MyDb_log' TO N'F:\SQLLogs\MyDb_log.ldf';

RESTORE DATABASE [MyDb]
FROM DISK = N'\\backups\MyDb\MyDb_diff.bak'
WITH NORECOVERY;

-- Apply all logs WITH NORECOVERY on secondaries
-- The AG redo thread will handle forward recovery after rejoin

Step 5: Rejoin the database to the AG

-- Rejoin on the primary (the secondary joins automatically after this)
ALTER DATABASE [MyDb]
SET HADR AVAILABILITY GROUP = [MyAGName];

-- Or in SSMS: Right-click the AG > Add Database > select MyDb

-- Monitor synchronization state after rejoining
SELECT
    drs.database_id,
    DB_NAME(drs.database_id)            AS DatabaseName,
    ar.replica_server_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.redo_queue_size,
    drs.log_send_queue_size
FROM sys.dm_hadr_database_replica_states    drs
JOIN sys.availability_replicas              ar
    ON drs.replica_id = ar.replica_id
WHERE DB_NAME(drs.database_id) = N'MyDb';

Step 6: Re-enable Agent jobs after synchronization confirms healthy

EXEC sp_update_job @job_name = N'DatabaseIntegrityCheck - MyDb', @enabled = 1;
EXEC sp_update_job @job_name = N'DatabaseBackup - MyDb - LOG',   @enabled = 1;

8 Automating AG Restore with dbatools Advanced

dbatools provides AG-specific cmdlets that wrap the manual steps above into a repeatable automation. This approach eliminates most manual steps, works consistently across environments, and is straightforward to rerun in DR or test environment refresh scenarios.

# Variables
$primary    = 'PrimarySQL01'
$secondary  = 'SecondarySQL01'
$agName     = 'MyAG'
$dbName     = 'MyDb'
$backupPath = '\\backups\MyDb'
$dataPath   = 'E:\SQLData'
$logPath    = 'F:\SQLLogs'

# Step 1: Remove from AG on the primary
Remove-DbaAgDatabase `
    -SqlInstance       $primary `
    -Database          $dbName `
    -AvailabilityGroup $agName `
    -Confirm:$false

# Step 2: Disable Agent jobs matching the database name pattern
Get-DbaAgentJob -SqlInstance $primary |
    Where-Object { $_.Name -like "*$dbName*" } |
    Disable-DbaAgentJob

# Step 3: Restore full chain on the primary
Restore-DbaDatabase `
    -SqlInstance              $primary `
    -DatabaseName             $dbName `
    -Path                     $backupPath `
    -WithReplace `
    -AutoRelocateFile `
    -DestinationDataDirectory $dataPath `
    -DestinationLogDirectory  $logPath

# Step 4: Restore same chain on the secondary WITH NORECOVERY
Restore-DbaDatabase `
    -SqlInstance              $secondary `
    -DatabaseName             $dbName `
    -Path                     $backupPath `
    -WithReplace `
    -NoRecovery `
    -AutoRelocateFile `
    -DestinationDataDirectory $dataPath `
    -DestinationLogDirectory  $logPath

# Step 5: Add the database back to the AG
Add-DbaAgDatabase `
    -SqlInstance       $primary `
    -Database          $dbName `
    -AvailabilityGroup $agName

# Step 6: Re-enable Agent jobs after confirming synchronization
Get-DbaAgentJob -SqlInstance $primary |
    Where-Object { $_.Name -like "*$dbName*" } |
    Enable-DbaAgentJob

9 Post-Restore Tasks Beginner

These steps apply to both standalone and AG restores. Run them after the database is online and, for AG databases, after synchronization is confirmed healthy.

Repair orphaned users

-- dbatools: find and repair all orphaned users in one command
Get-DbaDbOrphanUser -SqlInstance $server -Database $dbName |
    Repair-DbaDbOrphanUser

-- T-SQL: repair a specific user manually
ALTER USER [AppUser] WITH LOGIN = [AppUser];

Integrity check

-- Physical-only check is fast and catches most corruption
DBCC CHECKDB (N'MyDb') WITH NO_INFOMSGS, PHYSICAL_ONLY;

-- For a full check (slower, more thorough)
DBCC CHECKDB (N'MyDb') WITH NO_INFOMSGS;

Update statistics

-- Update all statistics in the database
EXEC sp_updatestats;

-- Or use dbatools for targeted updates with sampling control
Update-DbaStatistic -SqlInstance $server -Database $dbName

10 Workshop: AG and Multi-Log Restore Practice Advanced

This workshop covers the complete AG restore cycle in a test environment. The goal is to make the process familiar before it needs to happen under pressure.

1. Build a test AG with one primary and one secondary in a non-production environment
2. Take a full backup, a differential, and 10 consecutive log backups
3. Remove the database from the AG using ALTER DATABASE SET HADR OFF
4. Disable the log backup Agent job
5. Restore the full chain on the primary using dbatools with -AutoRelocateFile
6. Restore the same chain on the secondary with -NoRecovery
7. Add the database back to the AG and monitor sys.dm_hadr_database_replica_states until synchronization shows SYNCHRONIZED
8. Re-enable Agent jobs
9. Run DBCC CHECKDB and sp_updatestats
10. Repeat the exercise targeting a specific point in time using -StopAt in dbatools

References

• Microsoft Docs: Restore to a Point in Time (Full Recovery Model)
• Microsoft Docs: Tail-Log Backups
• Microsoft Docs: Restore a Transaction Log Backup
• Microsoft Docs: Restore and Recovery of Availability Databases
• Microsoft Docs: Remove a Database from an Availability Group
• dbatools: Restore-DbaDatabase
• dbatools: Remove-DbaAgDatabase
• dbatools: Add-DbaAgDatabase
• Microsoft Docs: DBCC CHECKDB
• Ola Hallengren: SQL Server Maintenance Solution
• SQLYARD: DBATools Migration Workflow Guide
• SQLYARD: Always On Availability Groups Complete Guide
• SQLYARD: SQL Server Orphan Users Guide