Command to know the Name of the database of a backup file

Question

Command to know the Name of the database of a backup file

Lester Policarpio

SSCertifiable

Points: 7109
More actions
May 4, 2008 at 6:57 pm

#70354

Hi GudAM just want to ask if there are any sql command/s to see the database name of a backup file? for example I have a database named Lester and the backup file of that database is backupDB_db_200805050856.BAK using tsql can i view the backupfile (using some sort of sql command) and it will output Lester as its database name?
Any help is fully appreciated Thanks...
"-=Still Learning=-"
Lester Policarpio

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

In short... no. There's no command available to get a backup file to identify the database(s) that have been backed up in the file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 2

ohhh... ok thanks.. i guess my backup script will be stuck in the mssql format (dbname_db_YYYYDDMMHHMM.BAK) 🙂

"-=Still Learning=-"

Lester Policarpio

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If you do a Restore Headeronly, you get a resultset with details on each backup that's contained within the file. The DB name is one of the things returned.

restore headeronly FROM DISK = '<Full path and name of backup file>'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 4

Woah!!!! thank you very much this is indeed helpfull I'll try to modify my script tomorrow. I'm almost hopeless for this one.

"-=Still Learning=-"

Lester Policarpio

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Note it's a resultset, so it's going to be difficult to get into a variable, unless you do something like this.

insert into SomeTable

execute ('Restore .... ')

select @SomeVar = [Database] From SomeTable ....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bob-683340 SSC Journeyman Points: 91 More actions · Answer 6

Hmm, why not this?

use msdb

select

database_name

from

backupmediafamily f

inner join backupset s on s.backup_set_id = f.media_set_id

where

physical_device_name = 'backupDB_db_200805050856.BAK '

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Yup. That will work great if the backup was created on the server where the query is being run.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gordon-265412 SSCrazy Points: 2898 More actions · Answer 8

GilaMonster (5/5/2008)
Yup. That will work great if the backup was created on the server where the query is being run.

Got to watch out for MSDB history purges too...

Your friendly High-Tech Janitor... 🙂

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 9

MSDB works, but it's not reliable for reasons mentioned above (needs to be same server, can be purged, etc)

What are you trying to achieve?