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:
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:
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:
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.