Now WHERE did I put that backup?

07 May

Ever run into a situation where you’re in a hurry, working on a dev box, for a presentation, and you back up your database in an “easy” place, and then when it’s time to do the restore, can’t remember where you put it?

Yeah, me neither…

<this is your cue to chuckle a bit, because I think we’ve all done it a time or two>

In fact, it was Karen Lopez (twitter | blog) who got me looking for this script, and I thought I’d put it out there.  Nothing proprietary about it – it’s just a script to find a backup – but it can be useful when you’re trying to find out where in all the clutter of files you’ve got on boxes the one backup you actually need really is.

At any rate, some time ago I wrote this script that showed me where the last backup was of a database – run from within that database.

Take a look – let me know what you think.  If you can improve it, by all means, feel free to let me know that too.  Enjoy.

SELECT @BackupName = (
SELECT     TOP (1) backupmediafamily.physical_device_name
FROM         msdb.dbo.backupmediaset AS backupmediaset_1
INNER JOIN        msdb.dbo.backupmediaset
ON                msdb.dbo.backupmediaset.media_set_id = backupmediaset_1.media_set_id
INNER JOIN        msdb.dbo.backupset
ON                msdb.dbo.backupmediaset.media_set_id = msdb.dbo.backupset.media_set_id
AND                backupmediaset_1.media_set_id = msdb.dbo.backupset.media_set_id
INNER JOIN      msdb.dbo.backupfile
INNER JOIN        msdb.dbo.backupfilegroup
ON                msdb.dbo.backupfile.backup_set_id = msdb.dbo.backupfilegroup.backup_set_id
ON                msdb.dbo.backupset.backup_set_id = msdb.dbo.backupfile.backup_set_id
AND             msdb.dbo.backupset.backup_set_id = msdb.dbo.backupfilegroup.backup_set_id
INNER JOIN        master.sys.sysdatabases
ON                backupset.database_name =
INNER JOIN         msdb.dbo.backupmediafamily
ON                msdb.dbo.backupmediaset.media_set_id = msdb.dbo.backupmediafamily.media_set_id
AND             backupmediaset_1.media_set_id = msdb.dbo.backupmediafamily.media_set_id)
print        ‘backupname ‘
print        @backupname

Leave a comment

Posted by on May 7, 2012 in Uncategorized


Tags: , ,

Leave a Reply

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

You are commenting using your 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

%d bloggers like this: