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
