Allow using `az sql db restore` cross-server

[marklagendijk]

Is your feature request related to a problem? Please describe.
I'm frustrated that I cannot use az sql db restore to restore a PITR backup of my database to a new database on another server.

Describe the solution you'd like
Currently az sql db restore supports the following arguments:

    --dest-name         
    --name -n           
    --resource-group -g                               
    --server -s                    

I would propose adding the following arguments:

    --dest-resource-group
    --dest-server

This way one can restore a PITR backup to another server in another resource group.

Describe alternatives you've considered
Only workarounds like:

• Using the REST API directly See comment below. Cross-server restore of PITR backups doesn't seem be supported by the REST API.
• First restoring the database on the same server, and then using an alternative method to get a clone on the target server

Additional context
I expect that az sql db restore uses the REST API, and specifically this endpoint.
As far as I can see there is no limitation that requires using the same server or resource group in the sourceDatabaseId.

The preview command for restoring LTR backups, az sql db ltr-backup restore, does support restoring cross-server.
But since LTR backups are only weekly, this does not suffice for our use-case.

[marklagendijk]

I tried to use az rest to test this, and it looks like restoring cross-server isn't supported.

SUBSCRIPTION_ID=
DEST_RESOURCE_GROUP=
DEST_SERVER=
DEST_DATABASE=Test
SOURCE_RESOURCE_GROUP=
SOURCE_SERVER=
SOURCE_DATABASE=
SOURCE_LOCATION=westeurope
RESTORE_TIME=2021-12-13T08:00:00.000Z

az rest --verbose --method put --url "https://management.azure.com/subscriptions/$SUBSCRIPTION_ID/resourceGroups/$DEST_RESOURCE_GROUP/providers/Microsoft.Sql/servers/$DEST_SERVER/databases/$DEST_DATABASE?api-version=2021-05-01-preview" --body "{\"location\": \"$SOURCE_LOCATION\",\"properties\": {\"createMode\": \"PointInTimeRestore\",\"sourceDatabaseId\": \"/subscriptions/$SUBSCRIPTION_ID/resourceGroups/$SOURCE_RESOURCE_GROUP/providers/Microsoft.Sql/servers/$SOURCE_SERVER/databases/$SOURCE_DATABASE\",\"restorePointInTime\": \"$RESTORE_TIME\"}}"

And then copy the url from Azure-AsyncOperation and query it using az rest --url URL

If I try this for a restore on the same server I get:

{
  "name": "d3ba5cce-1b26-4c1c-8893-f08104b75ed7",
  "startTime": "2021-12-13T12:06:56.41Z",
  "status": "InProgress"
}

But I try to restore to a different server + resource group I get:

{
  "error": {
    "code": "InternalServerError",
    "message": "An unexpected error occured while processing the request. Tracking ID: 'c5e53a82-fbd0-43a5-afd2-a328cdd63bdb'"
  },
  "name": "b64cf8db-7297-46d4-8654-c18276805321",
  "startTime": "2021-12-13T12:09:11.803Z",
  "status": "Failed"
}

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @azureSQLGitHub.

Issue Details

---

Is your feature request related to a problem? Please describe.
I'm frustrated that I cannot use az sql db restore to restore a PITR backup of my database to a new database on another server.

Describe the solution you'd like
Currently az sql db restore supports the following arguments:

    --dest-name         
    --name -n           
    --resource-group -g                               
    --server -s                    

I would propose adding the following arguments:

    --dest-resource-group
    --dest-server

This way one can restore a PITR backup to another server in another resource group.

Describe alternatives you've considered
Only workarounds like:

• Using the REST API directly,
• First restoring the database on the same server, and then using an alternative method to get a clone on the target server

Additional context
I expect that az sql db restore uses the REST API, and specifically this endpoint.
As far as I can see there is no limitation that requires using the same server or resource group in the sourceDatabaseId.

The preview command for restoring LTR backups, az sql db ltr-backup restore, does support restoring cross-server.
But since LTR backups are only weekly, this does not suffice for our use-case.

Author:	marklagendijk
Assignees:	-
Labels:	SQL, Service Attention, question, customer-reported, needs-triage
Milestone:	-

[jiasli]

Routing to service team.

[tnein]

Any progress on this?

You could also consider cross-subscription 😉

My workaround:

• Restore the DB backup in the environment it was created in.
• Export the resulting DB as a DACPAC to the destination environment.
• Import the DACPAC into the destination sql server.

[arvindshmicrosoft]

@marklagendijk - could you use PowerShell to accomplish this? It is possible to specify a different server there by using the combination of Get-AzSqlDatabase, Get-AzSqlDatabaseGeoBackup and Restore-AzSqlDatabase.

[marklagendijk]

@arvindshmicrosoft I can't see why that would work: what else can the Powershell implementation do than calling the REST API, which I already tried by using az rest?

[arvindshmicrosoft]

On the REST API - for a geo-restore (which is functionally the term also used for cross-server in the same region), the createMode should be set to Recovery instead. In the case of PowerShell, you will see that when Restore-AzSqlDatabase is used with -FromGeoBackup it internally sets the createMode to Recovery.

On the CLI - our team is aware of this gap in functionality. Tagging @SudhirRaparla.

[arvindshmicrosoft]

@marklagendijk I was recently made aware that Azure CLI added az sql db geo-backup and one of the commands there is for restore. It should be helpful in your case. Note that these commands are technically still in preview.