First published on MSDN on Jan 21, 2019.
Note: This version of article is adjusted for the latest Az Powershell module
Azure SQL Database - Managed Instance is fully-managed PaaS service that provides advanced disaster-recovery capabilities. Even if you accidentally drop a database or someone drops your database as part of security attack, Managed Instance will enable you to easily recover the dropped database. This feature is in preview at the time of writing this article.
Azure SQL Managed Instance performs automatic backups of you database every 5-10 minutes. If anything happens with your database and even if someone drops it, your data is not lost . Managed Instance enables you to easily re-create the dropped database from the automatic backups.
Now, let's imagine that someone dropped your database. Below you can find the PowerShell script that can restore it.
Recreate your dropped database
Before you run this script you need to login to your Azure account and select the subscription where your database is dropped using the script like:
Login-AzAccount
$subscriptionId = "cd82737-0791-418f-......"
Select-AzSubscription -SubscriptionId $subscriptionId
You also need to install necessary Az modules:
Install-Module -Name "Az.Accounts"
Install-Module -Name "Az.Resources"
Now you are ready to restore your database.
First, populate information about the instance and database (in this case AdventureWorksDW) that you want to recover:
$subscriptionId = "cd827379-9270-0791-....."
$resourceGroup = "rg_recovery"
$location = "West Central US"
$managedInstanceName = "my-managed-instance"
$deletedDatabaseName = "AdventureWorksDW"
$targetDatabaseName = "NewAdventureWorksDW"
In this example, dropped AdventureWorksDW on the instance jovanpop-try-re-create-db in West Central US region will be re-created as NewAdventureWorksDW database. Then, you can run the following script that uses these variables to recreate it:
$db = Get-AzResource -ResourceId "/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Sql/managedInstances/$managedInstanceName/restorableDroppedDatabases" -ApiVersion "2017-03-01-preview" |
Where-Object { $_.Properties.databaseName -eq $deletedDatabaseName } |
Sort-Object -Property @{Expression={$_.Properties.deletionDate}; Ascending = $False} |
Select-Object -First 1
Write-Host "Database $($db[0].Properties.databaseName) created on $($db[0].Properties.creationDate) dropped on $($db[0].Properties.deletionDate)"
This script with fetch the information about the deleted database and display it. Make sure that you get some info by examining $db object:
DOWS\system32> $db
Name : AdventureWorksDW,132034386123300000
ResourceGroupName : cl_recovery
ResourceType : Microsoft.Sql/managedInstances/restorableDroppedDatabases
Location : westcentralus
ResourceId : /subscriptions/......./resourceGroups/cl_pilot/providers/Microsoft.Sql/managedInstances/my-managed-instance/re
storableDroppedDatabases/AdventureWorksDW,132034386123300000
Once you double check that this database is the one that should be restored, you can execute the following script:
$properties = New-Object System.Object
$properties | Add-Member -type NoteProperty -name CreateMode -Value "PointInTimeRestore"
$properties | Add-Member -type NoteProperty -name RestorePointInTime -Value $db.Properties.deletionDate
$properties | Add-Member -type NoteProperty -name RestorableDroppedDatabaseId -Value $db.ResourceId
New-AzResource -Location $location -Properties $properties -ResourceId "subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Sql/managedInstances/$managedInstanceName/databases/$targetDatabaseName" -ApiVersion "2017-03-01-preview" -Force
As a result, new database called NewAdventureWorksDW will be created as a copy of the database AdventureWorksDW that is dropped.
NOTE: In some cases, the dropped date returned by this script might not be the exact date when the database is dropped. Currently, deleted date is the date when the tail log backup is finished; however, this might be up to 5 minutes after the date when you dropped the database. If you know exact date when you dropped the database use that date. Otherwise try to subtract 5min from this date if your restore fails. This is temporary limitation that will be fixed before GA of the feature.
Full script is shown in the following listing:
Login-AzureRmAccount
$subscriptionId = "<subscription id>"
Select-AzSubscription -SubscriptionId $subscriptionId
$resourceGroup = "<resource group>"
$location = "<region>"
$managedInstanceName = "<instance name>"
$deletedDatabaseName = "<database name>"
$targetDatabaseName = "<new name>"
$db = Get-AzResource -ResourceId "/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Sql/managedInstances/$managedInstanceName/restorableDroppedDatabases" -ApiVersion "2017-03-01-preview" |
Where-Object { $_.Properties.databaseName -eq $deletedDatabaseName } |
Sort-Object -Property @{Expression={$_.Properties.deletionDate}; Ascending = $False} |
Select-Object -First 1
Write-Host "Database $($db[0].Properties.databaseName) created on $($db[0].Properties.creationDate) dropped on $($db[0].Properties.deletionDate)"
$properties = New-Object System.Object
$properties | Add-Member -type NoteProperty -name CreateMode -Value "PointInTimeRestore"
$properties | Add-Member -type NoteProperty -name RestorePointInTime -Value $db.Properties.deletionDate
$properties | Add-Member -type NoteProperty -name RestorableDroppedDatabaseId -Value $db.ResourceId
New-AzResource -Location $location -Properties $properties -ResourceId "subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Sql/managedInstances/$managedInstanceName/databases/$targetDatabaseName" -ApiVersion "2017-03-01-preview" -Force
