Restore external .bak file to Azure Managed Instance

Question

Restore external .bak file to Azure Managed Instance

HSB 65

We are in the process of setting up an Azure Managed Instance and are testing with it.

In our current process, we get a nightly .bak file from a 3rd party and we automate that and back it up to one of our SQL databases. We have a job in the SQL Server Agent that runs this daily for us.

What are the options/best practices to restore this database in Azure still using this 3rd party .bak file and to have it automated?

SSingh-MSFT 16,371 Reputation points Moderator

2023-07-10T09:12:34.5933333+00:00

Hi
Tim Cavins,

Following up to see if the below suggestions were helpful. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.
HSB 65 Reputation points

2023-07-10T12:10:25.26+00:00

@SSingh-MSFT I am just now going through the responses and will see what may work. @RahulRandive has a response that seems like its what we need. I just need to figure out how to get the .bak file that we receive from our vendor to Azure Storage without using our existing SQL Server as it won't exist once we fully move to Azure.

Accepted answer

1 additional answer

Your answer

SSingh-MSFT 16,371 Reputation points Moderator

2023-07-10T09:12:34.5933333+00:00

Hi
Tim Cavins,

Following up to see if the below suggestions were helpful. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.
HSB 65 Reputation points

2023-07-10T12:10:25.26+00:00

@SSingh-MSFT I am just now going through the responses and will see what may work. @RahulRandive has a response that seems like its what we need. I just need to figure out how to get the .bak file that we receive from our vendor to Azure Storage without using our existing SQL Server as it won't exist once we fully move to Azure.

Answer 1

RahulRandive 10,486 Volunteer Moderator

Hi @HSB

Not sure about any third-party App but I believe you can achieve this within Azure.

You can use a stored proc activity and run the restore command inside that.
Something like this : https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-sample-database-quickstart#restore-from-a-backup-file

Or you can also automate that by creating a Managed Instance and Data Factory/Synapse

You can use the below query in the Script activity of the Data factory

RESTORE DATABASE [dbname] FROM URL = N'https://abcxxxxx.blob.core.windows.net/<>/dbname.bak'

Please find the below screenshot (similar one), select Script Activity and use the linked service for the Managed Instance and place the .bak files in the Storage account and use that path in the query.

User's image

The .bak will be restored once the script activity is executed and that's how you can automate that process using Data Factory.

Thank you!

HSB 65 Reputation points

2023-07-10T14:27:37.66+00:00

@RahulRandive Thank you. Before I can see if your method works well for us, I need to work on copying the .bak file we get nightly to Azure. For that, I've created a storage account and created a new container. I'm working on creating a SAS token that I could use with AZCopy that I could automate through Powershell to send the backup daily. My concern with this is that the SAS token requires an expiration date. I could create one far out into the future. What's the preferred way to do this so we could automate the process without having to modify tokens or anything else and just get the file over to the storage account daily?
HSB 65 Reputation points

2023-07-10T18:57:55.9566667+00:00

@RahulRandive I'm in Data Factory creating the script and I can't connect to the linked service that I have pointing to my Managed Instance using the same username/password that works from SSMS. I believe I need to add an IP address range on my NSG for the managed instance but I'm not sure what that value should be for the integration runtime.

I get the following error:

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'xxx.public.xxxx.database.windows.net,3342', Database: 'xxx', User: 'xxxx'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.), SqlErrorNumber=10060,Class=20,State=0, A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond Activity ID: c078734b-e60d-486d-ab34-109a6a50d02b.
RahulRandive 10,486 Reputation points Volunteer Moderator

2023-07-10T22:50:05.26+00:00

Hi Tim Cavins •

Firstly, make sure you have opened the "Allow access to Azure Service" in firewall setting in Azure SQL.

How are you connecting. Public or private network?

If its public, you may need to add Integration runtime IP address of that region.

Please refer to the link below for more information.

Azure SQL Database connectivity architecture - Azure SQL Database and Azure Synapse Analytics | Microsoft Docs

https://learn.microsoft.com/en-us/azure/virtual-network/service-tags-overview#discover-service-tags-by-using-downloadable-json-files

If you have a private connectivity then, please refer below documentation.

https://learn.microsoft.com/en-us/azure/data-factory/tutorial-managed-virtual-network-sql-managed-instance

Hope this helps.

Please let me know if you have any questions or need additional information.

Thank you!
HSB 65 Reputation points

2023-07-11T12:51:03.4033333+00:00

@RahulRandive I'm looking into the Allow access to Azure Service but I'm only seeing that under Sql Databases and I can't find that for Managed Instances. I have public endpoints enabled and NSG rules limiting inbound access by IP. I'm assuming the IP address is the hiccup. The Data Factory is in US East 2 and using your link, I added all of the IPs and Subnet IPs for US East and US East 2 in my NSG inbound rules for port 3342 and I'm still unable to connect from the Data Factory script.

I have rules set up for the following IPs from the links you sent:

40.121.158.30,40.79.153.12,40.78.225.32,40.79.84.180,52.177.185.181,52.167.104.0,104.208.150.3,40.70.144.193

