How to setup Azure Automation account to restore database on SQL MI ?

Question

Hello,

I have a PowerShell script (see below) that I currently run daily from a task scheduler on a VM to restore a production database (SQL Managed Instance) to a secondary instance (SQL Managed Instance).

I am planning to move this process to an Azure Automation Account. As I am new to using Azure Automation Accounts, I need assistance with understanding the necessary permissions required when using a service principal and managed identity to achieve this.

I need to set up the script below to run from the Azure Automation Account.

---

Select-AzSubscription xxx-xxxx-subscription

$utcTimeMinusTwoHours = (Get-Date).ToUniversalTime().AddHours(-2)

Format the time

$formattedTime = $utcTimeMinusTwoHours.ToString("yyyy-MM-dd HH:mm:ss")

Restore-AzSqlInstanceDatabase -FromPointInTimeBackup -Name "MyDB" -InstanceName "MySourceMIinstance" -ResourceGroupName "SourceResourceGrp" -PointInTime $formattedTime -TargetInstanceDatabaseName "MyTargetDB" -TargetInstanceName "MyTargetMI" -TargetResourceGroupName "MyTargetResourceGroup"

Answer 1

Hello Anoop !

Thank you for posting on Microsoft Learn.

In Azure Portal, create or use an already existing an Azure Automation account:

• Go to Azure Automation Accounts > Create.

Name it, select the region, resource group, and create it.

Enable System-assigned Managed Identity under "Identity" > "System Assigned" > On > Save.

Your automation account system-assigned managed identity needs access to perform the restore operation.

For both source and target SQL MI:

Assign the following RBAC roles at the resource group level or the SQL MI level, where needed:

• Contributor OR

Fine-grained roles:

SQL Managed Instance Contributor

  Reader (optional if you only need to read properties)
  
     Azure SQL DB Contributor (if you want tighter scope)
     

You can use this PowerShell (run in Cloud Shell or your local Az session):

$automationAccount = Get-AzAutomationAccount -Name "<your-automation-account>" -ResourceGroupName "<your-rg>"
$identityPrincipalId = $automationAccount.Identity.PrincipalId
New-AzRoleAssignment -ObjectId $identityPrincipalId `
    -RoleDefinitionName "Contributor" `
    -Scope "/subscriptions/<your-subscription-id>/resourceGroups/<source-or-target-RG>"

From the Modules Gallery, import the following into your Automation Account:

Az.Accounts

Az.Resources

Az.Sql

Then, go to your Automation Account > Runbooks > Create a Runbook

Name: Restore-SqlMIDatabase

Type: PowerShell

Runtime: 7.1 (recommended)

Paste your modified script (an example below):

param (
    [string] $sourceMI = "MySourceMIinstance",
    [string] $targetMI = "MyTargetMI",
    [string] $sourceRG = "SourceResourceGrp",
    [string] $targetRG = "MyTargetResourceGroup",
    [string] $databaseName = "MyDB",
    [string] $targetDBName = "MyTargetDB"
)

Connect-AzAccount -Identity

$utcTimeMinusTwoHours = (Get-Date).ToUniversalTime().AddHours(-2)

$formattedTime = $utcTimeMinusTwoHours.ToString("yyyy-MM-dd HH:mm:ss")

Restore-AzSqlInstanceDatabase -FromPointInTimeBackup `
    -Name $databaseName `
    -InstanceName $sourceMI `
    -ResourceGroupName $sourceRG `
    -PointInTime $formattedTime `
    -TargetInstanceDatabaseName $targetDBName `
    -TargetInstanceName $targetMI `
    -TargetResourceGroupName $targetRG

Go to the Runbook > Schedules > Add a schedule and create a new or link an existing schedule.