How to restore database using LTR backups with PowerShell

Published Jun 16 2022 01:47 PM 1,426 Views
Microsoft

Lets review steps to restore an Azure SQL database LTR backups with Azure CLI


#Import Az Module
Import-Module Az

# Login with your Azure account
Connect-AzAccount

# Identify available subscriptions
Get-AzSubscription

# Set context to the specific subscription for this task
Set-AzContext -SubscriptionId " Set-AzContext -SubscriptionId " xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

#Case 1: Restore a database without delete the server

$rg = "PGMtest"
$loc = "westeurope"
$srv = "pgmservertest"
$db = "pgmtesdb1"

Get-AzSqlDatabaseLongTermRetentionBackup -Location $loc -ServerName $srv -DatabaseName $db

ResourceId : /subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGMtest/providers/Microsoft.Sql/locations/westeurope/longTermRetentionServers/pgmservertest/longTermRetentionDatabases/pgmtesdb1/longTermRetentionBackups/f2ed7b9f-22d4-417a-bdfa-a6e73f390eba;132972482500000000
BackupExpirationTime : 7/16/2022 8:04:10 AM
BackupName : f2ed7b9f-22d4-417a-bdfa-a6e73f390eba;132972482500000000
BackupTime : 5/17/2022 8:04:10 AM
DatabaseName : pgmtesdb1
DatabaseDeletionTime : 5/18/2022 8:09:03 PM
Location : westeurope
ServerName : pgmservertest
ServerCreateTime : 5/5/2022 1:17:40 PM
ResourceGroupName : PGMtest
BackupStorageRedundancy : Geo


Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceId $backup.ResourceId -ResourceGroupName $rg -ServerName $srv -TargetDatabaseName "Test_LTR_Restore" -Edition "Standard" -ServiceObjectiveName "S3"


At this point I have deleted server “pgmtesdb1” and executed again “Get-AzSqlDatabaseLongTermRetentionBackup -Location $loc -ServerName $srv -DatabaseName $db” , and as you can see ResourceID has changed, so I have needed to assign it again to $backup and then restore on other server.


Get-AzSqlDatabaseLongTermRetentionBackup -Location $loc -ServerName $srv -DatabaseName $db

ResourceId : /subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/providers/Microsoft.Sql/locations/westeurope/longTermRetentionServers/pgmservertest/longTermRetentionDatabases/pgmtesdb1/longTermRetentionBackups/f2ed7b9f-22d4-417a-bdfa-a6e73f390eba;132972482500000000
BackupExpirationTime : 7/16/2022 8:04:10 AM
BackupName : f2ed7b9f-22d4-417a-bdfa-a6e73f390eba;132972482500000000
BackupTime : 5/17/2022 8:04:10 AM
DatabaseName : pgmtesdb1
DatabaseDeletionTime : 5/18/2022 8:09:03 PM
Location : westeurope
ServerName : pgmservertest
ServerCreateTime : 5/5/2022 1:17:40 PM
ResourceGroupName :
BackupStorageRedundancy : Geo

$backup = Get-AzSqlDatabaseLongTermRetentionBackup -Location $loc -ServerName $srv -DatabaseName $db -BackupName "f2ed7b9f-22d4-417a-bdfa-a6e73f390eba;132972482500000000"

Restore-AzSqlDatabase -FromLongTermRetentionBackup -ResourceId $backup.ResourceId -ResourceGroupName PGMresourcegroupwesteu -ServerName pgmserver3 -TargetDatabaseName "Test_LTR_Restore1" -Edition "Standard" -ServiceObjectiveName "S3"

ServerName : pgmserver3
DatabaseName : Test_LTR_Restore1
ResourceGroupName : PGMresourcegroupwesteu
Location : westeurope
DatabaseId : 8616c096-56fd-4739-975c-1b2573c7a569
Edition : Standard
CollationName : SQL_Latin1_General_CP1_CI_AS
CatalogCollation :
MaxSizeBytes : 268435456000
Status : Online
CreationDate : 5/19/2022 5:28:58 AM
CurrentServiceObjectiveId : 00000000-0000-0000-0000-000000000000
CurrentServiceObjectiveName : S3
RequestedServiceObjectiveName : S3
RequestedServiceObjectiveId :
ElasticPoolName :
EarliestRestoreDate :
Tags :
ResourceId : /subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/PGMresourcegroupwesteu/providers/Microsoft.Sql/servers/pgmserver3/databases/Test_LTR_Restore1
CreateMode :
ReadScale : Disabled
ZoneRedundant : False
Capacity : 100
Family :
SkuName : Standard
LicenseType :
AutoPauseDelayInMinutes :
MinimumCapacity :
ReadReplicaCount :
HighAvailabilityReplicaCount :
CurrentBackupStorageRedundancy : Geo
RequestedBackupStorageRedundancy : Geo
SecondaryType :
MaintenanceConfigurationId : /subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/providers/Microsoft.Maintenance/publicMaintenanceConfigurations/SQL_Default
EnableLedger : False


Enjoy.-

 

Co-Authors
Version history
Last update:
‎Jun 16 2022 02:15 PM
Updated by: