Επεξεργασία

Κοινή χρήση μέσω

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:

  1. In the Azure portal, on the Access Control (IAM) pane of a storage account, select Add role assignment.

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

  3. On the next page, for Assign access to, select Managed identity.

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