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 modifying 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 useful 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



Did You find this page useful?

Yes No



Last Modified: 12 December 2016

Releated Posts


2012-08-07 - MSSQL - Finding the database restore history
2011-09-24 - MSSQL - Checking Uptime
2011-08-11 - MSSQL - Saving memory by using stored procedures
2011-08-09 - MSSQL - Last Backup time and size
2011-06-24 - MSSQL - Extracting part of an email address
2011-06-15 - MSSQL - Bulk Deleting rows with backup
2011-06-07 - MSSQL - DBCC Check All Databases
2011-06-03 - MSSQL - Who locks what
2011-05-26 - MSSQL - RandomString Function
2011-05-22 - MSSQL - Removing the aspnet membership database
2011-05-19 - MSSQL - ShrinkLog
2011-05-16 - MSSQL - Enum
2011-05-05 - MSSQL - Log Sizes
2011-03-01 - MSSQL - Kill connections by host
2011-02-23 - C Sharp / MSSQL Get inserted value of NEWSEQUENTIALID()
2011-02-22 - MSSQL - Kill connections by username
2011-02-20 - MSSQL - Unsigned int
2011-02-20 - MSSQL - All temp tables are global
2011-02-17 - MSSQL - Convert IP To big int
2011-02-05 - MSSQL - Levenshtein
2011-02-02 - MSSQL - Kill connections to a database
2011-02-01 - MSSQL - Convert unix timestamp to date time
2011-01-30 - MSSQL - RegEx Support
2011-01-30 - MSSQL - Adding MD5 checksum support
2011-01-30 - MSSQL - Adding SHASum support
2011-01-30 - MSSQL - Enabling CLR Functions
2011-01-29 - MSSQL - TRIM Function