As a SQL Server user, it is crucial to have a clear understanding of the backup history for your databases. Knowing the details of your backups can help you ensure data integrity, track changes, and troubleshoot any issues that may arise.

In this blog post, we will explore a script that provides valuable information about the backup history of a single database. This script has been updated to incorporate the latest trends and additions in SQL Server 2014.

The script answers several important questions:

• What were the different backups taken on the current database?
• Which user was involved in taking the backup?
• What is the type of backup we are talking about (Full, Differential, or Log)?
• What was the recovery model and database compatibility level of the database at the time the backup was taken?
• What was the size of the backup (both compressed and uncompressed)?
• Was the backup password protected?
• When were the backups taken?

Let’s take a look at the script:

-- Recent backup history for the current database
SELECT s.database_name 'Database',
       s.recovery_model 'Recovery Model',
       s.compatibility_level,
       s.USER_NAME 'Backup by Username',
       CASE s.TYPE
           WHEN 'D' THEN 'Full'
           WHEN 'I' THEN 'Diff'
           WHEN 'L' THEN 'Log'
       END 'Backup Type',
       CONVERT(VARCHAR(20), s.backup_finish_date, 13) 'Backup Completed',
       CAST(mf.physical_device_name AS VARCHAR(100)) 'Physical device name',
       DATEDIFF(minute, s.backup_start_date, s.backup_finish_date) 'Duration Min',
       CAST(ROUND(s.backup_size * 1.0 / (1024 * 1024), 2) AS NUMERIC(10, 2)) 'Size in MB',
       CAST(ROUND(s.compressed_backup_size * 1.0 / (1024 * 1024), 2) AS NUMERIC(10, 2)) 'Compressed Size in MB',
       CASE
           WHEN LEFT(mf.physical_device_name, 1) = '{' THEN 'SQL VSS Writer'
           WHEN LEFT(mf.physical_device_name, 3) LIKE '[A-Za-z]:\%' THEN 'SQL Backup'
           WHEN LEFT(mf.physical_device_name, 2) LIKE '\\' THEN 'SQL Backup'
           ELSE mf.physical_device_name
       END 'Backup tool',
       s.is_copy_only,
       s.is_password_protected,
       s.is_force_offline /* for WITH NORECOVERY option */
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
WHERE s.database_name = DB_NAME() -- remove this condition if you want all databases
AND s.backup_finish_date > DATEADD(MONTH, -3, GETDATE()) -- Get data for the past 3 months
ORDER BY s.backup_finish_date DESC;

The output of this script provides a comprehensive overview of the backup history for the specified database. It includes details such as the recovery model, compatibility level, backup type, completion date, physical device name, duration, size (both compressed and uncompressed), backup tool used, and more.

It is worth noting that you can customize this script by adding additional fields to suit your specific requirements. For example, you can include information about encryption, collation, LSN (Log Sequence Number), and backup checksum.

Understanding your backup history is essential for maintaining the integrity and availability of your data. By regularly reviewing this information, you can ensure that your backups are being performed correctly and that you have the necessary data to restore your databases in case of any unforeseen events.

Feel free to modify the script provided to meet your specific needs and let us know which additional information you find useful in your environment to better manage your backups.