This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(48, 80%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAG%3C/text%3E%3C/svg%3E)
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?
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).
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/
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(252.8, 60%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMK%3C/text%3E%3C/svg%3E)
Hi @Andrea Galardi,
Here are the troubleshooting steps that might help:
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.
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.
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.
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.
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.
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(9.6, 34%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EPM%3C/text%3E%3C/svg%3E)
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.)
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(300.8, 16%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAP%3C/text%3E%3C/svg%3E)
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(291.2, 38%, 38%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMV%3C/text%3E%3C/svg%3E)
This worked for us as well - thank you!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(153.6, 82%, 24%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJF%3C/text%3E%3C/svg%3E)
What worked for me was to remove the highlighted bit below that did the text replace when getting the pool id. This used to work so I can only assume its been updated since.