Query MS-SQL database backup history

How to query your database backup history:

SELECT TOP 10
    d.name,
    backup_start_date,
    m.[user_name], m.database_name,
    m.server_name, m.machine_name, f.physical_device_name
FROM
    msdb.sys.databases d
    INNER JOIN msdb.sys.backupset b ON (b.database_name = d.name)
    LEFT JOIN msdb.sys.backupmediaset m ON b.media_set_id = m.media_set_id
    LEFT JOIN msdb.sys.backupmediafamily f ON f.media_set_id = m.media_set_id
WHERE
    d.name = N'MyDatabaseName'
    AND m.[type] = 'D' -- D or L
ORDER BY
    backup_start_date DESC,
    m.backup_set_id,
    f.physical_device_name

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s