The Practical Guide to Restoring SQL Server Databases (Full + Diff + Lots of Log Backups) — For DBAs

Updated with Always On Availability Group scenarios

Restores are when your habits show. This guide walks you through restoring a database that has a full backup, a recent differential, and anywhere from 10 to 60 transaction log backups. You’ll see both SSMS and command-line options, when to use PowerShell dbatools for speed, and what changes when the database is part of an Always On Availability Group (AG). We’ll also cover permissions, statistics, and Agent job handling. Inline references are included; full references are at the end.

---

Why the restore sequence matters

When restoring under the Full or Bulk-logged recovery models, the required order is:

1. Full backup → WITH NORECOVERY
2. Latest applicable differential → WITH NORECOVERY
3. Each log backup in order → WITH NORECOVERY (except the last)
4. Final restore → WITH RECOVERY or STOPAT for point-in-time

If the source database is still online, start with a tail-log backup to preserve the chain.

For long log chains, SSMS is clunky. dbatools automates this safely.

---

GUI vs PowerShell: which to use?

• SSMS GUI: Fine for small or one-off restores, good for screenshots and audits, but tedious for many logs.
• T-SQL: Repeatable and scriptable; works well when you want control.
• dbatools: Best when you have lots of log files, need to automate, or want predictable restores in lower environments.

---

Pre-restore checklist

• Confirm target instance and storage.
• Collect full, latest differential, and all logs needed.
• Verify chain in msdb with backupset and backupmediafamily.
• Take a tail-log backup if restoring to latest.
• Plan file relocation before starting.

---

Option A — SSMS GUI

Same steps as before: Full → Diff → Logs → Recovery. For multiple logs, carefully verify order in the GUI or script it out. SSMS works but is not the most efficient for 20–60 log files.

---

Option B — T-SQL Restore Example

-- Full WITH MOVE and NORECOVERY
RESTORE DATABASE [MyDb]
  FROM DISK = N'\\backups\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;

-- Diff
RESTORE DATABASE [MyDb]
  FROM DISK = N'\\backups\MyDb_diff.bak'
  WITH NORECOVERY, STATS = 10;

-- Logs (many)
RESTORE LOG [MyDb]
  FROM DISK = N'\\backups\MyDb_log_20251024_1200.trn'
  WITH NORECOVERY;

RESTORE LOG [MyDb]
  FROM DISK = N'\\backups\MyDb_log_20251024_1300.trn'
  WITH STOPAT = '2025-10-24T12:54:30', RECOVERY;

Option C — dbatools PowerShell (highly recommended for long log chains)

# Install once if needed
# Install-Module dbatools -Scope AllUsers

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

# Check the backup chain
Get-DbaBackupInformation -Path $backupPath -SqlInstance $server -Database $dbName |
  Test-DbaBackupInformation

