Sql-server – Restore .Bak file to different Server

backupsql server

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.

Best Answer

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.

Related Question