This article is contributed. See the original author and article here.
I made this as a quick lab based on the public documentation to help address this doubt about restoring a deleted database on Azure.
This is a common customer doubt.
- Create a new DW database and change the performance level to the minimum, this is just a lab as you can check on fig. 1.
Fig 1 Create database
- Connect on SQL Server Management Studio and check the last backup by running the following:
select * from sys.pdw_loader_backup_runs
Fig. 2 No backups
It is completely empty as there is no backup for this database, yet. So, let’s force a backup to be done by creating a restore point.
- Define a new restore point on the Azure Portal by clicking on… New Restore Point Fig 3.
Fig 3 Restore Point
- Connect on SQL Server Management Studio again and check the lastest backups by running again pdw_loader_backup_run as you can confirm on fig 4:
Fig 4 One backup
By the docs:
A data warehouse restore is a new data warehouse that is created from a restore point of an existing or deleted data warehouse. Restoring your data warehouse is an essential part of any business continuity and disaster recovery strategy because it re-creates your data after accidental corruption or deletion.
Doc: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/backup-and-restore
SQL DW will create 42 restore points and the user can additionally create more 42 restore points. Basically this is a restore to a point in time.
- Let’s add one more restore point.
- Create a table like the script below and then add a new restore point. You should get the results as shown in Fig 5.
create table test1 ( i int)
Fig 5 New restore point.
- Drop the database from Azure Portal as fig 6.
Fig 6 Drop the database
- Restore the database:
- Open PowerShell.
- Connect to your Azure account and list all the subscriptions associated with your account.
- Select the subscription that contains the deleted SQL pool to be restored.
- Get the specific deleted data warehouse.
Add the details and run on power shell:
$SubscriptionName="<YourSubscriptionName>"
$ResourceGroupName="<YourResourceGroupName>"
$ServerName="<YourServerNameWithoutURLSuffixSeeNote>" # Without database.windows.net
#$TargetResourceGroupName="<YourTargetResourceGroupName>" # uncomment to restore to a different server.
#$TargetServerName="<YourtargetServerNameWithoutURLSuffixSeeNote>"
$DatabaseName="<YourDatabaseName>"
$NewDatabaseName="<YourDatabaseName>"
Connect-AzAccount
Get-AzSubscription
Select-AzSubscription -SubscriptionName $SubscriptionName
# Get the deleted database to restore
$DeletedDatabase = Get-AzSqlDeletedDatabaseBackup -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName
# Restore deleted database
$RestoredDatabase = Restore-AzSqlDatabase –FromDeletedDatabaseBackup –DeletionDate $DeletedDatabase.DeletionDate -ResourceGroupName $DeletedDatabase.ResourceGroupName -ServerName $DeletedDatabase.ServerName -TargetDatabaseName $NewDatabaseName –ResourceId $DeletedDatabase.ResourceID
# Use the following command to restore deleted data warehouse to a different server
#$RestoredDatabase = Restore-AzSqlDatabase –FromDeletedDatabaseBackup –DeletionDate $DeletedDatabase.DeletionDate -ResourceGroupName $TargetResourceGroupName -ServerName $TargetServerName -TargetDatabaseName $NewDatabaseName –ResourceId $DeletedDatabase.ResourceID
# Verify the status of restored database
$RestoredDatabase.status
Simple like that! You can also use the portal for it, look at the public documentation for more details.
- If you run again sys.pdw_loader_backup_runs after the restore you will see the restore points as the user drop database and the latest backups:
Fig 7 Restore points
Note: A dropped DW database is cleaned-up 8 days after the drop and cannot be restored
That is it!
Liliam
UK Engineer.
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments