Restore Azure SQL Database to Alternate Location with PowerShell

Charles Lerant 1 Reputation point


I have been tasked with creating a powershell script that refreshes data from our production database to our beta database.

Currently to refresh the data we'll use a point in time backup from production and restore to our beta database using the steps found here under the heading "Restore to an Alternate Location" and we use the selections "Overwrite if the DB with the same name already exists on selected SQL instance".

So we basically overwrite our Beta DB with a backup taken from Production. The databases are on the same server and the same instance.

I'm trying to do this with powershell but so far I'm hitting a wall maybe I'm overthinking this. But when I search for restoring a database to an alternate location with powershell I seem to be just finding information that sends me to something like what I have below which is restoring to a separate instance.

Can what I'm trying to do be done with powershell?

Sorry if this seems like such a newbie question. This is my first foray into this type of thing.

Taken from Example 6 here

$vault = Get-AzRecoveryServicesVault -ResourceGroupName "resourceGroup" -Name "vaultName"
$BackupItem = Get-AzRecoveryServicesBackupItem -BackupManagementType AzureWorkload -WorkloadType MSSQL -VaultId $vault.ID -Name "MSSQLSERVER;model"
$StartDate = (Get-Date).AddDays(-7)
$EndDate = Get-Date
$FullRP = Get-AzRecoveryServicesBackupRecoveryPoint -Item $BackupItem -StartDate $StartDate.ToUniversalTime() -EndDate $EndDate.ToUniversalTime() -VaultId $vault.ID
$TargetInstance = Get-AzRecoveryServicesBackupProtectableItem -WorkloadType MSSQL -ItemType SQLInstance -Name "<SQLInstance Name>" -ServerName "<SQL VM name>" -VaultId $vault.ID
$AnotherInstanceWithFullConfig = Get-AzRecoveryServicesBackupWorkloadRecoveryConfig -RecoveryPoint $FullRP -TargetItem $TargetInstance -AlternateWorkloadRestore -VaultId $vault.ID
Restore-AzRecoveryServicesBackupItem -WLRecoveryConfig $AnotherInstanceWithLogConfig -VaultId $vault.ID

I have also looked at the documentation here...

but again when it goes to alternate workloads it points me restoring to another instan

SQL Server on Azure Virtual Machines
Azure SQL Database
{count} votes