and Subnets:
104.208.150.192/29,40.70.144.192/29,52.167.104.192/29,20.42.65.64/29,20.42.73.0/29,52.168.116.64/29,20.62.132.160/29

I have even tried adding an inbound rule in the NSG based off a Service Tag but not luck yet.
HSB 65 Reputation points

2023-07-11T15:28:36.5133333+00:00

@RahulRandive I was able to get the connection to work by creating the following inbound rule based on AzureCloud service tag. I don't know if this is allow more than I would like but I can play with it from here and will work on your script to restore a database. I know I need to look at the script to restore the database as a different name than what the .bak shows.
HSB 65 Reputation points

2023-07-11T17:03:34.0133333+00:00

@RahulRandive I'm getting an error on the script when debugging in ADF.

Script:
RESTORE DATABASE [AzureCopy]

FROM URL = N'https://xxxxxx.blob.core.windows.net/xxxx/AzureCopy.bak'

Error:
Cannot open backup device 'https://xxxx.blob.core.windows.net/xxxx/AzureCopy.bak'. Operating system error 86(The specified network password is not correct.). RESTORE DATABASE is terminating abnormally.

Do I need to run anything before the restore statement to create credentials? I've seen that on a few posts.
RahulRandive 10,486 Reputation points Volunteer Moderator

2023-07-11T19:20:13.7533333+00:00

Hi @Tim Cavins,

Yes, you are correct. For error related to system error 86 (The specified network password is not correct.) you need to create credentials.
HSB 65 Reputation points

2023-07-11T19:50:43.7333333+00:00
@RahulRandive I'm not having any luck getting it to work with a credential. Here is what I've done:

Create an access policy with full permissions that expires far out into the future.

Create a SAS token based off of that access policy.

Generate the SAS token for use in creating the credential.

Run the following script to create the credential:

Using SQL creating a credential with the container location:
CREATE CREDENTIAL [https://xxxxx.blob.core.windows.net/xxx]

WITH IDENTITY = 'SHARED ACCESS SIGNATURE'

, SECRET = 'MYSASTOKEN';

Run the following statement but get an error Msg 3201, Level 16, State 2, Line 19

Cannot open backup device 'https://xxxxx.blob.core.windows.net/xxxx/AzureCopy.bak'. Operating system error 5(Access is denied.).

Msg 3013, Level 16, State 1, Line 19

RESTORE FILELIST is terminating abnormally.

RESTORE FILELISTONLY FROM URL = 'https://xxxxxx.blob.core.windows.net/xxxx/AzureCopy.bak'

Try the following statement but get error: error Msg 3225, Level 16, State 1, Line 28

Use of WITH CREDENTIAL syntax is not valid for credentials containing a Shared Access Signature.

Msg 3013, Level 16, State 1, Line 28

RESTORE FILELIST is terminating abnormally.

RESTORE FILELISTONLY FROM URL = 'https://xxxx.blob.core.windows.net/xxx/AzureCopy.bak' with credential = 'https://xxxx.blob.core.windows.net/xxx'

Any suggestions?
RahulRandive 10,486 Reputation points Volunteer Moderator

2023-07-11T21:48:49.9033333+00:00

Hi Tim Cavins •

If you are getting an error 5 (Access Denied), make sure that you have not denied the access to your Azure Blob Storage account using the firewall:

Hopefully below troubleshooting blogs should help you.

Troubleshooting potential backup/restore issues on Azure SQL Managed Instance - Microsoft Community Hub

“Cannot OPEN BACKUP device” error seen when backing up Azure SQL Managed Instance to Azure Blob Storage - Microsoft Community Hub

Thank you!
HSB 65 Reputation points

2023-07-12T13:17:29.57+00:00

@RahulRandive Thank you! I was looking everywhere in Azure except the storage container assuming it was on the Managed Instance side. I had locked it down to our internal IPs. Once I enabled all networks, I was able to run it from SSMS and in Data Factory. I'm just not sure I'm thrilled with having it open, granted people would still need to know the name of the storage and the specific container as well. Ideally, I'd want it locked down and somehow allow managed instance to have access. I've marked your original post as the answer and thanks again for helping me along the way to get to the point I could try it!
RahulRandive 10,486 Reputation points Volunteer Moderator

2023-07-12T13:57:04.0266667+00:00

Hi Tim Cavins •

Thank you very much. Happy to help!

Glad I was able to answer your questions and provide you with an Azure solution for your requirements.
Aleksandar Kuzmov 0 Reputation points

2024-08-16T08:44:00.0066667+00:00

Hi,

Do you have experience with the cost difference (in practical application, all things being equal) between Azure SQL vs. Managed Instance?

Which one would be better ? As in my scenario I only need the Azure SQL Database, to be present for BAK file restoration and then to read from it with ADF or any other method.

Thanks

Answer 2

Alberto Morillo 34,671 MVP Volunteer Moderator

My suggestion is to create a SQL Server Agent job that creates the backup on an Azure Storage account. Here you will find some good tricks to get the best performance with backups on Azure Managed Instance that I doubt a third party tool would consider.

Share via

Restore external .bak file to Azure Managed Instance

1 additional answer

Your answer