patternsqlMajor

Get last full backup and transaction log backup for each database

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

lastfulleachlogdatabasegettransactionforandbackup

Problem

How to write script or a query that will display the last full backup and the last log backup for each database on each of the production servers using the system table "backupset" and related backup tables.

Solution

Something like this should get you the output that you're looking for. The below query pulls the most recent backup of type full, differential, or log backup for each database.

;with backup_cte as
(
    select
        database_name,
        backup_type =
            case type
                when 'D' then 'database'
                when 'L' then 'log'
                when 'I' then 'differential'
                else 'other'
            end,
        backup_finish_date,
        rownum = 
            row_number() over
            (
                partition by database_name, type 
                order by backup_finish_date desc
            )
    from msdb.dbo.backupset
)
select
    database_name,
    backup_type,
    backup_finish_date
from backup_cte
where rownum = 1
order by database_name;

Code Snippets

;with backup_cte as
(
    select
        database_name,
        backup_type =
            case type
                when 'D' then 'database'
                when 'L' then 'log'
                when 'I' then 'differential'
                else 'other'
            end,
        backup_finish_date,
        rownum = 
            row_number() over
            (
                partition by database_name, type 
                order by backup_finish_date desc
            )
    from msdb.dbo.backupset
)
select
    database_name,
    backup_type,
    backup_finish_date
from backup_cte
where rownum = 1
order by database_name;

Context

StackExchange Database Administrators Q#89278, answer score: 22

Revisions (0)

No revisions yet.