Finding backup files

Do you ever get thrown into a situation where a user needs to restore a database you have never heard of?  Of course you do.  That’s what happens when you are a DBA.  This week a co-worker was off sick and he had been the sole person working with one of our new clients.  They had been doing user testing and now wanted to go back to a previous backup. The testers needed a restore of database XYZ from 6pm last night, urgently so they could continue testing.    I knew that if the database was called XYZ, then the server was named XYZ-UAT.  Ok so, one puzzle solved.  The standard backups had not been setup yet, there was no way to say where the backups got put on box it was using a shared testing server.   I wrote a few queries to find the answer and realized it would be really easy to put it into a quick single query to find all the backups for a single database.   

declare @db as varchar(30) = 'XYZ-UAT'

select physical_device_name,backup_start_date

from msdb.dbo.backupmediafamily bmf

inner join msdb.dbo.backupmediaset bms

on bmf.media_set_id = bms.media_set_id

inner join msdb.dbo.backupset bs

on bms.media_set_id = bs.media_set_id

where bs.database_name = @db

Just put in the name of the database and voila you have the location of all the backups.

This would be handy for system administrators as well or to run in a PowerShell script to find all the backups to move to a different disk, you would just need to add a statement to get all the databases or the ones you want.