Database Restore: Traditional T-SQL vs. Automated PowerShell

The Traditional Approach: Manual T-SQL Steps

To restore a database in an AlwaysOn environment the "traditional" way:

-- Step 1: Disconnect all users
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- Step 2: Remove from AlwaysOn (if member)
-- (Must connect to primary replica)
ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE MyDatabase;

-- Step 3: Delete from secondary replicas
-- (Connect to each secondary, run:)
DROP DATABASE MyDatabase;

-- Step 4: Restore the database
RESTORE DATABASE MyDatabase
FROM DISK = 'D:\Backups\MyDatabase_Full.bak'
WITH REPLACE;

-- Step 5: Restore transaction logs (if needed)
RESTORE LOG MyDatabase
FROM DISK = 'D:\Backups\MyDatabase_Log.trn'
WITH RECOVERY;  -- NORECOVERY if more logs follow

-- Step 6: Restore users and logins
-- Execute user/login creation scripts (if preserved)

-- Step 7: Fix orphaned users
EXEC sp_change_users_login 'Report';

-- Step 8: Re-add to AlwaysOn
ALTER AVAILABILITY GROUP MyAG ADD DATABASE MyDatabase;

-- Step 9: Set to multi-user
ALTER DATABASE MyDatabase SET MULTI_USER;
GO

The Problem with Manual Restore

Error-prone: Multiple steps, each can fail silently or unexpectedly
Forgotten steps: Easy to miss user export, orphan user fix, or AG rejoin
Inconsistent: Each DBA does it slightly differently
Time-consuming: Each step requires manual intervention
No audit trail: Hard to track what was done

The Automated Approach: PowerShell (Invoke-sqmRestoreDatabase)

The sqmSQLTool module provides Invoke-sqmRestoreDatabase, a PowerShell function that handles all steps automatically. A single command replaces the manual 9-step procedure:

# Restore from Full + Diff + Logs, with AlwaysOn handling
$backupSequence = @(
    "D:\Backups\MyDatabase_Full.bak",
    "D:\Backups\MyDatabase_Diff.bak",
    "D:\Backups\MyDatabase_Log.trn"
)

Invoke-sqmRestoreDatabase `
    -SqlInstance "SQL01" `
    -BackupFiles $backupSequence `
    -DatabaseName "MyDatabase" `
    -RejoinAvailabilityGroup

What happens automatically:

Detects AlwaysOn membership
Removes database from AG (if member)
Deletes from all secondary replicas
Exports users before restore
Sets database to single-user mode
Restores Full + Diff + Logs in sequence
Imports users back
Fixes orphaned users
Re-adds to AlwaysOn with automatic seeding
Sets to multi-user
Logs every step with detailed messages

Feature-by-Feature Comparison

Feature	Manual T-SQL	Invoke-sqmRestoreDatabase
AlwaysOn Detection	Manual check required	Automatic
Remove from AG	Manual (easy to forget)	Automatic
Delete from Secondaries	Manual (connect to each)	Automatic
User Export	Manual scripts needed	Automatic (Export-DbaUser)
Single-User Mode	Manual (ALTER DATABASE)	Automatic
Full + Diff + Logs	Multiple RESTORE statements	Single -BackupFiles parameter
File Mapping	Manual or script (complex)	Automatic (RESTORE FILELISTONLY)
Orphan User Repair	Manual (sp_change_users_login)	Automatic (Repair-DbaDbOrphanUser)
Rejoin to AG	Manual (Add-DbaAgDatabase)	Automatic (-RejoinAvailabilityGroup)
Logging	None (manual tracking)	Detailed audit trail
Error Handling	Script must manage	Built-in (with rollback)
WhatIf/Confirm	Not applicable	Supported

Advanced Features of Invoke-sqmRestoreDatabase

1. Automatic File Mapping

Handles physical file renaming and relocation without manual scripting:

