Getting Important Information of SQL Server Backups

Regardless of the method employed to take SQL Server backups, they must be carefully monitored around the clock, particularly in critical database environments where data changes rapidly. Keeping backups current is vital, as they can save considerable time when it comes to recovering databases in the event of unforeseen incidents. Furthermore, it has been disappointing to observe numerous environments lacking a backup strategy, often with no one designated to oversee them. In this context, it is evident that these businesses were at significant risk of losing vast amounts of data and incurring substantial financial losses.

Sometimes, there is a belief that backing up the entire virtual machine is sufficient; however, this type of backup serves a different purpose than a dedicated database backup. Consequently, SQL Server database backups cannot be substituted with virtual machine backups, which can be time-consuming, inadequate, and impractical for databases. Thus, implementing an appropriate database backup strategy (for example, with Full + Differential + Log Backups) and closely monitoring them are essential tasks for ensuring the recovery process of databases.

Whether you have a database backup strategy in place or simply wish to check the status of a backup, having a useful script can be advantageous. Today, I am sharing a script that provides details about the duration of backups, the size of the backup files, the individual who performed the backup, and whether the backup was executed using the CHECKSUM option, among other information. This script retrieves essential details about backups for all databases within a SQL Server instance. For example, you can filter for full backups (type='D'), log backups (type='L'), and differential backups (type='I').

SELECT isnull(d.name,'') DatabaseName,
CASE is_read_only WHEN 1 THEN 'YES' ELSE 'NO' END as IsReadOnly, 
cast(sum(size)/128.0/1024.0 as decimal(8,2) ) as DBSize_InGB ,  
isnull( last_backup.recovery_model,'')RecoveryModel,isnull(last_backup.name,'') as BackupName ,
isnull(cast(last_backup.backup_start_date as varchar(24)),'(No Taken)') as BackupStartDate,  
isnull(case when [backup_size]>1024 then  cast( cast([backup_size]/1024.0 as decimal(8,2)) as varchar(12)) 
     + 'GB' else cast([backup_size] as varchar(12)) + 'MB' end,0) as BackupSize,
isnull(case when compressed_backup_size>1024 then  cast( cast(compressed_backup_size/1024.0 as decimal(8,2)) as varchar(12)) +
     'GB' else cast(compressed_backup_size as varchar(12)) + 'MB' end,0) as CompressedBackupSize ,
 isnull([duration],'') as Duration, isnull(is_copy_only,'') as IsCopyOnly, isnull(is_damaged,'') as IsDamaged,  
 isnull(has_backup_checksums,'') as HasBackupCheckSUM, isnull([user_name],'')as UserName
FROM master.sys.sysaltfiles  f
    inner join sys.databases d on f.dbid=d.database_id
    inner join (select database_name,backup_start_date, name, is_damaged,is_password_protected,
                has_incomplete_metadata,is_copy_only, cast(backup_size/1024.0/1024.0 as decimal(8,2))  as [backup_size], 
                cast(compressed_backup_size/1024.0/1024.0 as decimal(8,2))  as compressed_backup_size,
                isnull(cast(  CAST((DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date))/3600 AS varchar) + ' hours ' 
                     + CAST(((DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date))%3600 )/60 AS varchar)+ ' min'    as varchar(40) 
                ), '(No Available)') as [duration],bs.backup_finish_date, has_backup_checksums , [user_name], recovery_model
              FROM msdb.dbo.backupset bs
              WHERE bs.backup_set_id IN (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset
                                                      where    type = 'D'
                                                     GROUP BY database_name) 
                AND bs.database_name IN (SELECT name FROM master.dbo.sysdatabases  )
                                ) as last_backup   on d.name = last_backup.database_name
                             where fileid<>2    
                             group by dbid,d.name , d.is_read_only,d.recovery_model_desc, last_backup.database_name, 
last_backup.backup_start_date,last_backup.[backup_size] ,last_backup.compressed_backup_size,last_backup.name, last_backup.duration,
 last_backup.backup_finish_date,is_copy_only,is_damaged,has_backup_checksums , [user_name], last_backup.recovery_model
ORDER BY DATEDIFF(minute,  last_backup.backup_start_date,  last_backup.backup_finish_date) desc

That is all for now, let me know any remarks you may have.