Azure SQL database restore to Synapse

Andrea Galardi 0

we need to do restore sql server database from restore point to dedicated sql pool on synapse on same resource group we are trying to do restore azure sql server db on same restore group where we have synapse but it ends up with error ERROR1.JPG

Command use for restore:

Restore-AzSynapseSqlPool -FromRestorePoint -RestorePoint $restorePoint.RestorePointCreationDate -Name "NEW_NAME" -ResourceGroupName "TEST" -WorkspaceName "WSNAME" -ResourceId "SUBSCRIPTION_NAME" -PerformanceLevel DW300c

Do u have any advice out to proceed?

Erland Sommarskog 111.6K Reputation points MVP

2024-09-11T15:06:30.96+00:00

I may be misreading your question, but it sounds to me that you are trying to restore a backup from one platform. Azure SQL Database, to a completely different one, Azure Synapse Analytics Dedicated Pool. I would not expect that to work, since they are very different products (although the common surface area of T-SQL may make them look similar).
Andrea Galardi 0 Reputation points

2024-09-12T07:37:54.8233333+00:00

Hi Erland yes you understand it well, our task is following: Refresh SQL Synapse dedicated poool from PROD to TEST env. this is not possible directly in our case as PROD and TEST are on different subscriptions. What we did so far: made restore point on PROD synapse, restored this to new SQL server on prod subs. then moved SQL server to our TEST subscription, created new restore point on SQL server. Now we need to restore it to Synapase on PROD which is now on same resource group but we are getting errors: Error type Ctrl+C to copy Copy to clipboard

The client 'XXXXXXX-6ca8-4a6f-9f71-XXXXXXXXXXX' with object id 'XXXXXXXX-6ca8-4a6f-9f71-XXXXXXXX' has permission to perform action 'Microsoft.Sql/servers/databases/write' on scope '/subscriptions/XXXXXXX-6a34-41d1-9523-XXXXXXXXXX/resourceGroups/synapseworkspace-managedrg-XXXXXXX-eb6a-XXXXX-b0b3-XXXXXXXXX/providers/Microsoft.Sql/servers/datplattestsynapse/databases/restore'; however, it does not have permission to perform action(s) 'Microsoft.Sql/servers/databases/write' on the linked scope(s) '/subscriptions/XXXXXX-6a34-XXXXXX-9523-XXXXXXXXX/resourceGroups/TEST-XXX-XXXX/providers/Microsoft.Sql/servers/migrationtotest/databases/XXXXXX' (respectively) or the linked scope(s) are invalid. (Code: LinkedAuthorizationFailed)

BTW: we are owners of owners in whole subscription, so there should not be any permission issue. We followed this articles: https://www.spyglassmtg.com/blog/azure-synapse-cross-subscription-refresh https://dataonwheels.wordpress.com/2021/06/01/moving-synapse-databases-between-subscriptions-practical-guidance/
Mahesh Kurva 245 Reputation points Microsoft Vendor

2024-09-13T11:18:13.1266667+00:00
Hi @Andrea Galardi,

Here are the troubleshooting steps that might help:

The error message indicates that while you have permissions to write to the datplattestsynapse database, you don't have the required permissions on the migrationtotest SQL Server in your TEST subscription. You need to have Microsoft.Sql/servers/databases/write permissions on all relevant scopes, including the source and destination resources.

Ensure that the user or service principal performing the operation has the necessary roles and permissions. The required roles might include Contributor or a custom role with permissions to manage SQL databases and servers.

The error message indicates a problem with linked scope authorization. Ensure that there are no issues with the linked server or database and that it is correctly configured to allow the restore operation.

Please refer to the document, as it might be helpful: Cross-Subscription Restore for Dedicated SQL Pools.

I hope this information helps. Please do let us know if you have any further queries.
Erland Sommarskog 111.6K Reputation points MVP

2024-09-13T21:54:43.2566667+00:00

We followed this articles: https://www.spyglassmtg.com/blog/azure-synapse-cross-subscription-refresh https://dataonwheels.wordpress.com/2021/06/01/moving-synapse-databases-between-subscriptions-practical-guidance/

That sounded so crazy, so I had to try it for my own enlightenment. The first link of yours was not available, but I looked at the second one. But when I come to the restore page, it asks for a workspace, and not a server. And all that is listed is my Synapse workspace.

I guess that I am missing something.

My apologies for hijacking your original question to clear out my own confusion.
Erland Sommarskog 111.6K Reputation points MVP

2024-09-14T10:42:46.3533333+00:00

OK, got it. I see in the blade for my Azure SQL Server, there is an option to create a dedicated pool:

I was only aware of the option to create a dedicated pool in a Synapse workspace. Obviously, if you create your Synapse database the way above, you can restore it to another Azure SQL Server.

Again, my apologies for being of absolutely zero help with your question.
Mahesh Kurva 245 Reputation points Microsoft Vendor

2024-09-16T10:50:44.2466667+00:00

Hi @Andrea Galardi,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Erland Sommarskog 111.6K Reputation points MVP

2024-09-16T20:29:16.8+00:00

Just checking in to see if the below answer provided by @Erland Sommarskog helped.

Mahesh, have you actually read my miserable posts? I try to help people, but this time I really made a fool of myself.
Mahesh Kurva 245 Reputation points Microsoft Vendor

2024-09-17T10:54:18.68+00:00

Hi @Andrea Galardi,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Priv - Molly Vander Velde 0 Reputation points

2024-09-17T16:00:43.2633333+00:00

We were previously able to do this and it stopped working recently (with the error above that you received as well). Support hasn't been much help yet, so please post if you are able to resolve this! I am trying everything, but haven't had any luck working around the permissions error, even though I am owner on all of the objects (resource groups, sql pools, etc.)
Mahesh Kurva 245 Reputation points Microsoft Vendor

2024-10-03T12:33:43.77+00:00

Hi @Andrea Galardi,

Have you tried the solution mentioned by @Asanka Padmakumara. If it's not working for you, please let us know so we can offer further assistance.

1 answer

Asanka Padmakumara 11 Reputation points

2024-10-01T23:41:10.3433333+00:00

We had the same issue and fixed using following steps.

Add the "Microsoft Azure Synapse Resource Provider" system enterprise app as the "SQL DB Contributor" to the destination resource group and to the intermediate SQL server. It seems even though we use personal credentials, behind the seen it uses system app for restore purpose.
Please sign in to rate this answer.

2 people found this answer helpful.
Molly VanderVelde 51 Reputation points

2024-10-03T16:18:03.0366667+00:00

This worked for us as well - thank you!
Sign in to comment

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

Share via

Azure SQL database restore to Synapse

1 answer

Your answer