Restore SQL Server databases in Azure VMs across subscription

Question

I'm generating a runbook to execute a PowerShell script that takes a backup of a SQL Server on an Azure virtual machine and restores it to another SQL Server on a different virtual machine but in a different subscription. The code is giving me errors of objects not found, is there any example available on how to accomplish this?

Answer 1

Hello Miguel, I suggest you have a look to this link which will explain how to accomplish it . This is the one I use all the time https://learn.microsoft.com/en-us/azure/backup/backup-azure-sql-automation Regards Javier

Answer 2

Hello Javier, I'm familiar with that document, but it doesn't solve my problem. I'll provide an example of restoring a full backup of a database on another machine but within the same subscription, which does work for me.

Answer 3

# Variables para la primera suscripción (Recovery Services)      $SourceSubscriptionId = "XXXXXXXXXXXXXXXXXXXXXXX"        $resourceGroupName = "ResouceGroupBackup"        $RecoveryServicesVaultName = "NombreVault"        $databaseName = "dbrunbook"      $desiredBackupItemName = "SQLDataBase;mssqlserver;dbrunbook"          $TargetSqlInstance = "sqlinstance;mssqlserver"   $TargetServerName = "PreComputer.microsoft.com"    $TargetContainerName = "PS-Computer-PRE-DB1"     $DestinationDatabaseName =  "dbrunbook"  $AvailabilityGroupName = "GRoupPre"   # Conexión a la suscripción      Write-Output "Conectándose a la primera suscripción..."      Connect-AzAccount -SubscriptionId $SourceSubscriptionId -Identity        Write-Output "Conectado a la primera suscripcion"                 # Obtener el Vault de Servicios de Recuperación  $vault = Get-AzRecoveryServicesVault -ResourceGroupName $resourceGroupName -Name $RecoveryServicesVaultName       # Copias de seguridad correspondiente a la base de datos SQL ar_pss_db   $bkpItems = Get-AzRecoveryServicesBackupItem -BackupManagementType AzureWorkload -WorkloadType MSSQL -Name $databaseName -VaultId $vault.ID       # Como dicha base de datos puede estar en mas de un servidor     # Filtrar la colección para obtener el backup deseado        $bkpItem = $bkpItems | Where-Object { $.Name -eq $desiredBackupItemName }        # Dicho backup puede tener varios puntos de recuperacion (Full, diferencial, log)        # Obtenemos todos los puntos de recuperacion de los ultimos 7 dias       $startDate = (Get-Date).AddDays(-7).ToUniversalTime()        $endDate = (Get-Date).ToUniversalTime()      $RecoveryPoints= Get-AzRecoveryServicesBackupRecoveryPoint -Item $bkpItem -VaultId $Vault.ID -StartDate $startdate -EndDate $endDate         #Ordenamos por fecha de forma descendente        $SortedRecoveryPoints = $RecoveryPoints | Sort-Object -Property RecoveryPointTime -Descending    # Filtrar los puntos de recuperación para obtener el primero con RecoveryPointType 'Full'        $FullRP = $SortedRecoveryPoints | Where-Object { $.RecoveryPointType -eq 'Full' } | Select-Object -First 1       # Obtener el contenedor de backup de destino     $TargetContainer =  Get-AzRecoveryServicesBackupContainer -ContainerType AzureVMAppContainer -Status Registered  -VaultId $Vault.ID -FriendlyName $TargetContainerName        # Obtener la información del servidor de destino     $TargetInstance = Get-AzRecoveryServicesBackupProtectableItem -WorkloadType MSSQL -ItemType SQLInstance -Name $TargetSqlInstance -ServerName $TargetServerName -VaultId $vault.ID         # Obtener la configuración de recuperación para el punto de recuperación     $AnotherInstanceWithFullConfig = Get-AzRecoveryServicesBackupWorkloadRecoveryConfig -AlternateWorkloadRestore -VaultId $Vault.ID -TargetContainer $TargetContainer -TargetItem $TargetInstance -RecoveryPoint $FullRP      # Modificar el nombre de la base de datos de destino. Por defecto el nombre seria <Target Instance name>/azurebackup1_restored_3_19_2019_1850 $AnotherInstanceWithFullConfig.RestoredDBName = $databaseName # Sobreescribir la base de datos si existe $AnotherInstanceWithFullConfig.OverwriteWLIfpresent = "yes"   #Lanzar el proceso de Restore de la base de datos Restore-AzRecoveryServicesBackupItem -WLRecoveryConfig $AnotherInstanceWithFullConfig -VaultId $vault.ID       Write-Output "----- Proceso FINALIZADO ------"

Answer 4

@Miguel Angel Mela Lozoya
Thank you for reaching out. You can use Azure Database Migration Service (DMS)’s PowerShell to achieve it. However, DMS does not take the backup. So, you will need to initiate the backups (Full / Tlogs), configure the Backup folder (holding current backup) as file share or place the backups in a file share, and use this template for migration. OR Backup can be directly uploaded to Azure blob container  and use this template for migration.  Refer: Tutorial: Migrate SQL Server to SQL Server on Azure Virtual Machine online using Azure Data Studio - Azure Database Migration Service | Microsoft Learn  Note: For local file share scenario, SHIR need to be configure for DMS. For Backups uploaded to blob container, no SHIR is needed. Regards, Oury