Invoke-sqmRestoreDatabase `
    -SqlInstance "SQL01" `
    -BackupFile "D:\Backups\OldDB.bak" `
    -DatabaseName "OldDB" `
    -NewDatabaseName "NewDB" `
    -NewDatabaseFilePath "E:\SQLData" `
    -NewLogFilePath "F:\SQLLogs"

The function automatically maps each file from the backup to new locations.

2. Pre-Restore Backup

Automatically backs up the existing database before restore (for recovery if needed):

Invoke-sqmRestoreDatabase `
    -SqlInstance "SQL01" `
    -BackupFile "D:\Backups\MyDatabase.bak" `
    -DatabaseName "MyDatabase" `
    -BackupBeforeRestore

3. Policy Management

Temporarily disables Policy-Based Management policies during restore to avoid conflicts:

# Policies are automatically disabled, restore runs, then re-enabled
Invoke-sqmRestoreDatabase `
    -SqlInstance "SQL01" `
    -BackupFile "D:\Backups\MyDatabase.bak" `
    -DatabaseName "MyDatabase"

4. WhatIf and Confirm

Preview what would happen without making changes:

Invoke-sqmRestoreDatabase `
    -SqlInstance "SQL01" `
    -BackupFile "D:\Backups\MyDatabase.bak" `
    -DatabaseName "MyDatabase" `
    -WhatIf

When to Use Each Approach

Scenario	Manual T-SQL	Automated PowerShell
Simple restore (no AG, no users)	✓ Fine	✓ Overkill but safe
AlwaysOn database restore	✗ Error-prone	✓ Designed for this
Restore with user recovery	✗ Manual scripts needed	✓ Automatic
Full + Diff + Logs restore	✗ Multiple statements	✓ Single command
Emergency restore (speed matters)	✗ Slow, error-prone	✓ Fast, reliable
One-time restore	✓ Manual is fine	~ Helps avoid mistakes
Repeating restore (daily/weekly)	✗ Manual each time	✓ Script it once

Risk Comparison

Manual T-SQL Risks

Forgotten AG removal = Restore fails with "database is part of AG"
Forgotten secondary deletion = AG won't accept database back
Skipped user export = Users lost after restore
Typo in RESTORE statement = Wrong database renamed or wrong file used
Incomplete log restore = Database inconsistent
No rejoin to AG = Database stays outside AG

Automated PowerShell Safeguards

Automatic AG detection and handling
Automatic secondary replica cleanup
User export/import built-in
Orphan user repair automatic
File mapping validated with RESTORE FILELISTONLY
WhatIf mode to preview all steps
Detailed logging of every action
Rollback on failure (returns database to previous state)

Real-World Example: Production Restore

Scenario: Restore Production Database to Test After Data Corruption

Manual Approach (error-prone, time-consuming):

Connect to primary AG member
Remove database from AG (1-2 minutes)
Wait for secondary replicas to drop database (2-3 minutes)
Export users (manual script)
Connect to target SQL instance
Set to single-user mode
Run RESTORE DATABASE
Run RESTORE LOG (multiple times)
Run user import script
Manually fix orphans
Re-add to AG
Verify replication

Total time: 30-45 minutes. Risk of errors at each step.

Automated Approach:

Invoke-sqmRestoreDatabase `
    -SqlInstance "TestSQL01" `
    -BackupFiles $backupSequence `
    -DatabaseName "Production" `
    -NewDatabaseName "Production_Restored" `
    -RejoinAvailabilityGroup

Total time: 10-15 minutes. Automatic handling of all steps with logging.

The Verdict

For AlwaysOn environments: Always use automation. Manual restore is too complex and error-prone. For single-server: Both work, but automation is safer and faster once the script is ready.

Automated restore (Invoke-sqmRestoreDatabase) provides:

Consistency — same steps every time
Reliability — fewer failure points
Speed — 2-3x faster than manual
Audit trail — log every action
Safety — WhatIf mode to preview

← Back to Blog