Restore database across servers(Azure SQL Database and Azure SQL managed instance)- Azure Automation

[Sabrin_Alsahsah]

In this article, you will find the required steps and setup to restore your Azure SQL database from one Azure SQL server to another Azure SQL server. The provided method can be applied on both Azure SQL database and Azure SQL server with a few differences that will be discussed later in this article in detail.

One of the common scenarios to use this approach is when customers would like to have an automated and scheduled job to restore a database from production to the development environment for development reasons.

In the below steps, we will be using Azure Automation and PowerShell Runbook commands to restore the database on another Azure SQL server\instance:

1. Access your Azure portal and create a new Azure Automation account if you don’t have an existing one, and you can follow this link for the required steps.  

Note: it’s required to set “create Azure Run As Account” to yes in order to run the PowerShell script later successfully. In case this value cannot be set to yes in your environment it will require to enable managed identity to Azure automation and you can check this link for more information about this preview.

2. When your automation account is created successfully, create a new PowerShell Runbook by accessing your Azure automation account -> Runbooks blade -> create a runbook and fill the required fields, and chose PowerShell as the Runbook type (as below).

For more information, you can check this link.

3. Import the below modules to your PowerShell runbook by accessing module gallery blade:

1. Az.Accounts
2. Az.Automation
3. Az.Compute
4. Az.sql

4. Add one of the below PowerShell commands to your runbook based on your Azure SQL resource type, if its Azure SQL database or Azure SQL managed instance.

Azure SQL database

Notes:

• The below script will use Geo Restore to restore the database from one Azure SQL server to another Azure SQL server. Geo-restore is available only for SQL databases configured with geo-redundant backup storage and you can have more information by accessing this link.
• The script will include a remove database command as it will remove the existing database and restore a new copy. 
• You can adjust the service tier level on the script as required.

Import-Module Az.Accounts
Import-Module Az.Automation
Import-Module Az.Compute
Import-Module Az.sql

# Ensures you do not inherit an AzContext in your runbook
Disable-AzContextAutosave -Scope Process

$connection = Get-AutomationConnection -Name AzureRunAsConnection

while(!($connectionResult) -and ($logonAttempt -le 10))
{
    $LogonAttempt++
    # Logging in to Azure...
    $connectionResult = Connect-AzAccount `
                            -ServicePrincipal `
                            -Tenant $connection.TenantID `
                            -ApplicationId $connection.ApplicationID `
                            -CertificateThumbprint $connection.CertificateThumbprint

    Start-Sleep -Seconds 30
}

$subscriptionId = "******"
$resourceGroupName = "******"
$ServerName = "******"
$TargetServerName= "******"
$databaseName = "******"
$targetDatabase = "******"

Get-AzSubscription -SubscriptionId $subscriptionId
Select-AzSubscription -SubscriptionId $subscriptionId

Remove-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $TargetServerName -DatabaseName $targetDatabase -Force

$GeoBackup = Get-AzSqlDatabaseGeoBackup -ResourceGroupName $resourceGroupName -ServerName $ServerName -DatabaseName $databaseName

Restore-AzSqlDatabase -FromGeoBackup -ResourceGroupName $resourceGroupName -ServerName $TargetServerName -TargetDatabaseName $targetDatabase -ResourceId $GeoBackup.ResourceID -Edition "Standard" -ServiceObjectiveName "S2"

Azure SQL managed instance

Notes:

• The below script will use Point In Time Restore (PITR) to restore a copy of the database from one Azure SQL instance to another Azure SQL instance. You can have more information by accessing this link.
• The script will include a remove database command as it will remove the old copy and create a new one as a copy.
• The script will restore the database to before 30 minutes, this can be adjusted as required. 
• You can adjust the service tier level on the script as required.

Import-Module Az.Accounts
Import-Module Az.Automation
Import-Module Az.Compute
Import-Module Az.sql
$connectionName = "AzureRunAsConnection"
try
{
     # Get the connection "AzureRunAsConnection "
     $servicePrincipalConnection = Get-AutomationConnection -Name $connectionName       
"Logging in to Azure..."
Connect-AzAccount `
-ServicePrincipal `
-TenantId $servicePrincipalConnection.TenantId `
-ApplicationId $servicePrincipalConnection.ApplicationId `
              -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 
}
catch {
     if (!$servicePrincipalConnection)
        {
         $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
       } else{
        Write-Error -Message $_.Exception
        throw $_.Exception
       }
}
 
Set-azContext -SubscriptionId "********"
$targetResourceGroupName = "********"
$targetInstanceName = "********"
$targetDatabase = "********"
$subscriptionId = "********"
$resourceGroupName = "********"
$managedInstanceName = "********"
$databaseName = "********"
$pointInTime = (Get-Date).AddMinutes(-30)
Remove-AzSqlInstanceDatabase -Name $targetDatabase -InstanceName $targetInstanceName -ResourceGroupName $targetResourceGroupName  -Force
Restore-AzSqlInstanceDatabase -FromPointInTimeBackup -ResourceGroupName $resourceGroupName -InstanceName $managedInstanceName -Name $databaseName -PointInTime $pointInTime -     TargetInstanceDatabaseName $targetDatabase -

5. Create a schedule for your runbook by following the steps in this link, and make sure to link it with your runbook.

6. Test running the runbook and make sure you are not receiving any errors in the errors sections.

I hope this article was helpful for you, please feel free to share your feedback in the comments section. 

Sabrin Alsahsah