Adding a database from a .bak file

Question

Adding a database from a .bak file

sadvani

SSC Veteran

Points: 217
More actions
November 21, 2007 at 1:42 pm

#200647

Hello-
I am brand, brand new to SQL Server and am trying to ramp up to a somewhat functional level user.....
Anyway I have a .bak file I was given from a client on a disc in a zipped file. I extracted the zip file to the "backup" folder in the MSSQL folder, then tried to Restore Database from the Object Explorer window in the management studio.
Error message:
System.Data.SqlClient.SqlError: Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL\Data\EDMSPDMiami_Data.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Express.Smo)
The pathway for the .mdf file looks like it is pointing to my CD drive (D:\). Can anyone tell me what is going on?

Viewing 5 posts - 1 through 4 (of 4 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

The backup operation will read the .mdf and .ldf file locations from the backup header. So by default, it will try to restore the database to the exact same location where the original files were located. This sometimes causes problems when restoring a database to a new machine as the directory structure may be vastly different. You could either create the correct folder structure that SQL Server is referring to as missing or in the GUI, change the file location before you begin the restore. I prefer to do all of this via script as I do not like the GUI for backup/restores, but you should be able to find it OK. Let me know if you continue to have problems.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

ramoha SSCommitted Points: 1909 More actions · Answer 2

For issues with backups taken from other locations please use Restore script with "move" option. Check booksonline for exact syntax.

Razi, M.
http://questivity.com/it-training.html

sadvani SSC Veteran Points: 217 More actions · Answer 3

Thanks - changing things using the GUI - from the "Options" area - did the trick (once I realized the file structure I had and that the backup implied were not the same).

Best,

Sanjay

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 4

you can look how the database was configured before it was backed up using restore filelistonly command, like here:

restore filelistonly from disk='E:\sqlbackup\mydatabase20071121.bak'

...and your only reply is slàinte mhath