Azure SQL Managed Instance - How does the new database created in the portal reference the restored database from on-prem?

Scott Evans 0 Reputation points
Oct 10, 2024, 10:00 AM

Hi All,

I`m in the process of migrating SQL databases from on-premise to Azure SQL Managed instance. I have followed the "back up and restore" outlined here "https://video2.skills-academy.com/en-us/data-migration/sql-server/managed-instance/guide". 

I have started with a test db, created a new managed instance database and restored the db from the storage account URL and I can see the storage utilisation has noted the size of the imported db and all tests are good in regards to the SQL db and application.

How does the manually created managed instance database reference the copied database from on-premise? As tests have now been successful we are looking to migrate the production db from on-premise to a newly created managed instance db I have created for production. In regards to backup retention, how does the managed instance database know which db to back up depending on the configured retention policy? (which will be different between test and production).

Thank you,

Scott

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,254 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Mahesh Kurva 1,625 Reputation points Microsoft Vendor
    Oct 10, 2024, 3:01 PM

    Hi @Scott Evans,

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

    How does the manually created managed instance database reference the copied database from on-premise?

    Since your test migration was successful, when you restore a database from an on-premises SQL Server instance to a SQL Managed Instance, the restored database is a new database in the SQL Managed Instance. The restored database is not linked to the original database in any way.

    To migrate your production database to the newly created managed instance database, you can follow the same process you used for the test database. You will need to create a new managed instance database, restore the production database from the storage account URL, and then update your application to point to the new managed instance database.

    Backup retention, how does the managed instance database know which db?

    Regarding backup retention, the managed instance database knows which database to back up based on the database name. When you configure the backup retention policy, you can specify the retention period for each database individually. This means that you can have different retention policies for your test and production databases.

    I would request you to refer the below mentioned links for more information.

    https://video2.skills-academy.com/en-us/azure/azure-sql/managed-instance/long-term-backup-retention-configure?view=azuresql&tabs=portal.

    https://video2.skills-academy.com/en-us/azure/azure-sql/managed-instance/automated-backups-overview?view=azuresql.

    https://video2.skills-academy.com/en-us/azure/azure-sql/managed-instance/automated-backups-overview?view=azuresql.

    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.

    1 person found this answer helpful.

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.