SQL Server on Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
315 questions
Restore Azure SQL Database to Alternate Location with PowerShell
Charles Lerant
1
Reputation point
Hello,
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 https://learn.microsoft.com/en-us/azure/backup/restore-sql-database-azure-vm 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...
https://learn.microsoft.com/en-us/azure/backup/backup-azure-sql-automation
but again when it goes to alternate workloads it points me restoring to another instan
{count} votes
-
Oury Ba-MSFT 10,326 Reputation points Microsoft Employee2021-07-08T21:06:04.247+00:00 @Charles Lerant Thank you for posting your question on Microsoft Q&A.
Have you checked the permissions on the instance?. Are there any instance showing "Not Ready"?
Regards,
Oury
IIt's not really a permission issue I'm having.
I just need to be pointed in the right direction. What I'm trying to do is restore a DB to the same instance just a different DB.
For example.
II have a back up of msqlinstance1/PROD_DB
And I'm trying to restore it to msqlinstance1/BETA_DB
But not finding how to do that in powershell. All the research I have found thus far just shows how to restore to the same instance.
Does that make sense?
@Charles Lerant It is a bit confusing you mentioned above that "but again when it goes to alternate workloads it points me restoring to another instance"
I just need to be pointed in the right direction. What I'm trying to do is restore a DB to the same instance just a different DB.
But not finding how to do that in PowerShell. All the research I have found thus far just shows how to restore to the same instance.
Are you trying to restore back up DB to the same instance or to another instance.?
Regards,
Oury
Hi @Charles Lerant Please see my comment above. there is a confusing , could you please clarify whether you are trying to restore backup DB to the same instance ot to another instance.
Regards,
Oury
IIt is on the same instance. But a different database.
Sorry for the delayed response I have been out of the office for a week. My appologies.
Maybe this will clarify things more.
I have a DB called Production and another DB called Beta. We backup production regularly. They are both on the same server and both on the same instance.
\
So I have
MSSQLSERVER;Production
MSSQLSERVER;Beta
I want to restore Beta using the production backup.
So my script is so far as follows...
connect to azure
Connect-AzAccount
get vault information
$vault = Get-AzRecoveryServicesVault -ResourceGroupName "Backup-Vault" -Name "Backup-Vault"
get the backup item
$bkpItem = Get-AzRecoveryServicesBackupItem -BackupManagementType AzureWorkload -WorkloadType MSSQL -Name "SQLDataBase;MSSQLSERVER;Production" -VaultId $vault.ID
fetch point-in-time recovery point
Get-AzRecoveryServicesBackupRecoveryLogChain -Item $bkpItem -VaultId $vault.ID
set PointInTime
$PointInTime = Get-Date -Date "2019-03-20 01:00:00Z"
set the recovery configuration
$OverwriteWithLogConfig = Get-AzRecoveryServicesBackupWorkloadRecoveryConfig -PointInTime $PointInTime -Item $bkpItem -OriginalWorkloadRestore -VaultId vault.ID
The next step would be to just run the restore. But that's where I'm stuck.
How do do I restore to MSSQLSERVER;Beta?
Hi @Charles Lerant Our PG group suggests to create a support request so we can better assist you. Let me know what the support ticket number is so we can keep track. Feel free to reach out if you run into any issues.
Regards,
Oury
Sign in to comment