Backup Report

I was working on one the project and they were looking for a backup report which should specify whether it’s physical or logical and few more details. Whatever data I fetched looks good and I felt the same can be helpful for you guys to pull it as a report. If you need any more additions to it please let me know.

Applies To

SQL Server 2005 and Above

SELECT DATENAME([weekday], [bs].[backup_start_date]) [Day Of the Week],
										   CASE [bmf].[device_type]
											  WHEN 2
											  THEN 'Physical'
											  WHEN 5
											  THEN 'Tape'
											  WHEN 7
											  THEN 'Virtual'
											  WHEN 7
											  THEN 'Permanent Backup'
										   END [Virtual\Native],
											  CASE [bs].[type]
												 WHEN 'D'
												 THEN 'Full'
												 WHEN 'I'
												 THEN 'Differential'
												 WHEN 'L'
												 THEN 'Log'
											  END [BackupType],
	  [database_name] [DatabaseName],
	  [bs].[backup_start_date] [StartTime],
	  CONVERT( VARCHAR(10), DATEDIFF([ss], [bs].[backup_start_date], [bs].[backup_finish_date])) + ' seconds' [BackupDuration],
	  [bs].[description] [Description],
						WHEN [bs].[last_family_number] = 1
						THEN 'SplitBackup-BackupSet-' + CONVERT(    VARCHAR(10), [bs].[backup_set_id]) + '-File ' + CONVERT(VARCHAR(4), [bmf].[family_sequence_number])
						ELSE 'Single File'
					 END [SplitBackup],
	  [bmf].[physical_device_name] [BackupDestination],
	  CONVERT( VARCHAR(15), FLOOR([bs].[backup_size] / [bs].[last_family_number] / 1048576)) + ' MB' [BackupSize],
																					 WHEN [bs].[backup_size] = [bs].[compressed_backup_size]
																					 THEN 'Uncompressed'
																					 ELSE 'Compressed'
																				  END [Compression],
																						WHEN [bs].[is_snapshot] = 1
																						  OR [bs].[is_copy_only] = 1
																						THEN 1
																						ELSE 0
																					 END [Snapshot\CopyOnly]
FROM [msdb]..[backupset] [bs]
	INNER JOIN [msdb]..[backupmediafamily] [bmf] ON [bmf].[media_set_id] = [bs].[media_set_id]
ORDER BY [bs].[backup_start_date] DESC;


Leave a Reply

Join the Conversation


Leave a comment

Your email address will not be published. Required fields are marked *



Related Posts

Issues restoring SQL Azure bacpac due to QueryStoreStaleQueryThreshold
In our environment we have inhouse and SQL Azure databases. We will be refreshing the DEV environments weekly once from the production SQL Azure ...
T-SQL Script to find the names of the StoredProcedure that has used dynamic SQL
This article has the script that will be useful to find the names of the  Stored procedure that has used dynamic sql within the definition of the...
Retrieve Auditing Configuration Details
Script to retrieve auditing configuration details in sql server. Auditing feature is released from SQL Server 2008 onward so if you have utilized...
powered by RelatedPosts