Disaster recovery - Backup locations

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:

Results screen

Script messages after execution:

("n" rows affected)

Script results detailed description:

Column nameData typeDescription
database_namenvarchar(128)Name of the database involved in the backup operation. Can be NULL.
physical_device_namenvarchar(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.
typechar(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_datedatetimeDate and time the backup operation started. Can be NULL.
backup_finish_datedatetimeDate and time the backup operation finished. Can be NULL.
backup_size_kbnumeric(20,0)Size of the backup set, in bytes. Can be NULL. For VSS backups, backup_size is an estimated value.

Leave a Reply

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