11

I'm setting up an Azure SQL DB for our Web App. We have enabled Point In Time Retention (PITR) and Long Term Retention (LTR). Our process is to keep backups for 1 year.

Periodically, we need to upgrade the DB by applying SQL scripts. Sometimes there is a problem with the upgrade scripts and the upgrade fails. We need to rollback the database to the previous version.

To rollback the DB I tried the restore feature. However, the restore feature seems to only create new DBs; therein lies the problem. Restoring to a new DB and removing the old one works great, but we lose all our backup history. It appears backups are tied to the DB (probably to the ResourceId).

So, how can I use Azure SQL DB and periodically restore a DB and still maintain all the back up history?

Improve this question
3
  • Have you found any good solutions for maintaining backup history on restored database instances? Commented Apr 13, 2020 at 14:19
  • I have created a community request for this feature: feedback.azure.com/forums/217321-sql-database/suggestions/… Commented Apr 13, 2020 at 14:32
  • From what I am finding, LTR backups will be retained for deleted databases. So, if you rename your new database with the old database name, your LTR backups will continue to be created and queriable (only via powershell though) by the original database name. Point in time restores appear to be limited to the lifetime of the database, as they use differential backups and transaction logs to perform restores. It is also worth noting that the Azure portal will not do all this hard work for you, you must use PowerShell's Az module. Commented Apr 13, 2020 at 16:39

2 Answers 2

Reset to default
9
Answer recommended by Microsoft Azure Collective

Unfortunately, restoring from a backup in Azure SQL Database always creates a new database. The secret here may be to rename the newly restored database with the name of the original database. You will even see that the restored database once renamed it then shows all the security recommendations, automatic tuning recommendations of the original database.

So delete existing database, restored the database, and rename it as the original database.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Yes, this is what I have implemented as a "close" solution. It leaves a small backup hole: if the customer upgrades and then needs to revert to a version of the DB after the last LTR was taken, they can't :-(. Thanks.
Thank you for the information. This would definitely be a useful feature to migrate existing backups into a recently restored database. Maintaining a collection of deleted databases makes listing backups to our customers difficult.
@Greg just to confirm. if you delete the existing database "db", restore the database from deleted to "dbnew" and the rename "dbnew" to "db", the backup history will be tied to what's now "db". Is that Correct?
I just had to do this exactly as described (restore, delete original, rename backup to original). What is weird: In SQL Server > Backups > Available LTR backups it DOESN'T list any backup available ("None"). BUT: when clicking on restore I can still see my LTR backups🤷‍♂️ Also I had to reapply my backup policy. Let me know if there's a better solution to restore backups.
4

You can reference this document Recover an Azure SQL database using automated database backups , it gives the answer that all the recover are creating new database.

By default, SQL Database backups are stored in geo-replicated blob storage (RA-GRS). The following options are available for database recovery using automated database backups: Create a new database on the same SQL Database server recovered to a specified point in time within the retention period.

  • Create a database on the same SQL Database server recovered to the deletion time for a deleted database.
  • Create a new database on any SQL Database server in the same region recovered to the point of the most recent backups.
  • Create a new database on any SQL Database server in any other region recovered to the point of the most recent replicated backups.

If you configured backup long-term retention, you can also create a new database from any LTR backup on any SQL Database server.

improtant:

You cannot overwrite an existing database during restore.

"So, how can I use Azure SQL DB and periodically restore a DB and still maintain all the back up history?"

You can use Database replacement:

If the restored database is intended as 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 using the ALTER DATABASE command in T-SQL.

Hope this helps.

Improve this answer

2 Comments

Thanks, I looked at this, but the backup history is tied to the original database, so it'll move with old DB, so the NEW db will not have any backup history even though it'll have the name of the original DB.
Hi @Greg, I have an idea that the new database still has the feature of auto backup, it will have new backup history. Don't delete the original DB, using the backup to create a new database manually, then using the new DB to work with your app. All the backup history still exist. But it's not a good solution. As I know about Azure sql database, it can not help you achieve your purpose for now.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.