Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I Copied some database backups from one server to another server(new instance no databases are present).I tried restoring using below command

Restore database dbname
from disk='g:\e2bak\dbname.bak'
with 
stats=10

It failed with below error

Directory lookup for the file "G:\MSSQL$s73g\MDF\dbname.mdf" failed with the operating system error 3(The system cannot find the path specified.)Msg 3156, Level 16, State 3, Line 1

Also server level default database settings are to store data and log in

G:\MSSQL10_50.SG\MSSQL

I can overcome the error by specfying move option. But my confusion here is

1.Why database .bak file is looking for G:\MSSQL$s73g\MDF\dbname.mdf",this is the path
  of dbname database files in production from where we copied the .bak file

2.Also when I restored the database with move option and took a backup in same server,
  I was able to restore the Database with below command

Restore database dbname 
from disk='G:\e1ebk\dbname.bak'
with replace,stats=10

Can you please help me understand why .Bak file location is pointing out to some other directory(which doesn't even exist in our server and existed only on production server) and in second case why this worked without move option.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

When you take a backup all information including the file path is included as part of the backup. As you found out if you just run restore database it will try to restore to the exact same location.

when you include with replace, the backup overwrites the existing .mdf and .ldf files. I normally also have the data and log path directives to specify where to put the files (on my phone will edit to include the exact command).

this is normal behavior since if I am restoring a database on the same server you are most likely recovering from something and would want the db files to be the same.

EDIT: Here is my normal restore command, whether on the same server or different. I like to specify exactly where things are going.

RESTORE DATABASE dbName FROM DISK = N'Path\To\Backup.bak' WITH MOVE 'dbName_DataFileLogicalName' TO 'Path\To\Data.mdf', MOVE 'dbName_LogFileLogicalName' TO 'Path\To\Log.ldf', REPLACE, STATS = 10

By always specifying the files I am writing to it cuts out some uncertainty and when some one comes and looks it is very clear what is going on.

share|improve this answer

1) SQL server doesn't want to decide where to store new database files, because that could cause issues with disc space / performance if you restore a large production database to the location of your default sql server installation, typically the system drive (c:).

2) If you restore an existing database and choose to overwrite it, it will write the files to the same location, under the assumption that if the old database had enough space / performance, the restored database will also have it.

It's possible to script mass restores of multiple databases to new locations, write if you want that and I'll post a script.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.