Where is my backup?

6

December 12, 2012 by Kenneth Fisher

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

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.

About these ads

6 thoughts on “Where is my backup?

  1. Jason Hopkins says:

    You might also check “BackupDirectory” under Facets (default “Server” Facet), or run the following:

    DECLARE @backupLoc VARCHAR(2048);
    EXEC [master].sys.xp_instance_regread
    ‘HKEY_LOCAL_MACHINE’
    ,’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer’
    ,’BackupDirectory’
    ,@backupLoc OUTPUT
    ;
    SELECT @backupLoc AS backupLoc;

    • Unfortunately the regread makes an assumption that the default backup location is the one actually being used. I’m not certain since I’m not as comfortable with policies as I would like but my guess is that the facet is looking at the same property.

      I’ve worked with way to many servers over the years where the backup directory was set at install, but the actual location changed over time and no one ever changed it in the registry. Not to mention the servers where no one ever bothered changing it from the default to begin with and then just put the backups somewhere convenient.

  2. ksa&bMyk1d$ says:

    Very interesting article. It came very handy since I’m looking into why some of my logs shows a full backup of all my databases, put it points to a physical device name that doesn’t make sense. This article pointed me in the right direction

  3. […] last year of blogging my thoughts and queries I’ve discovered something interesting. I posted Where is my Backup? and found myself referring back to it over and over again. And that’s just one example. Turns […]

  4. […] If you have it set correctly then you just need to type the name (and possibly a subdirectory) and off you go. Otherwise you have to remember and type in the correct path. Personally I deal with 70+ servers and more coming all the time. There is no possible way I’m going to be able to remember all of the backup paths. I have to look them up each time. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 397 other followers

%d bloggers like this: