Jovan Popovic
Mar 23 · 2 min read

In most of the cases if you loose your data you business stops. If you loose your database all applications that are using it will stop working. Therefore, you always need to have some disaster recovery strategy that can ensure that you can recover your data even if it is dropped.

Azure SQL Database — Managed Instance is fully-managed PaaS SQL Server Database Engine that provides advanced disaster-recovery capabilities. Even if you accidentally drop the database or someone drops your database as part of security attack, Managed Instance will enable you to easily recover the dropped database.

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. Just make sure that you have the following prerequisites:

  • PowerShell version >= 5.1.17763.134
  • AzureRM.Resources version >= 6.7.3

I had some hard-to-debug problems with executing this script on a machine that has lower version of AzureRM library so it is better not to experiment with earlier versions.

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-AzureRmAccount
$subscriptionId = "cd827379-9270-0791-....."
Select-AzureRmSubscription -SubscriptionId $subscriptionId

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 = "jovanpop-try-re-create-db"
$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-AzureRmResource -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-AzureRmResource `
-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.

Azure SQLDB Managed Instance

Stories about Azure SQL Database Managed Instance - Fully-managed SQL Server hosted in Azure cloud

Jovan Popovic

Written by

Azure SQLDB Managed Instance

Stories about Azure SQL Database Managed Instance - Fully-managed SQL Server hosted in Azure cloud

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade