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.
Fig 1 Create database
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.
Fig 3 Restore Point
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.
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.
create table test1 ( i int)
Fig 5 New restore point.
Fig 6 Drop the database
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.
Fig 7 Restore points
Note: A dropped DW database is cleaned-up 8 days after the drop and cannot be restored
That is it!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.