We recently added responsibility for ~80 more servers to our team. This means that my team-mates and I are working on servers that we aren’t familiar with. And that means that we don’t know where the backups are stored. So yesterday when I had to do an ad-hoc backup for a database on one of these servers, I had to go searching for the backup location. Now this isn’t the most common task in the world but it does come up every now and again. So I thought I would share my knowledge (not extensive) of where the backup information is kept.

First all of the backup information is in the MSDB database. From there if you do a query on sys.tables for anything LIKE ‘%backup%’ you get the following tables:

backupmediaset
backupmediafamily
backupset
backupfilegroup
backupfile

For this particular task we need the tables backupset and backupmediafamily.

BOL says the following about backupset:
Contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. RESTORE, RESTORE FILELISTONLY, RESTORE HEADERONLY, and RESTORE VERIFYONLY statements operate on a single backup set within the media set on the specified backup device or devices.

Some of the interesting columns in this table are:

• backup_set_id – Unique id for the backup set.
• media_set_id – Unique id for the media set. References back to backupmediafamily.
• backup_finish_date – Datetime the backup completed (Note: This is more important than when the backup started. Data in the backup includes transactions up to the finish time, not just the start time.)
• database_name – Name of the database backed up.
• Name – Name of the backup.
• type – Type of the backup. Possible types include:
  • D = Database
  • I = Differential database
  • L = Log
  • F = File or filegroup
  • G =Differential file
  • P = Partial
  • Q = Differential partial

Of course there are other columns with interesting information, but these include the ones we need for this.

BOL on backupmediafamily says:
Contains one row for each media family. If a media family resides in a mirrored media set, the family has a separate row for each mirror in the media set.

And the columns I’m going to mention for this one are:

• media_set_id – Unique id for the media set.
• logical_device_name – Name of the media set in sys.backup_devices.name if the backup device is permanent (see device_type).
• physical_device_name – Physical name and location of the backup device.
• device_type – type of backup device
  • Disk
    • 2 – temporary
    • 102 – permanent
  • Tape
    • 5 – temporary
    • 105 – permanent
  • Pipe – SQL 2000
    • 6 – temporary
    • 106 – permanent
  • Virtual
    • 7 – This isn’t in BOL so I’m going off of what I found here: http://www.developmentnow.com/g/118_2004_11_0_0_479070/device-type-7-in-backupmediafamily.htm. Anthony Thomas said: Looks like a virtual_device used to create backups using the VBDI API. The
      Veritas SQL Server client uses this among others.

A backup device is considered permanent if it is stored in sys.backup_devices. Created by using sp_addumpdevice.

So using the above tables to find where my backups are located I can use the following query:

SELECT TOP 10 backupset.database_name, backupset.type, backupset.name,
backupmediafamily.physical_device_name, backupset.backup_finish_date,
backupmediafamily.logical_device_name, backupmediafamily.device_type
FROM backupset
JOIN backupmediafamily
ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE backupset.type = 'D'
ORDER BY backupset.backup_finish_date DESC, backupset.database_name DESC

This query returns the last 10 backups of a given type based on what you put in the WHERE clause. In this case full backups. Because different types of backups (say Logs and Fulls) are stored in different locations you should always check based on the type of backup you are going to do. By looking in the physical_device_name column you can see where the backups are being stored. With any luck they are all being put in the same place. If not then use some logic based on how many are in any given location, maybe expand the query to the most recent 100 etc.

Fair warning, the backups that I work with are all fairly straightforward. No mirrors, multiple backup devices etc. There may be additional difficulties at that point.