Query Azure SQL database backup info

Question

Post reply

Query Azure SQL database backup info

ksl28

SSC Enthusiast

Points: 178

May 3, 2023 at 7:54 am

#4183379

Hi,

Ive been trying to figure out a way, to query the backup info for my Azure SQL databases, without having to login to the Azure portal.

I came up with the query, and at first it looked like it worked as expected:

select *
from
    (SELECT Name,
        Database_ID,
        physical_database_name
    from sys.databases
    WHERE database_id > = 4
    ) as databaseinfo
    LEFT JOIN (
    select
        physical_database_name,
        backup_file_id,
        backup_start_date,
        backup_finish_date,
        case backup_type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction log'
        END AS BackupType,

        CASE in_retention 
        WHEN 1 THEN 'In retention'
        WHEN 0 THEN 'Out of retention'
        END AS IsBackupAvailable
    from sys.dm_database_backups
) as backupinfo
    ON backupinfo.physical_database_name = databaseinfo.physical_database_name

But it appears that the databases, have the same value in physisal_database_name, which makes no sense in my opinion.

UniqueDatabaseName

How do i query my backup AND join it with the other tables, so that i can see the database name?

Attachments:

You must be logged in to view attached files.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

What you're seeing there is the internal server name. It's not actually a database, but the VM on which your databases are running. I don't have all the internals access to get into details beyond what I just typed though.

I'm not sure how to get the database name. Maybe through the database_guid there. I'm not sure if that maps back to sys.databases or not.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ksl28 SSC Enthusiast Points: 178 More actions · Answer 2

I was afraid, that it would be the answer.

I cant seem to find a way, to map the database_guid to the info from the sys.dm_database_backups table - if you have more suggestions, im all ears 🙂

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

I don't have an answer or even a guess. Hang on though. I'll see if I can get some help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning