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

Question

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://learn.microsoft.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

Answer 1

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://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/long-term-backup-retention-configure?view=azuresql&tabs=portal.

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/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.