# Restore
Restore-DbaDatabase -SqlInstance $server -DatabaseName $dbName `
  -Path $backupPath -WithReplace -AutoRelocateFile `
  -DestinationDataDirectory $dataPath -DestinationLogDirectory $logPath `
  -TrustDbBackupHistory

🧠 Special Considerations: Always On Availability Groups (AG)

Restoring a database that’s part of an AG is not the same as a standalone restore. If the database is joined to an AG, you must remove or suspend it before restoring. Here’s why:

• AG manages log flow. You can’t apply backups while the database is synchronized.
• Log backup jobs may still run and interfere.
• Readable secondaries rely on the log chain, so manual restores must respect it.
• AG metadata must be updated after the restore.

Step-by-step for AG environments:

1. Remove the database from the AG

ALTER DATABASE [MyDb] SET HADR OFF;

Or in SSMS: Right-click Always On High Availability → Availability Databases → Remove.

Disable backup or ETL jobs

• Stop transaction log backup jobs.
• Stop any Agent jobs touching this database.
• Prevent unexpected new log backups.

Restore on the primary (Full → Diff → Logs) as normal.

Restore on secondaries (optional reseed)

• Restore the same chain on each secondary WITH NORECOVERY.
• This preps them to be joined back into the AG.

Rejoin the database to the AG

ALTER DATABASE [MyDb] SET HADR AVAILABILITY GROUP = [MyAGName];

1. Or use SSMS → Add Database to Availability Group.
2. Re-enable Agent jobs after everything is synchronized.

---

Automating the AG restore and reseed with dbatools

dbatools includes cmdlets specifically designed for AG scenarios:

# Variables
$primary = 'PrimarySQL01'
$secondary = 'SecondarySQL01'
$agName = 'MyAG'
$dbName = 'MyDb'
$backupPath = '\\backups\MyDb'

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

# 2) Disable jobs (example: Ola log backup)
Get-DbaAgentJob -SqlInstance $primary | Where-Object {$_.Name -like '*MyDb*'} | Disable-DbaAgentJob

# 3) Restore on primary
Restore-DbaDatabase -SqlInstance $primary -DatabaseName $dbName -Path $backupPath -WithReplace -AutoRelocateFile

# 4) Restore chain on secondary
Restore-DbaDatabase -SqlInstance $secondary -DatabaseName $dbName -Path $backupPath -WithReplace -NoRecovery

# 5) Add back to AG
Add-DbaAgDatabase -SqlInstance $primary -Database $dbName -AvailabilityGroup $agName

# 6) Re-enable jobs
Get-DbaAgentJob -SqlInstance $primary | Where-Object {$_.Name -like '*MyDb*'} | Enable-DbaAgentJob

This approach removes most manual steps, works consistently, and is easy to rerun in DR or test refresh scenarios.

---

Post-Restore Tasks (Applies to Standalone and AG)

1. Repair orphaned users

Get-DbaDbOrphanUser -SqlInstance $server -Database $dbName | Repair-DbaDbOrphanUser

or

ALTER USER [AppUser] WITH LOGIN = [AppUser];

2.Integrity check

DBCC CHECKDB (N'MyDb') WITH NO_INFOMSGS, PHYSICAL_ONLY;

3.Statistics

EXEC sp_updatestats;

or targeted updates if needed.

Workshop: AG + Multi-Backup Restore Practice

1. Build a test AG with one primary and one secondary.
2. Take a Full + Diff + 10 Log backups.
3. Remove database from AG.
4. Disable log backup job.
5. Restore full chain on primary with dbatools.
6. Restore same backups on secondary WITH NORECOVERY.
7. Add database back to AG and validate sync.
8. Re-enable Agent jobs.
9. Run CHECKDB and sp_updatestats.

---

Safe Defaults and Tips for AGs

• Always restore on primary first.
• Restore secondaries with NORECOVERY to avoid extra log shipping steps.
• Always disable Agent backup jobs to keep the log chain clean.
• Re-enable jobs only after synchronization completes.
• Automatic seeding works too, but many DBAs prefer manual restore for control.

---

Final Thoughts

Restoring a database correctly isn’t just about the right backups — it’s about respecting the environment. On a standalone instance, SSMS or T-SQL is fine. For heavy chains or AG environments, dbatools saves hours and avoids mistakes.

On AG setups, the most common mistakes are:

• Forgetting to remove the database from the AG before restoring.
• Letting backup jobs run mid-restore.
• Not reseeding secondaries properly.

Automate these steps, keep them in a runbook, and restoring under pressure will feel like any other day.

---

References

1. Microsoft Docs — Restore to a Point in Time (Full Recovery Model)
2. Microsoft Docs — Tail-Log Backups
3. Microsoft Docs — Restore a Transaction Log Backup
4. Microsoft Docs — Restore a Database Backup using SSMS
5. Microsoft Docs — Restore and Recovery of Availability Databases
6. Microsoft Docs — Remove a Database from an Availability Group
7. dbatools Docs — Restore-DbaDatabase
8. dbatools Docs — Remove-DbaAgDatabase
9. dbatools Docs — Add-DbaAgDatabase
10. Microsoft Docs — DBCC CHECKDB
11. Microsoft Docs — UPDATE STATISTICS
12. Ola Hallengren — Maintenance Solution for SQL Server

---

✅ Key takeaway:
If the database is part of an AG, remove it first, disable jobs, restore your chain, rejoin it, and re-enable everything. If it’s standalone, follow the same sequence minus the AG steps. And if you’re doing this more than once a month, automate it with dbatools.