Restore a SQL Server Database on Azure SQL Managed Instance

Summary

An Azure SQL Managed Instance (MI) provides a complete SQL Server instance hosted in Azure. It is built specifically to bridge the gap between traditional on-premises environments and the cloud. It operates at the instance level, allowing you to host, group, and manage multiple databases together under one roof.

It is deployed directly into an isolated, dedicated Azure Virtual Network (VNet) with a private IP address, providing native network security and isolation. Azure SQL Managed Instances are best suited for "Lift-and-shift" migrations. If you have legacy applications running on an on-premises SQL Server and want to migrate to Azure without rewriting application code, MI provides near 100% feature compatibility.

In an on-premises deployment of SQL Server, the database backups are read directly from local disks or network drives to restore their content on a SQL Server Instance. However, in an Azure SQL Managed Instance, the process of database restoration is directly performed from an Azure Blob Storage via the RESTORE FROM URL command:

Below is a complete overview of the steps involved in restoration workflow:

1. Upload the database backup file (.bak) to an Azure Storage Container.
2. Generate a Shared Access Signature (SAS) token for the storage container.
3. Create a credential inside Azure SQL Managed Instance referencing the storage container.
4. Execute the RESTORE DATABASE FROM URL command.

Procedure

1. Prepare the Azure Blob Storage and generate a Shared Access Signature (SAS) Token:
   1. Create a storage account on Azure by clicking the Storage accounts service in the Azure portal.

2. Once the storage account is created, proceed to create a container inside the same by choosing the Containers option under Data storage.

3. Upload the (.bak) file of the database to the respective Azure blob storage container.

4. Generate a shared access signature (SAS) token for your respective container.
   1. Click the Shared access tokens option.
   2. Choose appropriate Permissions required for reading the file.
   3. Set an appropriate expiry date and time.
   4. Click Generate SAS token and URL and copy the Blob SAS token.

2. Create Database Credentials on the Azure SQL Managed Instance:
   1. Connect with your Azure SQL Managed Instance using an administrative account from SQL Server Management Studio or Azure Data Studio.
   2. Execute the below-mentioned SQL query against the master database to create a credential that allows Azure SQL Managed Instance to read from the Blob Container:

-- Replace <StorageAccountName>, <ContainerName>, and <YourSASToken>
IF NOT EXISTS (
    SELECT * FROM sys.credentials
    WHERE name = 'https//<StorageAccountName>.blob.core.windows.net/<ContainerName>'
)
BEGIN
    CREATE CREDENTIAL [https//<StorageAccountName>.blob.core.windows.net/<ContainerName>]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
         SECRET = '<YourSASTokenWithoutLeadingQuestionMark>';
END;
GO

3. Restoring the Database:
   1. Before restoring the database, execute the below query to check if Azure SQL Managed Instance can read the files and check the database name:

RESTORE HEADERONLY
FROM URL = 'https//<StorageAccountName>.blob.core.windows.net/<ContainerName>/<YourBackupFileName>.bak';
GO

2. Execute the below SQL query to restore the database on Azure SQL Managed Instance:

RESTORE DATABASE [YourRestoredDatabaseName]
FROM URL = 'https//<StorageAccountName>.blob.core.windows.net/<ContainerName>/<YourBackupFileName>.bak';
GO

3. Navigate to Databases under the Data Management section of your Azure SQL Managed Instance and check if the database has been restored or not, as shown in the next image: