Restoring Azure SQL Datawarehouse

Posted on by piro

Microsoft recently added snapshots to the Azure SQL datawarehouse recovery arsenal. What does it do? A snapshot is like a point-in-time marker, which allows you to restore a datawarehouse to a state recorded at the marker.

Restore points are useful in more than just the traditional bcp scenarios, such as disaster recovery. Restore points can also be used to refresh environments or move point-in-time data from one server to another in different subscriptions.

Normal Azure datawarehouse backups are geo-backups which automatically occur every 8 hours, using snapshots we dont have to wait for the next backup to occur, we are able to restore our defined point-in-time snapshot into a different server and subscription.

First thing, we use the New-AzSqlDatabaseRestorePoint powershell commandlet (in the Az module, the old AzureRm commandlet works too) to create a snapshot

$RestorePointLabel = (New-Guid).Guid
$SrcRestorePoint = New-AzSqlDatabaseRestorePoint -ResourceGroupName $SrcResourceGroup `
-ServerName $SrcServerName -DatabaseName $SrcDbName `
-RestorePointLabel $RestorePointLabel

Next we can choose to restore into a server in the same subscription or into another sub. If the target server is in the the same subscription as the source then we can use the Restore-AzSqlDatabase commandlet


# Get the Database Object
$Database = Get-AzureRmSqlDatabase -ResourceGroupName $SrcResourceGroup -ServerName $SrcServerName -DatabaseName $SrcDbName
# Restore the database
Restore-AzSqlDatabase -FromPointInTimeBackup -PointInTime $SrcRestorePoint.RestorePointCreationDate -ServerName $TrgSqlServerName -TargetDatabaseName $TrgDatabaseName -ResourceId $Database.ResourceID -ResourcegroupName $TrgResourceGroupName

In the event that we want to restore into a server in a different subscription, we have to

  • create a temporary server in the source subscription
  • restore the snapshot from the source server into the temporary server
  • move the temporary server into the target subscription
  • create a new snapshot in the temporary server
  • restore the new snapshot into the target server in the same subscription

Code for this can be found in github.

Leave a comment