MSSQL - Last Backup time / size

9. August 2011 18:00

 

In mssql server it is possible to find the last backup time as well as the largest size or compressed size of the backups. This can be down by listing the databases using the system table sys.databases and reading from the msdb.dbo.backupset which is one of the tables that is updated when a backup is taken on the server. The sizes returned will be in bytes.

 

SELECT db.name AS DBName,
	COALESCE(CONVERT(VARCHAR(MAX), MAX(bs.backup_finish_date), 101), 'N/A') as LastBackup,
	MAX(bs.backup_size) AS LargestBackup,
	MAX(bs.backup_size) AS LargestCompressedBackup
	FROM sys.databases db
	LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = db.name
GROUP BY db.name
ORDER BY db.name

 

It would also be possible to include the maximum time a backup has taken for a database by modifiing the sql above and using datediff and max on the start and finish time of the backup. So the following can be used to find out how much space and time a backup will be required for each database.

 

SELECT db.name AS DBName,
	COALESCE(CONVERT(VARCHAR(MAX), MAX(bs.backup_finish_date), 101), 'N/A') as LastBackup,
	COALESCE(CONVERT(VARCHAR(MAX), MAX(DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date)), 101), 'N/A') as RunTime,
	MAX(bs.backup_size) AS LargestBackup,
	MAX(bs.backup_size) AS LargestCompressedBackup
	FROM sys.databases db
	LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = db.name
GROUP BY db.name
ORDER BY db.name

 

The msdb.dbo.backupset is usful for finding out the entire history of backups taken on a database. More information can be found out about it at http://technet.microsoft.com/en-us/library/ms186299.aspx

 

E-mail Kick it! DZone it! del.icio.us Permalink