Where are my Long Term Retention backups (using PowerShell)

One of the ways to find and restore a database from a Long Term Retention backup is to use the Azure PowerShell cmdlets.
If Azure PowerShell is news to you, you can get started at https://docs.microsoft.com/en-us/powershell/azure/overview?view=azurermps-4.2.0

Sometimes, customers find themselves struggling to find the restore points they want, most of the times due to 2 reasons:

1 - Not setting the proper StartTime and EndTime on the Get-AzureRmRecoveryServicesBackupRecoveryPoint.
By default, this cmdlet will request the available recovery points for the last month if you don't set any StartTime, here is an example:

Get-AzureRmRecoveryServicesBackupRecoveryPoint -Item $backupItem

will issue:

.../recoveryPoints?$filter=startDate eq '2017-07-07 11:44:37 AM' and endDate eq '2017-08-
 06 11:44:37 AM'...


In case you only set a StartDate you also need to be aware that the cmdlet will automatically set the EndDate for you. Since the time difference between StartDate and EndData should not be more than 30 days, it will set it for 30 days. Here is an example:

$StartDate = (Get-Date "1/1/2017 0:00 AM").ToUniversalTime()
 Get-AzureRmRecoveryServicesBackupRecoveryPoint -Item $backupItem -StartDate $StartDate

will issue:

.../recoveryPoints?$filter=startDate eq '2017-01-01 12:00:00 AM' and endDate eq '2017-01-
 31 12:00:00 AM'...



2 - In case you have reused the database name, you will end up having more then one BackupItem in the vault.
You can get into this by situations like deleting a database and creating a new one with the same name or restoring a database then deleting the previous one and renaming the restored one.

New databases will have new ids and that id is part of the backup item name. Here is two BackupItem names for two instances of the same "database name" :

Each backup item can have several RecoveryPoints.

The following image will help you to better understand the concepts:
Backup container, items and recovery points


In order to help customers we have built a script that will help find all the available recovery points and restore a database from one of them, before getting our hands on the code let's see what the 3 steps will do:

Step 1 will list all the available recovery points from all backup items in the backup container starting from 1/1/2017.
Since you can only get 30 days at a time we will do several iterations until today's date.
You can change this start date into a more appropriate value for you at $StartDate = (Get-Date "1/1/2017 0:00 AM").ToUniversalTime()
You will be able to see the BackupItems listed in yellow and corresponding RecoveryPoints in green (more or less the same as the previous image).

Backup container, items and recovery points

Step 2 is about picking a BackupItem name and a corresponding RecoveryPointTime datetime.
Here is an example how the variables must be set:

$recoveryDate ='06/03/2017 23:40:35' 

We will also set destination server and database parameters in this step.


Step 3 will issue the restore request based on the information set in the previous steps.


Here is the script:

#STEP 1:

$resourceGroupName = "{replace}"
$vaultName = "{replace}"
$databaseName = "{replace}"

#Get the vault
$vault = Get-AzureRmRecoveryServicesVault -ResourceGroupName $resourceGroupName -Name $vaultName

# Set the vault context to the vault we want to restore from
Set-AzureRmRecoveryServicesVaultContext -Vault $vault

# the following commands find the container associated with the server 'myserver' under resource group 'myresourcegroup'
$container = Get-AzureRmRecoveryServicesBackupContainer -ContainerType AzureSQL -FriendlyName $vault.Name

# Get the long-term retention metadata associated with a specific database
$item = Get-AzureRmRecoveryServicesBackupItem -Container $container -WorkloadType AzureSQLDatabase -Name $databaseName

# Get all available backups for the previously indicated database
if ($item -isnot [array])
 Get-AzureRmRecoveryServicesBackupRecoveryPoint -Item $item
 $StartDate = (Get-Date "1/1/2017 0:00 AM").ToUniversalTime()
 while((Get-Date).ToUniversalTime() -gt $StartDate)
 $EndDate = $StartDate.AddDays(30).ToUniversalTime()
 if($EndDate -gt (Get-Date))
 $EndDate = (Get-Date).ToUniversalTime() 

 Write-Host Available Recovery Points from $StartDate until $EndDate -foregroundcolor White

 foreach ($backupItem in $item)
 Write-Host at BackupItem $backupItem.Name -foregroundcolor Yellow
 $recoveryPoints = Get-AzureRmRecoveryServicesBackupRecoveryPoint -Item $backupItem -StartDate $StartDate -EndDate $EndDate | Select RecoveryPointTime
 foreach ($point in $recoveryPoints) 
 write-host $point.RecoveryPointTime -foregroundcolor "Green"
 $StartDate= $StartDate.AddDays(30).ToUniversalTime()

#STEP 2:
#Pick a BackupItem name and a corresponding RecoveryPointTime datetime
$recoveryDate ='06/03/2017 23:40:35'

#Set destination server and database parameters
$serverName = "{replace}"
$restoredDatabaseName = "{replace}"
$edition = "Premium"
$performanceLevel = "P1"

#STEP 3:
#Restore the database
$backupItem = Get-AzureRmRecoveryServicesBackupItem -Container $container -WorkloadType AzureSQLDatabase -Name $backupItemName
$recoveryPoint = Get-AzureRmRecoveryServicesBackupRecoveryPoint -Item $backupItem -StartDate (Get-Date $recoveryDate).ToUniversalTime().AddMinutes(-10) -EndDate (Get-Date $recoveryDate).ToUniversalTime().AddMinutes(10)

$restoredDb = Restore-AzureRmSqlDatabase -FromLongTermRetentionBackup -ResourceId $recoveryPoint.Id -ResourceGroupName $resourceGroupName `
 -ServerName $serverName -TargetDatabaseName $restoredDatabaseName -Edition $edition -ServiceObjectiveName $performanceLevel



You need to set proper variables by replacing the '{replace}'.
We left some variables with sample values so it gets more clear to you, you also need to replace those at:


Comments (0)

Skip to main content