We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

RESTORE DATABASE with MOVE TO option

AndyAinscow
AndyAinscow asked
on
Medium Priority
946 Views
Last Modified: 2012-05-07
I have a problem with restoring databases from backups.  I'll try to explain.

The situation is that I will get a BAK file (full backup) performed programatically with the following T-SQL statement
BACKUP DATABASE [dbNameGoesHere] TO DISK = 'somepath\BackupFile.bak'
The file name (BackupFile.bak) is invariant however the name of the database is not necessarily known to me nor is the original file path of the backup or database.

I want to be able to restore to a specific location on my system. eg.
RESTORE DATABASE [foo] FROM DISK = 'x:\BackupFile.BAK' MOVE xxx_Data TO 'D:\foo\mydata.mdf', MOVE xxx_Log To 'D:\foo\mydata_log.ldf'

The problem is that I don't know what the logical names for the data and the log are inside the .bak file.
How do I find that information OR is there a way around without requiring the logical names?

In a nutshell I would like the T-SQL statement to achieve what I want.
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
run this to get the logical file info

restore filelistonly FROM DISK = 'x:\BackupFile.BAK'
AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Author

Commented:
OK, but then what ?  
Do you mean this will do the job?

restore filelistonly FROM DISK = 'x:\BackupFile.BAK' ;  RESTORE DATABASE [foo] FROM DISK = 'x:\BackupFile.BAK' MOVE xxx_Data TO 'D:\foo\mydata.mdf', MOVE xxx_Log To 'D:\foo\mydata_log.ldf'
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
once you run the filelist only, you will get the contents of the backup file including the logical file name
 then you can use that logicall name to modify your 'WITH MOVE ' statement
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Author

Commented:
Thanks.
I'll give that a try tomorrow (late evening here now) and get back to you with the results.
AndyAinscowFreelance programmer / Consultant
CERTIFIED EXPERT

Author

Commented:
I've not been able to get the thing to run with that as one SQL statement.  :-(

However I have used the FILELISTONLY part to build a recordset and in code get the contents of the first field (logical name) in the returned set.  I can then use this to build the T-SQL command without the FILELISTONLY part.  That does work, maybe not quite as efficiently but it works.  Mission accomplished.

Thanks for your help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.