RSS

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.

DECLARE @BACKUPNAME SYSNAME
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 = master.sys.sysdatabases.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

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

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

 
%d bloggers like this: