Compatible from SQL Server 2008
Returns list of backups created over all databases, with location, timings and size information. This script is useful when you want to quickly check where your database backups are stored, or find out if there is some additional backup software that can be breaking your backup chain. Some file system backup software can be touching also your databases when SQL Writer Service is running.
You can also identify from where you need to get database backups needed to fit actual recovery scenario, for example when it is needed to restore from tape or some long-term storage.
SELECT database_name, physical_device_name, type, backup_start_date, backup_finish_date, backup_size / 1024.0 FROM msdb.dbo.backupset b INNER JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id ORDER BY backup_finish_date DESC
Results
Script results screen:
Script messages after execution:
("n" rows affected)
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| database_name | nvarchar(128) | Name of the database involved in the backup operation. Can be NULL. |
| physical_device_name | nvarchar(260) | Physical name of the backup device. Can be NULL. This field is shared between backup and restore process. It may contain the original backup destination path or the original restore source path. Depending on whether backup or restore occurred first on a server for a database. Note that consecutive restores from the same backup file will not update the path regardless of it's location at restore time. Because of this, physical_device_name field cannot be used to see the restore path used. |
| type | char(1) | Backup type. Can be: D = Database I = Differential database L = Log F = File or filegroup G =Differential file P = Partial Q = Differential partial Can be NULL. |
| backup_start_date | datetime | Date and time the backup operation started. Can be NULL. |
| backup_finish_date | datetime | Date and time the backup operation finished. Can be NULL. |
| backup_size_kb | numeric(20,0) | Size of the backup set, in bytes. Can be NULL. For VSS backups, backup_size is an estimated value. |