Getting Backup File Information on SQL Server 2005/SQL Server 2008

In a lot of instances, you may be doing something like database mirroring or a migration job that involves a lot of databases and possibly a lot of backup files, especially transaction log backups. It’s a real pain if you have to restore these files by hand. It would be much easier if you have a way to get a listing of all of the backup files for a particular set of databases so that you could write some code to quickly produce a script to do them all at once for you. With all those backup files, you will also need a way to receive files if you are transferring them from server to server. To do this you need 3 ways to quickly receive large files.

Luckily, SQL Server maintains such a log between the two tables in the msdb database: Backupset & BackupmediaFamily

A simple piece of T-SQL code can then be used to return the information on the all the backup ran for that particular instance. Then it is just a matter of wrapping this knowledge up in a process to output the appropriate script for the particular restore process that you are needing to do.

Below is a sample T-SQL query that can be used on both SQL Server 2005 and SQL Server 2008:

SELECT database_name
       ,user_name as ExecutedByUser
       ,physical_device_name
       ,backup_finish_date
FROM msdb..backupset bckupset 
     INNER JOIN msdb..backupmediafamily bckmedia 
     ON bckupset.media_set_id = bckmedia.media_set_id

Cheers,
AJ