Share via

Restoring a Complete, Up-To-Date Database Across Azure SQL Managed Instances

sguidos 5 Reputation points
2024-11-11T18:29:03.6833333+00:00

I want to "move" a database from one SQL Managed Instance to another. My plan is to use the Azure Portal to manually:

  1. "Stop" the Web App writing to the database
  2. "Restore" the database to the new SQL Managed Instance
  3. Update the Web App's Connection String to point to the new SQL Managed Instance
  4. "Start" the Web App

My concern is that:

  1. SQL Managed Instances do not let me manually trigger a "final" database backup on the source database
  2. Per all the documentation I can find, the automatic backup occurs "every 5 to 10 minutes"
  3. When I use the Azure Portal to Restore a database, the "Restore Point (UTC)" is always about 6 minutes "behind" the current time

How can I ensure that the Restore operation will contain the complete, up-to-date database "as of" the time I "Stopped" the Web App? Do I wait 12 minutes and pray? That's what the "Q&A Assist" AI Generated Answer says to do, LOL

Azure SQL Database

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mahesh Kurva 805 Reputation points Microsoft Vendor
    2024-11-11T22:47:10.8966667+00:00

    Hi @sguidos,

    Welcome to the Microsoft Q&A and thank you for posting your questions here.

    As I understand your concern about ensuring the database is up to date at the time of the restore.

    Here are some steps to help you achieve this:

    Stop the Web App: This will prevent any new writes to the database, ensuring that no new data is added after you stop the app.

    Wait for the Backup: Since automatic backups occur every 5 to 10 minutes, wait for at least 10 minutes after stopping the Web App to ensure that the latest backup includes all the data up to the point when the app was stopped.

    Check the Latest Backup Time: In the Azure Portal, when you go to restore the database, check the “Restore Point (UTC)” to confirm it reflects a time after you stopped the Web App. This will help ensure that the backup includes all recent changes.

    Restore the Database: Proceed with restoring the database to the new SQL Managed Instance using the latest available restore point.

    Update the Connection String: Once the restore is complete, update the Web App’s connection string to point to the new SQL Managed Instance.

    Start the Web App: Finally, start the Web App to resume operations with the new database instance.

    Additionally, you might want to consider using the database copy and move feature in Azure SQL Managed Instance, which allows for an online move operation with minimal downtime. This feature ensures that all changes from the source database are replicated to the destination database until you manually complete the operation, providing a more seamless transition.

    For more information, please refer the documents:

    https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/automated-backups-overview?view=azuresql

    https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/database-copy-move-how-to?view=azuresql&tabs=azure-portal

    Hope this helps. Do let us know if you any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.