First published on MSDN on Oct 16, 2017
While majority of the time when restoring a deleted database you will want to select the time it was deleted as the restore point, there is however the ability to restore a deleted database to a Point in Time prior to the deletion. With all Azure SQL DB offerings, there is a retention of backups that can be used for Point in Time Restore. The same restore points are able to be used for a deleted database when using the delete restore process. You can review the below articles for more information about Point in Time Restore and retention periods for different tiers.
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automated-backups#how-long-do-y...
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-recovery-using-backups#point-in...
Here is an example using the
Restore-AzureRmSqlDatabase
Azure Powershell cmdlet.
$ResourceGroup = "ResourceGroup01"
$ServerName = "Server01"
$DatabaseName = "Database01
$RestoredDatabaseName = "Database01_Restored"
$DBEdition = "Standard"
$DBTier = "S2"
$RestoreTime = "2017-10-10 12:00"
$DeletedDatabase = Get-AzureRmSqlDeletedDatabaseBackup -ResourceGroupName $ResourceGroup -ServerName $ServerName -DatabaseName $DatabaseName
Restore-AzureRmSqlDatabase -FromDeletedDatabaseBackup -DeletionDate $DeletedDatabase.DeletionDate -ResourceGroupName $DeletedDatabase.ResourceGroupName -ServerName $DeletedDatabase.ServerName -TargetDatabaseName $RestoredDatabaseName -ResourceId $DeletedDatabase.ResourceID -Edition $DBEdition -ServiceObjectiveName $DBTier -PointInTime $RestoreTime"
In the above example I am going to restore the deleted database 'Database01' to 'Database01_Restored' at an S2 tier from the Point in Time of 2017-10-10 12:00 UTC. Database was dropped on 2017-10-15 23:26 UTC in my case. The restore process can take some time depending on multiple factors such as database size but once completed, you should have your deleted database restored to the time requested. If you do want to use the drop time for the restore, simply drop the -PointInTime $RestoreTime portion of the above Restore-AzureRmSqlDatabase command.
If you provide a timeframe that is not a valid Point in Time for the database you will be presented with an error showing the valid timeframes that can be chosen, example:
The specified point in time, '10/10/2016 12:00:00', is not valid for database 'Database01'. Valid points in time should be between '10/08/2017 17:00:19' and '10/15/2017 23:26:33' inclusive.
To see a list of all restorable databases for a server you can run the following:
$ResourceGroup = "ResourceGroup01"
$ServerName = "Server01"
Get-AzureRmSqlDeletedDatabaseBackup -ResourceGroupName $ResourceGroup -ServerName $ServerName
The portal currently only allows restoring to when the database was dropped. Using the Azure Powershell cmdlets is the only way to restore a deleted database to a specific point in time as of today. This feature is intended to be provided in the portal in the future.
