Restore a database to SQL Server 2022 from Azure SQL Managed Instance
Applies to:
Azure SQL Managed Instance
This article describes how to restore a database backup from Azure SQL Managed Instance to SQL Server 2022.
Overview
The database format alignment between SQL Managed Instance and SQL Server 2022 gives you an easy way to copy or move databases from your managed instance to an Enterprise, Developer, or Standard edition of SQL Server 2022 hosted on-premises, on virtual machines in Azure, or in other clouds.
Restoring databases from managed instances to SQL Server 2022 instances unlocks the following scenarios:
- Ensures database mobility between SQL Managed Instance and SQL Server-based products.
- Provides database copies to customers and other eligible parties.
- Refreshes environments outside SQL Managed Instance.
The ability to restore copy-only full backups of databases from SQL Managed Instance to SQL Server 2022 is available by default in all existing and any new deployed instances.
Important
The ability to restore copy-only full backups of databases from SQL Managed Instance to SQL Server 2022 will be available until the end of mainstream support for SQL Server 2022.
Take a backup on SQL Managed Instance
First, create a credential to access the storage account from your instance, take a copy-only backup of your database, and then store it.
You can create your credential by using a managed identity or a shared access signature (SAS) token.
A managed identity is a feature of Microsoft Entra ID (formerly Azure Active Directory) that provides instances of Azure services, such as Azure SQL Managed Instance, with an automatically managed identity in Microsoft Entra ID, the system-assigned managed identity.
You can use this identity to authorize requests for data access to other Azure resources, including storage accounts. Services such as Azure SQL Managed Instance have a system assigned managed identity, and can also have one or more user-assigned managed identities. You can use either system-assigned managed identities or user-assigned managed identities to authorize the requests.
Before the Azure storage administrator writes a backup file to a storage account, they must grant permissions to the managed identity to write the data. Granting permissions to the managed identity of the instance is done the same way as granting permissions to any other Microsoft Entra user. For example:
In the Azure portal, on the Access Control (IAM) pane of a storage account, select Add role assignment.
Select the Storage Blob Data Contributor built-in Azure role-based access control (RBAC) role. This provides read/write access to the managed identity for the necessary Azure Blob Storage containers.
Instead of granting the managed identity the Storage Blob Data Contributor Azure RBAC role, you can grant more granular permissions. To learn more, see Set ACLs in Azure Data Lake Storage Gen2.
On the next page, for Assign access to, select Managed identity.
Choose Select members and then, in the Managed identity dropdown list, select the appropriate managed identity. For more information, see Assign Azure roles by using the Azure portal.
Now, creating the database-scoped credential for managed identity authentication is simple.
In the following example, note that Managed Identity
is a hard-coded string, and you need to replace the generic storage account name with the name of the actual storage account:
CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<containername>]
WITH IDENTITY = 'MANAGED IDENTITY'
Next, take a COPY_ONLY
backup of your database by running the following sample T-SQL command:
BACKUP DATABASE [SampleDB]
TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<containername>/SampleDB.bak'
WITH COPY_ONLY
Restore to SQL Server
Restore the database to SQL Server by using the WITH MOVE
option of the RESTORE DATABASE T-SQL command and providing explicit file paths for your files on the destination server.
To restore your database to SQL Server, run the following sample T-SQL command with file paths appropriate to your environment:
RESTORE DATABASE [SampleDB]
FROM URL = 'https://<mystorageaccountname>.blob.core.windows.net/<containername>/SampleDB.bak'
WITH
MOVE 'data_0' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\SampleDB_data_0.mdf',
MOVE 'log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\SampleDBlog.ldf',
MOVE 'XTP' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\SampleDB_xtp.xtp'
Note
To restore databases that are encrypted at rest by using Transparent Data Encryption (TDE), the destination instance of SQL Server must have access to the same key that's used to protect the source database through the SQL Server Connector for Azure Key Vault. For details, review Set up SQL Server TDE with AKV.
Considerations
When you're restoring a database to SQL Server, consider the following:
- You must use the
WITH MOVE
qualifier and provide explicit paths for the data files. - Databases that are encrypted with service-managed TDE keys can't be restored to SQL Server. You can restore an encrypted database to SQL Server only if it was encrypted with a customer-managed key and the destination server has access to the same key that's used to encrypt the database. For more information, see Set up SQL Server TDE with Azure Key Vault.
- This capability is only available to instances with the SQL Server 2022 update policy. You will not be able to restore your database backup to SQL Server 2022 from an instance with the Always up to date update policy.
Next steps
- To learn how to create your first managed instance, see Quickstart guide.
- For a features and comparison list, see SQL common features.
- For more information about virtual network configuration, see SQL Managed Instance virtual network configuration.
- For a quickstart that creates a managed instance and restores a database from a backup file, see Create a managed instance.
- For a tutorial about using Azure Database Migration Service for migration, see SQL Managed Instance migration using Database Migration Service.
- For advanced monitoring of SQL Managed Instance database performance with built-in troubleshooting intelligence, see Monitor Azure SQL Managed Instance by using Azure SQL Analytics.
- For pricing information, see SQL Database pricing.