Azure SQL DB Backup

Question

Aoyone know how we can get list of all SQL Database with the backup date using powershell?

Answer 1

Azure SQL Database is a PaaS (Platform as a Service) offering, so you don’t perform or track backups the same way as with on-prem SQL Server. Backups are taken automatically by Azure, and you can query information about their retention and restore points—but you can’t get a traditional “last backup date” directly like you would from msdb.dbo.backupset.

That said, you can use PowerShell to list all databases and their available restore points (or earliest/latest backup timestamps). Here’s how:

1. Install and import Az module (if not done yet)

Install-Module -Name Az -AllowClobber -Scope CurrentUser

Import-Module Az

Connect-AzAccount

2. Get all Azure SQL Databases in a subscription

List all SQL Databases

Get-AzSqlDatabase -ResourceGroupName "<ResourceGroup>" -ServerName "<ServerName>"

This returns basic information: database name, edition, status, max size, etc.

3. Get Restore Points (includes backup timestamps)

To get backup/restore info, use Get-AzSqlDatabaseRestorePoint or Get-AzSqlDeletedDatabaseBackup.

Example for existing databases:

$resourceGroup = "<ResourceGroup>"

$serverName = "<ServerName>"

Get-AzSqlDatabase -ResourceGroupName $resourceGroup -ServerName $serverName | ForEach-Object {

$dbName = $_.DatabaseName

Write-Output "=== Database: $dbName ==="

Get-AzSqlDatabaseRestorePoint -ResourceGroupName $resourceGroup -ServerName $serverName -DatabaseName $dbName

}

This will list restore points (timestamps) available for each database — these correspond to Azure’s backups.

4. For Deleted Databases (long-term backup)

If you want to see backups of dropped databases that are still within retention:

Get-AzSqlDeletedDatabaseBackup -ResourceGroupName $resourceGroup -ServerName $serverName

This shows Database Name, Deletion Date, and Earliest/Latest Restore Dates.

5. If you want to export into a report

$results = @()

Get-AzSqlDatabase -ResourceGroupName $resourceGroup -ServerName $serverName | ForEach-Object {

$dbName = $_.DatabaseName

$restorePoints = Get-AzSqlDatabaseRestorePoint -ResourceGroupName $resourceGroup -ServerName $serverName -DatabaseName $dbName

foreach ($point in $restorePoints) {

    $results += [PSCustomObject]@{

        DatabaseName = $dbName

        RestorePointType = $point.RestorePointType

        RestorePointCreationDate = $point.RestorePointCreationDate

    }

}

}

$results | Format-Table -AutoSize

Or export to CSV

$results | Export-Csv "AzureSqlDb_Backups.csv" -NoTypeInformation

Key Takeaways

Azure SQL automatically backs up databases — no manual jobs required.

You cannot query msdb like on-prem SQL Server.

Use Get-AzSqlDatabaseRestorePoint for current DBs or Get-AzSqlDeletedDatabaseBackup for dropped ones.

You can export to CSV if you need reporting.

Answer 2

Hi @Handian Sudianto ,

please run the following script and see if all data you want are in the result set:

$arsv= Get-AzRecoveryServicesVault -ResourceGroupName "<name of the resource group>" -Name "<name of service recovery vault"
$backupJobs = Get-AzRecoveryServicesBackupJob -VaultID $arsv.ID -BackupManagementType AzureWorkload
$backupJobs | Select-Object *

The script gets all backup jobs without any filter and displays all data in the result set. Filtering the data properties is possible with Select-Object <column1>, <column2> instead of the Select-Object *.

---

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards

Andreas Baumgarten

Answer 3

Hello Handian Sudianto

You can use the below query to get the data of the backups,

# get the list of all LTR backups in a specific Azure region
# backups are grouped by the logical database id, within each group they are ordered by the timestamp, the earliest backup first
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location

# get the list of LTR backups from the Azure region under the named server
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location -ServerName $serverName

# get the LTR backups for a specific database from the Azure region under the named server
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location -ServerName $serverName -DatabaseName $dbName

# list LTR backups only from live databases (you have option to choose All/Live/Deleted)
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location -DatabaseState Live

# only list the latest LTR backup for each database
$ltrBackups = Get-AzSqlDatabaseLongTermRetentionBackup -Location $server.Location -ServerName $serverName -OnlyLatestPerDatabase

https://learn.microsoft.com/en-us/azure/azure-sql/database/long-term-backup-retention-configure?view=azuresql&tabs=powershell#view-ltr-backups-1