Azure SQL Database finding latest available restore point

azureazure-sql-databasebackuprestore

From reading books online it is clear that there are two types of retention for Azure SQL Database.

  • Backups for point-in-time restore
  • Backups for long-term retention

When it comes to restore there are multiple options.

  • Point-in-time restore
  • Deleted database restore
  • Geo-restore

When I think of Geo-Restore the latest restore point cannot be same as Point in-time restore as backup has to be copied in a different region/data center.

There is a delay between when a differential backup is taken and when
it is geo-replicated to an Azure blob in a different region. This
delay can be up to an hour, so, if a disaster occurs, there can be up
to one hour data loss.

To find the latest restore point for Geo backup I can use Get-AzureRmSqlDatabaseGeoBackup command.

Question:

  • How can I find out LastAvailableBackupDate for point in-time restore?

Best Answer

You can use Get-AzureRmSqlDatabaseRestorePoints to get the earliest possible restore point for the db. The output from this command is below: 

ResourceGroupName        : 
ServerName               : 
DatabaseName             : testdb
Location                 : East US
RestorePointType         : CONTINUOUS
RestorePointCreationDate : 
EarliestRestoreDate      : 2/23/2019 1:53:30 AM
RestorePointLabel        :

Unfortunately, this command does not give you the latest restore point.

According to Microsoft the transaction log backups generally occur every 5 - 10 minutes. I attempted to do a test restore with the recovery point 2 minutes behind the current time and got the following error:

Choose a restore point between earliest restore point and latest backup time which is 6 minute before current time.

Related Question