CODEWITHSUNDEEP
azureazure-sql-databasedatabase-backups
Adamon
Adamon

Reputation: 514

Azure SQL database backups and restore

We are preparing our DR plan for an Azure-hosted application and I have come across some confusing wording in the database restore documentation.
As I understand it, log backups are taken roughly every 5 minutes and these are geo-redundant, but when I do a restore with the -FromGeoBackup flag, the most recent backup is older than that.
I can do a point in time restore using a more recent backup but as I understand it, that can only be done on the same server.
What if our primary data center goes down and we need to restore to a new server in a new data center?
Is the only way to do this via -FromGeoBackup with a roughly 1-hour gap in data or can I use the geo-redundant log backups to get a more recent version?
This is using a Standard tier database if that's relevant.

Upvotes: 1

Views: 932

Answers (2)

Alberto Morillo
Alberto Morillo

Reputation: 15698

Yes, if you are not using Failover Groups\Geo-replication then you have the default recovery option of Geo-restore (Restore-AzureRmSqlDatabase -FromGeoBackup). There is a delay between when a backup is taken and when it is geo-replicated to an Azure blob in a different region. As a result, the restored database can be up to one hour behind the original database. With Geo-restore the RTO is 12 h and the RPO is 1 h.

If restoring a database to an hour earlier can result in business liability or your organization cannot afford that, you should be using failover groups. This enables your database (and applications) to quickly restore availability in a different region in case of an outage.

With Auto-failover groups the RTO is 1 h ( to ensure that the failover is justified by the scale of the outage) and RPO 5s. With manual database failover, however, the RTO is 30 s and the RPO is 5 s.

Upvotes: 1

Leon Yue
Leon Yue

Reputation: 16431

I think Point-in-time restore -FromPointInTimeBackup maybe more suitable you.

You can restore a standalone, pooled, or instance database to an earlier point in time by using the Azure portal, PowerShell, or the REST API. The request can specify any service tier or compute size for the restored database. Ensure that you have sufficient resources on the server to which you are restoring the database. When complete, the restore creates a new database on the same server as the original database. The restored database is charged at normal rates, based on its service tier and compute size. You don't incur charges until the database restore is complete.

You generally restore a database to an earlier point for recovery purposes. You can treat the restored database as a replacement for the original database, or use it as a data source to update the original database.

Database replacement

  • If you intend the restored database to be a replacement for the original database, you should specify the original database's compute size and service tier. You can then rename the original database, and give the restored database the original name by using the ALTER DATABASE command in T-SQL.

Data recovery

  • If you plan to retrieve data from the restored database to recover from a user or application error, you need to write and execute a data recovery script that extracts data from the restored database and applies to the original database. Although the restore operation may take a long time to complete, the restoring database is visible in the database list throughout the restore process. If you delete the database during the restore, the restore operation will be canceled and you will not be charged for the database that did not complete the restore.

    Reference: Restore-AzSqlDatabase

Hope this helps.

Upvotes: -1

Related Questions