Restore Azure SQL Managed Instance DB from One Server to Another

PS 376 Reputation points
2023-11-27T00:02:11.7666667+00:00

All,

I need to automate the restoration of a few databases from a higher environment to a lower environment in Azure SQL Managed Instance. The databases are hosted in two different resource groups under the same subscription. Is there an out-of-the-box feature or built-in functionality to accomplish this? Please provide any resources or advice to help me create a suitable solution.

Thank you!

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.
12,756 questions
.NET CLI
.NET CLI
A cross-platform toolchain for developing, building, running, and publishing .NET applications.
323 questions
PowerShell
PowerShell
A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
2,081 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Javier Villegas 895 Reputation points MVP
    2023-11-27T00:21:05.7466667+00:00

    Hi PS

    I do have the same situation and I just handle it by taking the backup on the prod MI and then Restore it to the lower env using PowerShell Restore-AzSqlInstanceDatabase

    Check here

    Restore a database from a backup in Azure SQL Managed Instance

    Regards

    Javier

    1 person found this answer helpful.
    0 comments
  2. ShaktiSingh-MSFT 13,436 Reputation points Microsoft Employee
    2023-11-27T05:06:33.41+00:00

    Hi PS •,

    Welcome to Microsoft Q&A forum.

    As I understand, you want to perform restoration of Azure SQL Managed Instance from one server to another in the same subscription.

    Please refer to the below documentations which has detailed explanation on how we can perform restore of Azure SQL MI:

    https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/recovery-using-backups?view=azuresql&tabs=azure-portal

    https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-sample-database-quickstart?view=azuresql

    https://stackoverflow.com/questions/60427576/restoring-database-in-azure-managed-instance-from-one-subscription-to-another

    Consider the following limitations when working with backups and Azure SQL Managed Instance:

    • Geo-restore of a database can only be performed to an instance in the same subscription as the source SQL managed instance.
    • SQL Managed Instance databases can only be restored to SQL Server 2022 (either on-premises, or on a virtual machine) if the source SQL Managed Instance has enrolled in the November 2022 feature wave.
    • SQL Managed Instance databases are encrypted with TDE by default. When the source database uses a customer-managed key (CMK) as the TDE protector, to restore your database to an instance other than the source SQL Managed Instance, the target instance must have access to the same key used to encrypt the source database in Azure Key Vault, or you must disable TDE encryption on the source database before taking the backup.
    • You can only track the progress of the restore process by using the sys.dm_exec_requests and sys.dm_operation_status dynamic management views.
    • When service endpoint policies are enabled on Azure SQL Managed Instance, placing a service endpoint policy on a subnet prevents point-in-time restores (PITR) from instances in different subnets.

    Let us know if you face any issue in doing the same.

    Thanks

    1 person found this answer helpful.
    0 comments