Fix & Causes of Issue Use WITH MOVE to identify a valid location for the file While Restore Database one instance to other

Fix & Causes of Issue Use WITH MOVE to identify a valid location for the file While Restore Database one instance to other

Whenever you Restored DB from one instance to another you will get above Error

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.

let see how to solve that

Let see some Source and destination instance

Source DB

Destination DB see below

Now am going to take backup on Source Instance Welcome-PC\MSSQL05 as see below

Now am going to try it normal RESTORE With Recovery It will give error as see below

To Fix this issue i used to do /try to do RESTORE WITH Norecovery,Replace option it will give error as we saw Earlier

Here Problem is with Logical Name in Sp_helpdb bcos Source DB and Destination DB is differ

Source db sp_helpdb

 

destionation db sp_helpdb

Solution 1:
Normally we can able to solve using GUI as choose ellipses to locate/point out Destination db physical file

as see below

If we need to achieve this Result we should consider With Move option or put Logical Name as correct as see below

1) We should give Source Logical name with Destionation Path Location

RESTORE database VT
From Disk='F:\To move and check on db\VirendraTest_bk.Bak'
 WITH MOVE 'VirendraTest' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\VT.mdf',
 MOVE 'VirendraTest_log' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\VT_log.LDF',
REPLACE,STATS=10

as see below

Otherwise it will give Error

Also if you need furthur more files needs to restore use WITH MOVE,REPLACE,NORECOVERY
as see below

RESTORE database VT
From Disk='F:\To move and check on db\VirendraTest_bk.Bak'
 WITH MOVE 'VirendraTest' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\VT.mdf',
 MOVE 'VirendraTest_log' TO 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\VT_log.LDF',
REPLACE,STATS=10,NoRECOVERY

Now you can see it is restoring mode so it allows to add differential or log or both

Kindly see now finally use WITH RECOVERY to finish RESTORE

When you click Refresh on Server it is ready you can see tables

but you need to solve orphan users using sp_change_users_login and sp_help_rev_login to copy login script

That is it.

Keep Learning!!!!!!!!!!!!!!!!!!!!!!!