During my recent training on SQL Server Performance Tuning, I received a question about when the database was last backed up. Although the training primarily focused on performance tuning, I enjoy answering all types of questions related to SQL Server. In this blog post, I will share a script that can be used to determine the last backup date for a database.

Here is the script:

SELECT TOP 100
    s.database_name,
    CASE s.[type]
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
    END AS BackupType,
    m.physical_device_name,
    CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
    CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
    s.backup_start_date,
    s.server_name,
    s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
ORDER BY backup_start_date DESC, backup_finish_date

When you run the above query, it will display a result set showing the backup name and type of the backup. One of my favorite columns in the result set is the “TimeTaken” column, as it provides insights into the backup process.

If you are interested in learning more about the backup timeline and understanding the database restore process in the Full Recovery Model, I highly recommend reading my previous blog post on the topic. It has been one of the most popular posts on my blog so far.

Thank you for reading! If you have any further questions or topics you would like me to cover in future blog posts, please let me know.