Azure DB Backup

Question

Azure DB Backup

Handian Sudianto 6,071

Hello,

Can we make an script / automation to detect if there any SQL Database with no available LTR backups?

User's image

Accepted answer

1 additional answer

Your answer

Answer 1

TP 123.5K Volunteer Moderator

Hi,

Below will return list of databases on Azure SQL without LTR backup, excluding master:

$databases = Get-AzSqlDatabase -ResourceGroupName yourResourceGroup -ServerName yourAzureSqlServer|where DatabaseName -ne "master" 
$databases|ForEach-Object -Process {if ((Get-AzSqlDatabaseLongTermRetentionBackup -Location $_.Location -ServerName $_.ServerName) -eq $null) {$_.DatabaseName} }|Out-File databases.txt -noClobber

-TP

Handian Sudianto 6,071 Reputation points

2023-05-31T00:51:28.1766667+00:00

Hello,

Can we export the result to txt file?
TP 123.5K Reputation points Volunteer Moderator

2023-05-31T02:16:57.69+00:00

Yes, you can export it to txt file by adding Out-File on the end of it. I'll update the sample code above so it includes Out-File.

Right now all it exports is the list of database names based on the Resource Group and Server Name you specify.
Handian Sudianto 6,071 Reputation points

2023-05-31T02:30:18.27+00:00

Hello,

Thanks you very much.
Handian Sudianto 6,071 Reputation points

2023-06-12T01:37:09.4033333+00:00

Hello

i have another SQL server with one DB not have the backup, but why your script is not showing any result for this SQL Server? But if i test on another SQL server it's work.

DB seg-ijp have not LTR enabled, and if i change the script from -eq null to -ne null the script have result and detect seg-ijp have LTR

Answer 2

TP 123.5K Volunteer Moderator

So that I can try to recreate this, can you tell me what might be different about this database, besides the name? I see the earliest PITR is more recent than the others in the list, and you mentioned LTR isn't enabled for it, anything else?

Handian Sudianto 6,071 Reputation points

2023-06-12T06:24:59.4666667+00:00

Hello,

In this SQL Server all DB with LTR disabled is detected using given script (example bpm DB)

But on the other database with different resource group andsql server name the script return empty value. All three DB created created on 9 June and 12 Jun
TP 123.5K Reputation points Volunteer Moderator

2023-06-12T08:34:58.2166667+00:00
Is it picking up a LTR backup of deleted database with same name? One way to test would be to add DatabaseState parameter to only show live databases.

As a test, see if this updated script returns the database in both cases:

$databases = Get-AzSqlDatabase -ResourceGroupName yourResourceGroup -ServerName yourAzureSqlServer|where DatabaseName -ne "master" $databases|ForEach-Object -Process {if ((Get-AzSqlDatabaseLongTermRetentionBackup -Location $_.Location -ServerName $_.ServerName -DatabaseState Live) -eq $null) {$_.DatabaseName} }|Out-File databases.txt -noClobber

If deleted database doesn't explain it then I'll give you different version for troubleshooting so we can see what it is doing and thus develop a fix. Please let me know your results whether negative or positive.

Thanks.
Handian Sudianto 6,071 Reputation points

2023-06-12T08:42:34.1766667+00:00

Hello ,

All database is in use and i'm not asking about deleted database. After run your script, the result still empty. If the script use Not Equal (-ne $null) all database is listed. It's possible to get all database with their LTR?

Share via

Azure DB Backup

1 additional answer

Your answer