Hi, I am using SQLEXPR 2008. I have take database Backup from 2008 SQL, Using Script and tried to restore the database on another machine having SQLEXPR 2012 Installed using SQL Script and getting Following  error 

SQL Script :

1.BACK UP :  BACKUP DATABASE test TO DISK='D:\SQLServerBackups\test.dat'

2.RESTORE: RESTORE DATABASE test FROM DISK = 'D:\SQLServerBackups\test.dat' WITH REPLACE

FOLLOWING ERROR

1------------------------ 1 --- > WHY THIS ERROR -- THIS PATH WILL NOT BE THERE ON NEW MACHINE
Directory lookup for the file "C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.APAYROLL\MSSQL\DATA\test.mdf" failed with the operating system error 3(The system cannot find the path specified.).

2------------------------ 2
File 'test' cannot be restored to 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.APAYROLL\MSSQL\DATA\test.mdf'. Use WITH MOVE to identify a valid location for the file.

3------------------------ 3
Directory lookup for the file "C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.APAYROLL\MSSQL\DATA\test_log.LDF" failed with the operating system error 3(The system cannot find the path specified.).

4------------------------ 4
File 'tran_BBLMS_Jan2015_Dec2015_log' cannot be restored to 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.APAYROLL\MSSQL\DATA\test_log.LDF'. Use WITH MOVE to identify a valid location for the file.

Please take a look at this thread:

RESTORE DATABASE WITH REPLACE Fails in SQL 2012 (Works in 2005/2008)

1------------------------ 1 --- > WHY THIS ERROR -- THIS PATH WILL NOT BE THERE ON NEW MACHINE
[..] operating system error 3(The system cannot find the path specified.).

You say that the path does not exits and then you're surprised, that you'll get an error message which tells you exactly this. Come on, this is clear and totally obvious: How should be something restored to a non-existing path? This simply not possible.

Thus the hint in the further error message about the MOVE clause. The RESTORE command - without MOVE clause - tries to restore the database files at the same physical path from where the backup was tkaen. The move clause specifies a differente restore path. So just specify an existing path in the MOVE clause.

• Proposed as answer by Kalman TothModerator Saturday, February 21, 2015 1:50 PM

Hi, thanks for the reply,

I know that this path is not there on other machine. but i wanted to know why it's looking for that path, when i tried to restore database on to SQL 2012.

Because when you use RESTORE DATABASE statement to restore by default, data and log files are restored to their original locations. Locations of mdf and ldf as it was when it backed up. To relocate a database, we have to use MOVE option to relocate each of the database files.

You can do simple RESTORE FILELISTONLY option to see a list of the files that were backed up and their original locations. 

RESTORE FILELISTONLY FROM DISK = 'C:\AdventureWorks.BAK'

This is where SQL Server will create mdf and ldf when doing the restore on any server.

SQL Server 2008 is product version 10.xxx and the default path used by a SQL Server 2008 has this as you can see in your example -

C:\Program Files (x86)\Microsoft SQL Server\MSSQL10

The backup includes this path for the files. This is where it will try to restore them unless you specify otherwise.

The SQL Server 2012 is product version 11.xxx and it has \MSSQL11 mentioned in its path. Your SQL Server 2012 server does not have the MSSQL10 directory. So the backup restore fails.

You need to use the MOVE keyword in the command.

The REPLACE keyword that you are using means to overwrite an existing database with the same name in the SQL Server.

Reference -

Restore a Database to a New Location (SQL Server)
https://msdn.microsoft.com/en-us/library/ms186390(v=sql.110).aspx