Restore with bak

Question

Restore with bak

TRACEY-320982

SSChampion

Points: 13653
More actions
December 22, 2008 at 3:34 pm

#203274

Is there a way to determine which bak was used in a restore of a database.
i.e if i used test1234.bak to restore databass TEST
Can i find this information in any system file .
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

It might be in the SQL Server Logs.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

If it's not in the logs, I think you're stuck.

http://msdn.microsoft.com/en-us/library/ms187408(SQL.90).aspx

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 3

Use msdb;

Select *

From dbo.restorehistory r

Inner Join dbo.backupset b2 On b2.backup_set_id = r.backup_set_id

Inner Join dbo.backupmediaset b3 On b3.media_set_id = b2.media_set_id

Inner Join dbo.backupmediafamily b4 On b4.media_set_id = b3.media_set_id

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Neat query Jeffery. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 5

That works if you're restoring same server, but if this were DR or on another box, you wouldn't know, would you?

rajiniforu Ten Centuries Points: 1147 More actions · Answer 6

rajiniforu

Ten Centuries

Points: 1147

December 22, 2008 at 9:15 pm

#916540

Nice Query Jeffery, its very useful. Thank you [:)]

rajiniforu Ten Centuries Points: 1147 More actions · Answer 7

Hi Steve,

I have tried restoing a test database from a network backup and still it works. It gives the phisical_device_name as network backup location \\servername\backupshare\backupfilename.

Please correct me if I misunderstood your question.

Regards,

Rajini

Jeffrey Williams SSC Guru Points: 90305 More actions · Answer 8

Steve Jones - Editor (12/22/2008)
That works if you're restoring same server, but if this were DR or on another box, you wouldn't know, would you?

As long as you are not restoring msdb - then it should work. This should show the restore history on the system you run it.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Atul DBA Hall of Fame Points: 3610 More actions · Answer 9

Good query by Jeffery. Yes it will work untill the msdb is not restored post user database restoration.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 10

TRACEY-320982

SSChampion

Points: 13653

December 23, 2008 at 8:32 am

#916798

Thanks for the details.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 11

I had to just confess i restored the wrong DB....ouch that hurts.

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 12

restore header_only from disk = 'your bak file path'

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj