Restore a SQL Server backup to Azure SQL Managed Instance

PS 396

What is the best way to restore an SQL Server backup file (.bak) from a storage container to Azure SQL Managed Instance, and overwrite the existing database with the backup? I am looking for the appropriate PowerShell Az command to accomplish this task. Any assistance or relevant information would be highly appreciated. Thank you in advance for your help.

Vinodh247 22,951 Reputation points MVP

2024-01-29T11:06:21.4066667+00:00

Just checking if you had a chance to look at this. if so, can you please update it?
Pds 46 Reputation points

2024-07-19T17:54:48.8333333+00:00

I just added mu question so when you get a chance please help!
Vinodh247 22,951 Reputation points MVP

2024-07-21T10:12:19.46+00:00

Hi Pds: Just checking if you had a chance to look at my response to your followup question. if so, can you please update?

1 answer

Vinodh247 22,951 Reputation points MVP

2024-01-27T11:50:56.04+00:00
Hi PS,

Thanks for reaching out to Microsoft Q&A.

A .bak file is a backup file of a SQL Server database that contains the data and the log files of the database. To restore a .bak file to Azure SQL Managed Instance, you have a few options:

You can use SQL Server Management Studio (SSMS) to restore the database from Azure Blob Storage to Azure SQL Managed Instance by using the restore wizard.

You can first restore the .bak file to a local SQL Server instance, then export the database as a .bacpac file, and then import the .bacpac file to Azure SQL Managed Instance by using the Azure portal or SSMS.

You can't overwrite an existing database during restore.

Hence overwriting is not possible. You can restore to a new name, drop the old database, and rename the new one.

Try using 'Restore-AzSqlDatabase' for restoring azure SQL database using PowerShell command.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.
Please sign in to rate this answer.

1 person found this answer helpful.
Pds 46 Reputation points

2024-07-19T17:54:06.1633333+00:00

Thanks Vinodh.

You have provided very useful information.

I have couple question regarding you have provided the solution:

While running Backup command from SSMS, I am getting following error:

Cannot open backup device 'https://SqlblobStorage.blob.core.windows.net/mssqltest/dbasqldb.bak'. Operating system error 50(The request is not supported.).

Vinodh247 22,951 Reputation points MVP

2024-07-20T00:50:26.1233333+00:00

I hope you are using the sql server 2012 and above.

Seems that there is an issue with accessing the specified backup device in azure blob storage. Make sure that the SQL Server credential used for accessing the blob storage is set up correctly.

CREATE CREDENTIAL [YourCredentialName] WITH IDENTITY = 'your_storage_account_name', SECRET = 'your_storage_account_key';

Confirm that the container where you’re trying to store the backup has the necessary permissions

Ensure that the container is not set to readonly mode

Check if there are any firewall settings or network configurations that might be blocking the connection

make sure that you are using the correct backup command as below:

BACKUP DATABASE [YourDatabaseName] TO URL = 'https://SqlblobStorage.blob.core.windows.net/mssqltest/dbasqldb.bak' WITH CREDENTIAL = 'YourCredentialName';

Vinodh247 22,951 Reputation points MVP

2024-07-27T10:36:01.3433333+00:00

Pds: Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful.

In case if you have any resolution, please share the same here with the community as it can be helpful to others. Else, please respond with more details and we will try to see if we can help further.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Restore a SQL Server backup to Azure SQL Managed Instance

1 answer

Your answer