question

97670328 avatar image
0 Votes"
97670328 asked OuryBa-MSFT commented

Issue restoring sqlazure database from Long term retention using the REST API

I am trying to copy a sqlazure database from long term retention using the documented REST API:
POST https://management.azure.com/subscriptions/{subscriptionId}/providers/Microsoft.Sql/locations/{locationName}/longTermRetentionServers/{longTermRetentionServerName}/longTermRetentionDatabases/{longTermRetentionDatabaseName}/longTermRetentionBackups/{backupName}/copy?api-version=2021-02-01-preview

I am passing all the info correctly but I receive copy is not supported feature in this subscription.
Anything I need to configure in my azure subscription?
I can do it from the portal but not from REST.
I have no issue using PITR restore from REST with no issue. It is just the restore from LTR.

Thanks

Khaled

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

97670328 avatar image
0 Votes"
97670328 answered OuryBa-MSFT commented

Hi Oury,

I do have a support ticket:
TrackingID#2108110010000253

Thanks

Khaled

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Got it Thank you @97670328

0 Votes 0 ·

@97670328 In order this works as suggested you should get the correct Id for the backup and try again, you can use the PS command Get-AzSqlDatabaseLongTermRetentionBackup to get the IDs for backups
more information https://docs.microsoft.com/en-us/powershell/module/az.sql/get-azsqldatabaselongtermretentionbackup?view=azps-6.3.0 [docs.microsoft.com]

Please let support and confirm here if this is resolved.

Regards,
Oury

0 Votes 0 ·
97670328 avatar image
0 Votes"
97670328 answered OuryBa-MSFT commented

Hi Oury,

Still does not work, I am emailing the details.
I am getting this error now:
{
"error": {
"code": "MissingRecoveryServicesRecoveryPointId",
"message": "Missing recovery services recovery point Id."
}
}

Thanks

Khaled

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @97670328 Could you please open a support ticket if you have a support plan. Let us know if you don't have it so we can unable a free one time support ticket for you.
Let us know if you run into any issues.

Regards,
Oury


0 Votes 0 ·
OuryBa-MSFT avatar image
0 Votes"
OuryBa-MSFT answered

@97670328 Are you using the API correctly?
First fetch the LTR backup resource id and then use that in restore.
Please do not use recoveryServicesRecoveryPointResourceId, use longTermRetentionBackupResourceId instead.
Also please provide the exact resource Id you are trying to use in the request (Send it through azcommunity@micrososft.com Subject line attn: Oury, so I don't missed it) so we can check whether it is a valid one or not.
This ID should be something similar to below and can be fetched from List-LTR Backup API.

124106-image.png

Please note:
<db name to restore> this would be source db name
<restoreId> this is something of format guid;timestamp in unixformat


Regards,
Oury



image.png (13.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

97670328 avatar image
0 Votes"
97670328 answered OuryBa-MSFT converted comment to answer

I am still getting this error:

{
"error": {
"code": "MissingRecoveryServicesRecoveryPointId",
"message": "Missing recovery services recovery point Id."
}
}

Here is what I am passing:
{"properties":{"createMode":"RestoreLongTermRetentionBackup ","recoveryServicesRecoveryPointResourceId":"/subscriptions/<mysubscription>/resourceGroups/warlockdbs1/providers/Microsoft.Sql/servers/<myserver>/databases/<db name to restore>/longTermRetentionBackups/<restoreId>","targetDatabaseName":"db_3400__ltr"},"location":"East US"}

Am I missing anything?
I even tried it from the doc in try button and still getting the same error.

Thanks

Khaled

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

97670328 avatar image
0 Votes"
97670328 answered OuryBa-MSFT commented

Hi Oury,

Let me check it out and get back. I think I have tried this before and still had issues.
Will update shortly.

Thanks for all help,

Khaled

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sure @97670328.

Regards,
Oury

0 Votes 0 ·
DimitriFurman-MSFT avatar image
0 Votes"
DimitriFurman-MSFT answered OuryBa-MSFT commented

Hi Khaled,

To restore an LTR backup, you need to first call one of the APIs that list LTR backups: https://docs.microsoft.com/en-us/rest/api/sql/2021-02-01-preview/long-term-retention-backups and get the Id property for the backup you want to restore. Then, call, https://docs.microsoft.com/en-us/rest/api/sql/2021-02-01-preview/databases/create-or-update, specify RestoreLongTermRetentionBackup as CreateMode, and use the Id property from the previous call as recoveryServicesRecoveryPointResourceId.

We will improve our REST API documentation to clarify these steps.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @97670328 Please see response above from our Product Group(@DimitriFurman-MSFT ).

Hope that answer your question. If the answer above helped resolve your issue please mark as accepted answer. Otherwise let us know how we can better assist you.

Regards,
Oury

0 Votes 0 ·

Hi @97670328 Please see response above from our Product Group(@DimitriFurman-MSFT ).

Hope that answer your question. If the answer above helped resolve your issue please mark as accepted answer. Otherwise let us know how we can better assist you.

Regards,
Oury

0 Votes 0 ·
97670328 avatar image
0 Votes"
97670328 answered

Does anyone from Microsoft dev group that can help identifying the above error I have or have documentation about the error?
Pl. let me know if you need any info to produce this.

Thanks

Khaled

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

97670328 avatar image
0 Votes"
97670328 answered

Thanks Oury. I was hoping to avoid using powershell. I am doing this in .NET and I was trying to use fluent or even REST API to avoid using powershell. This is part of any API I am offering to our client.

Is this a bug in the REST API? any plan to fix it and when?

Great thanks for helping.

Khaled

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

97670328 avatar image
0 Votes"
97670328 answered OuryBa-MSFT edited

Here is the error I am getting:
{\"error\":{\"code\":\"MissingRecoveryServicesRecoveryPointId\",\"message\":\"Missing recovery services recovery point Id.\"}}"

I have tried the following request for PUT request body:

{"properties":{"createMode":"RestoreLongTermRetentionBackup","recoveryServicesRecoveryPointResourceId":"/subscriptions/<mysub>/resourceGroups/<myresourcegroup>/providers/Microsoft.Sql/servers/<sqlservername>/databases/<sourcedb>/longTermRetentionBackups/<LTR-backupname>","sourceDatabaseId":"/subscriptions/<mysub>/resourceGroups/<myresourcegroup>/providers/Microsoft.Sql/servers/<mysqlserver>/databases/<dbtoberestored>"},"location":"<location>"}

Thanks

Khaled

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KhaledHassen-0716 Could you please try to use PowerShell to see if that will resolve the issue. We have a customer who faced similar issue before.
Please follow instructions in this document Manage Azure SQL long term back up retention.
For Get-AzSqlDatabaseLongTermRetentionBackup and Restore-AzSqlDatabase, you will need to have one of the following roles:

Subscription Owner role or

SQL Server Contributor role or

Custom role with the following permissions:

Regards,
Oury

0 Votes 0 ·
97670328 avatar image
0 Votes"
97670328 answered

Thanks Ben. Yes I am trying to restore the database from LTR.
I have tried the create-or-update and it works well for Pitr restore.

But for LTR, it asks about recoveryServicesRecoveryPointResourceId. Not sure what to specify for this param.

Great thanks for helping.

Khaled

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.