Restore an Azure SQL Database

This is a part of series “Stairway to being an Azure SQL DBA“, where I will be covering all the topics that an Azure SQL DBA should know about.

If you have been following along you know that taking manual backups in Azure SQL Database isn’t possible and Azure takes care of the backup and it’s called Automated backups.

In this blog we’ll see how you can restore an automated backup for some older period within the period of backup retention that you have defined.

Steps to restore an Azure SQL Database using GUI

To restore your Azure SQL Database from an older backup we need to go to the database which we want to restore and click on the “Restore” button.

When you click on the restore button, you will get the option to select a time of PITR or Long term retention backup, and just click on the Review + Create.

Steps to Restore an Azure SQL Database using PowerShell

To restore Azure SQL Database you cannot use the T-SQL the reason being you don’t know the location of the Azure SQL Database backups.

For doing it using PowerShell first connect to Azure using Az Login. Once connected to your Azure subscription.

PS C:\>$Database = Get-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01"

Once you have the details for the database you need to have the time to which you want to restore, target database name, edition and server name.

PS C:\> Restore-AzSqlDatabase -FromPointInTimeBackup -PointInTime UTCDateTime -ResourceGroupName $Database.ResourceGroupName -ServerName $Database.ServerName -TargetDatabaseName "RestoredDatabase" -ResourceId $Database.ResourceID -Edition "Standard" -ServiceObjectiveName "S2"

To know more about the Az-SqlDatabase command check the ms docs link.

Conclusion

An Azure SQL Database can be restored to the same server or to a different server in a different region (Geo-Restore, will cover this in next blog), using Portal or PowerShell for doing this.

Advertisement