How to change Azure SQL DB LTR Recovery Service Vault ?

[RaghdaALdeen]

First published on MSDN on Sep 13, 2017
Received a comment saying 'Can't Change the server Recovery Service Vault', took a breathe and start a new Lab on Azure Portal trying to find out how it can be changed, after a lot of tries unfortunately I realized that it can't be done through the portal, so what is our best friend Solution ?...

PowerShell...

I decided to try using PowerShell , so this is what I 've done to change the Recovery Service Vault for an already configured LTR for an Azure SQL server .

Scenario:

I have Azure SQL Server "test" which contains 4 Azure DBs  with Recovery Service Vault "Test Vault", I want to change the Vault for this server to be "New Vault" and add one of the DBs "testDB" to this Vault ,so find below an explained steps to reach that then the code to execute:

P.S : Things to keep in mind about LTR and Recovery Service Vault.

• Same Resource group


• Same Region


• Same subscription

1. Make sure that you already installed Azure PowerShell, if not then Install and Configure Azure PowerShell .


2. Login to your Azure Account


3. Create a Recovery Services Vault and assign to the Parameter $serverName the same Server name you want to change it's Recovery Service Vault.


4. Set the Server to use the Recovery Vault for its long term retention Backups, which the server is already assigned to the parameter.


5. Create a Retention Policy to set how long to keep a database backup


6. Configure a database to use the Previously defined retention Policy.

# Login to your Azure Acount

Login-AzureRmAccount

# Create a recovery services vault
$resourceGroupName = "{resource-group-name}"
$serverName = "{test}"

$serverLocation = (Get-AzureRmSqlServer -ServerName $serverName -ResourceGroupName $resourceGroupName).Location
$recoveryServiceVaultName = "{new-vault-name}"

$vault = New-AzureRmRecoveryServicesVault -Name $recoveryServiceVaultName -ResourceGroupName $ResourceGroupName -Location $serverLocation
Set-AzureRmRecoveryServicesBackupProperties -BackupStorageRedundancy LocallyRedundant -Vault $vault

# Set your server to use the vault to for long-term backup retention

Set-AzureRmSqlServerBackupLongTermRetentionVault -ResourceGroupName $resourceGroupName -ServerName $serverName -ResourceId $vault.Id

# Retrieve the default retention policy for the AzureSQLDatabase workload type
$retentionPolicy = Get-AzureRmRecoveryServicesBackupRetentionPolicyObject -WorkloadType AzureSQLDatabase

# Set the retention value to two years (you can set to any time between 1 week and 10 years)
$retentionPolicy.RetentionDurationType = "Weeks"
$retentionPolicy.RetentionCount = 2
$retentionPolicyName = "my2weeksRetentionPolicy"

# Set the vault context to the vault you are creating the policy for
Set-AzureRmRecoveryServicesVaultContext -Vault $vault

# Create the new policy
$policy = New-AzureRmRecoveryServicesBackupProtectionPolicy -name $retentionPolicyName -WorkloadType AzureSQLDatabase -retentionPolicy $retentionPolicy
$policy

# Enable long-term retention for a specific SQL database

$databaseName = "testDB"
$policyState = "enabled"
Set-AzureRmSqlDatabaseBackupLongTermRetentionPolicy -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName -State $policyState -ResourceId $policy.Id



Now, go to the portal and check the Long Term Backup blade to see the change !