1

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?
1

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.

-1

For azure sql databases full log Backups are scheduled to run every 5-10 minutes. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automated-backups So you can issujust assume last available restore point is 10 minutes before current time. In reality it depends on what you are using it for. If you are issuing automated restores I would go for 30min-1hr before current time just to be sure.

  • 1
    Welcome to DBA.SE and thanks for your contribution. An assumption is not the same as knowing. The author of the question is looking for a way to know, where he can retrieve the exact information for the question: "How can I find out LastAvailableBackupDate for point in-time restore?". Your answer is only a reference to the official documentation, which has also been mentioned in the previous answer from @nsb53. How does this differ from the previous answer? How does your answer improve on the other answer given? – hot2use Mar 4 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.