Share via

Azure Data Studios Azure SQL Migration hangs with Restoring Migration Status

Matthew Snook 0 Reputation points
2023-03-10T18:57:31.64+00:00

I am attempting to migrate a trivial database from an on-prem SQL Server 2019 instance into Azure using the Azure SQL Migration extension for Azure Data Studio. I am trying to migrate to an instance of SQL Server on Azure Virtual Machine.

I am able to successfully create the migration job and am on the "monitor your migration" step. The migration status is stuck on "Restoring" with a warning of "Full backup can not be found." If I cancel the migration, that warning is upgraded to an error. Despite the claim of this error, I have uploaded the full backup file as a page blob to an Azure blob container; the extension itself would not allow me to proceed if I had not.

I am the owner of the Azure subscription and everything therein using the Free tier.

I went through the process as I understand it: I created a migration plan, I have an instance of SQL Server on Azure Virtual Machine created (Free accounts cannot create Azure SQL Managed Instances). I have uploaded a full back up of the database as a page blob to a blob container. The container is in the same region as the virtual machine instance. I have attempted this migration with six databases at once and with one database by itself, and I have attempted this migration with both the Online and Offline strategies.

Going through the troubleshooting steps: I have an Azure account that is an Owner, I have created a SQL Server on Azure Virtual Machine, I have used sysadmin credentials for the source database, I have used an Azure storage account blob container, I do not know if the service account has read/write permissions, I don't think there's a TDE certificate for the source database, the database backups are in Azure, I'm not sure how to validate Runtime installation, and I have registered the Microsoft.DataMigration provider.

Having spoken with Azure Chat Support, they have told me that as far as they can see I have everything set up correctly and cannot see any reason that this migration is not working. They suggested posing my problem here in the hopes that somebody would have additional ideas.

For full clarity, this is definitely a case of "I don't know what I don't know." I am new to the Azure space and have followed the directions that I have found to the best of my ability, but I cannot say for certain that I have not missed something. The best that I can do is to tell you the information that I have confirmed with Azure Chat Support and provide additional information as requested.

Additional information from the Azure SQL Migation tool:

Source database name: Jeff

Target database name: Jeff

Migration status: Restoring

Source server:

Target server: cleanscreenvm

Backup location: westus3container

Source version: SQL Server 2019 15.0.2000.5

Target version:

Last applied backup files: -

{
  "properties": {
    "migrationStatusDetails": {
      "migrationState": "MonitorMigration",
      "blobContainerName": "westus3container",
      "restoreBlockingReason": "Full backup can not be found.",
      "pendingLogBackupsCount": 0,
      "pendingDiffBackupsCount": 0
    },
    "backupConfiguration": {
      "sourceLocation": {
        "fileStorageType": "AzureBlob",
        "testConnectivity": true
      }
    },
    "migrationStatusWarnings": {
      "restoreBlockingReason": "Full backup can not be found."
    },
    "scope": "/subscriptions/f2a65e47-cde5-40b8-adf6-62258faf6ecc/resourceGroups/PoC/providers/Microsoft.SqlVirtualMachine/sqlVirtualMachines/cleanscreenvm",
    "provisioningState": "Succeeded",
    "migrationStatus": "Restoring",
    "startedOn": "2023-03-10T00:58:08.323Z",
    "sourceDatabaseName": "Another",
    "migrationService": "/subscriptions/f2a65e47-cde5-40b8-adf6-62258faf6ecc/resourceGroups/migrationservicepoc/providers/Microsoft.DataMigration/sqlMigrationServices/migrationservice",
    "migrationOperationId": "5f013005-6c46-4e2b-9acd-e799b36302a1",
    "kind": "SqlVm"
  },
  "id": "/subscriptions/f2a65e47-cde5-40b8-adf6-62258faf6ecc/resourceGroups/PoC/providers/Microsoft.SqlVirtualMachine/sqlVirtualMachines/cleanscreenvm/providers/Microsoft.DataMigration/databaseMigrations/Another",
  "name": "Another",
  "type": "Microsoft.DataMigration/databaseMigrations"
}
{
  "properties": {
    "migrationStatusDetails": {
      "migrationState": "MonitorMigration",
      "blobContainerName": "westus3container",
      "restoreBlockingReason": "Full backup can not be found.",
      "pendingLogBackupsCount": 0,
      "pendingDiffBackupsCount": 0
    },
    "backupConfiguration": {
      "sourceLocation": {
        "fileStorageType": "AzureBlob",
        "testConnectivity": true
      }
    },
    "migrationStatusWarnings": {
      "restoreBlockingReason": "Full backup can not be found."
    },
    "scope": "/subscriptions/f2a65e47-cde5-40b8-adf6-62258faf6ecc/resourceGroups/PoC/providers/Microsoft.SqlVirtualMachine/sqlVirtualMachines/cleanscreenvm",
    "provisioningState": "Succeeded",
    "migrationStatus": "Restoring",
    "startedOn": "2023-03-10T17:00:49.86Z",
    "sourceDatabaseName": "Jeff",
    "migrationService": "/subscriptions/f2a65e47-cde5-40b8-adf6-62258faf6ecc/resourceGroups/migrationservicepoc/providers/Microsoft.DataMigration/sqlMigrationServices/migrationservice",
    "migrationOperationId": "1b376260-0005-4992-8de1-7d2867d81cdd",
    "kind": "SqlVm"
  },
  "id": "/subscriptions/f2a65e47-cde5-40b8-adf6-62258faf6ecc/resourceGroups/PoC/providers/Microsoft.SqlVirtualMachine/sqlVirtualMachines/cleanscreenvm/providers/Microsoft.DataMigration/databaseMigrations/Jeff",
  "name": "Jeff",
  "type": "Microsoft.DataMigration/databaseMigrations"
}
Azure Database Migration service

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Matthew Snook 0 Reputation points
    2023-03-11T00:59:26.3866667+00:00

    It took a few hours and staring at various screens for a while for me to realize this, but I believe that I have found the issue: my database backup is from SQL Server 2019. The SQL Server instance on my virtual machine was for 2012. I deleted my vm and recreated it with a SQL Server 2019 instance and I have officially been able to click the "cutover complete" button for the migration process, and the migration status is now "Succeeded." I haven't confirmed that everything worked the way I expected it to, but I have at the very least resolved the problem posed in this question.

    Put simply, make sure that your database backup is created from the same version of SQL Server that you are migrating to. Otherwise you may find yourself being told "Full backup can not be found" when what the error really means is "Full backup can not be found for target version of SQL Server."