Automating Database Restoration Between SQL Server Availability Groups Using PowerShell

Introduction

Managing SQL Server databases in Always On Availability Groups (AGs) often involves complex operations like database migrations, synchronizing replicas, or recovering from failures. Manually handling these tasks can be time-consuming and error-prone. Automating these processes not only reduces downtime but also ensures consistency across your AG environments.

In this post, I’ll walk you through a PowerShell script that automates the restoration of a database from one AG to another. The script handles everything—from performing a COPY-ONLY backup on the source AG to restoring the database on the target AG’s primary and secondary replicas, applying transaction logs, and rejoining the database to the new AG seamlessly.

This automation simplifies the restoration process, minimizes downtime, and keeps your AG replicas in sync without manual intervention. Let’s dive into how the script works and why it’s an essential tool for SQL Server administrators.

Script Overview

The script is designed to automate the process of restoring a database from a source Always On Availability Group to a target AG environment. Below is a step-by-step explanation of what the script does:

1. Import the SQL Server module.
2. Set up necessary variables based on your environment.
3. Define a function to execute SQL commands with error handling.
4. Perform a COPY-ONLY backup of the database from the source AG.
5. Take a transaction log backup to capture recent transactions.
6. Remove the database from the target AG if it already exists.
7. Restore the database on the target primary replica.
8. Restore the database on all target secondary replicas.
9. Apply the transaction log backup on the target primary replica.
10. Bring the database online on the target primary replica.
11. Apply the transaction log backup on all target secondary replicas.
12. Re-add the database to the target AG on the primary replica.
13. Re-join the database to the target AG on all secondary replicas.
14. Confirm completion of the restoration process.

Prerequisites

• SqlServer PowerShell Module: Ensure that the SqlServer module is installed and imported.
• Shared Backup Path: A shared network location accessible by all servers involved.

The Script

1. Import the SQL Server Module

Import-Module SqlServer

This line ensures that the required SqlServer PowerShell module is available for use.

2. Set Up Variables

$sourceAgListener = "SourceAGListener"
$targetPrimaryReplica = "TargetPrimaryReplica"
$targetSecondaryReplicas = @("SecondaryReplica1", "SecondaryReplica2")
$database = "YourDatabaseName"
$targetAgName = "TargetAGName"
$sharedBackupPath = "\\Shared\Backup\Path"
$sharedBackupFile = Join-Path $sharedBackupPath "$database.bak"
$tlogBackupFile = Join-Path $sharedBackupPath "$database.trn"

3. Define a Function to Execute SQL Commands

function Invoke-SqlCmdWithCheck {
    param (
        [string]$serverInstance,
        [string]$query
    )
    Write-Host "Executing query on $serverInstance"
    Invoke-Sqlcmd -ServerInstance $serverInstance -Query $query -ErrorAction Stop -TrustServerCertificate
}

4. Perform a COPY-ONLY Backup from the Source AG

Write-Host "Starting COPY-ONLY backup of $database from source AG..."
$backupQuery = @"
BACKUP DATABASE [$database]
TO DISK = N'$sharedBackupFile'
WITH COPY_ONLY, INIT
"@
Invoke-SqlCmdWithCheck -serverInstance $sourceAgListener -query $backupQuery
Write-Host "Backup completed at $sharedBackupFile."

5. Perform a Transaction Log Backup on the Source AG

Write-Host "Starting transaction log backup of $database..."
$tlogBackupQuery = @"
BACKUP LOG [$database]
TO DISK = N'$tlogBackupFile'
WITH INIT
"@
Invoke-SqlCmdWithCheck -serverInstance $sourceAgListener -query $tlogBackupQuery
Write-Host "Transaction log backup completed at $tlogBackupFile."

6. Remove the Database from the Target AG if It Exists

Write-Host "Checking if $database exists in the Target AG..."
$checkDatabaseQuery = "SELECT COUNT(*) AS DatabaseCount FROM sys.availability_databases_cluster WHERE database_name = N'$database'"
$result = Invoke-Sqlcmd -ServerInstance $targetPrimaryReplica -Query $checkDatabaseQuery -TrustServerCertificate

if ($result.DatabaseCount -eq 1) {
    Write-Host "Removing $database from the Target AG on the primary replica..."
    $removeAgDbQuery = "ALTER AVAILABILITY GROUP [$targetAgName] REMOVE DATABASE [$database]"
    Invoke-SqlCmdWithCheck -serverInstance $targetPrimaryReplica -query $removeAgDbQuery
    Write-Host "$database removed from the target AG."
} else {
    Write-Host "$database does not exist in the Target AG."
}

7. Restore the Database on the Target Primary Replica

Write-Host "Restoring $database on the Target Primary Replica ($targetPrimaryReplica) from $sharedBackupFile..."
$restorePrimaryQuery = @"
RESTORE DATABASE [$database]
FROM DISK = N'$sharedBackupFile'
WITH NORECOVERY, REPLACE
"@
Invoke-SqlCmdWithCheck -serverInstance $targetPrimaryReplica -query $restorePrimaryQuery
Write-Host "Primary replica restore completed."

8. Restore the Database on All Target Secondary Replicas

foreach ($secondaryReplica in $targetSecondaryReplicas) {
    Write-Host "Restoring $database on target secondary replica ($secondaryReplica) from $sharedBackupFile..."
    $restoreSecondaryQuery = @"
RESTORE DATABASE [$database]
FROM DISK = N'$sharedBackupFile'
WITH NORECOVERY, REPLACE
"@
    Invoke-SqlCmdWithCheck -serverInstance $secondaryReplica -query $restoreSecondaryQuery
    Write-Host "Restore completed on target secondary replica $secondaryReplica."
}

9. Apply the Transaction Log Backup on the Target Primary Replica

Write-Host "Applying transaction log backup on the Target Primary Replica ($targetPrimaryReplica)..."
$restoreTlogPrimaryQuery = @"
RESTORE LOG [$database]
FROM DISK = N'$tlogBackupFile'
WITH NORECOVERY
"@
Invoke-SqlCmdWithCheck -serverInstance $targetPrimaryReplica -query $restoreTlogPrimaryQuery
Write-Host "Transaction log restore (NORECOVERY) completed on Target Primary Replica."

10. Bring the Database Online on the Target Primary Replica

Write-Host "Bringing $database online on the Target Primary Replica ($targetPrimaryReplica)..."
$restoreRecoveryQuery = @"
RESTORE DATABASE [$database]
WITH RECOVERY
"@
Invoke-SqlCmdWithCheck -serverInstance $targetPrimaryReplica -query $restoreRecoveryQuery
Write-Host "Database $database is now online on the Target Primary Replica."

Conclusion

By automating the database restoration process between AGs using this PowerShell script, you can significantly reduce manual effort and the potential for errors. The script ensures that all replicas are properly synchronized and that the database is seamlessly integrated into the target AG.

This tool is invaluable for SQL Server administrators who need to manage complex AG environments efficiently. Feel free to customize the script to suit your specific environment and requirements.