Error restoring database backup

Question

I am getting an error using SQL Server 2012 when restoring a backup made with a previous version (SQL Server 2008). I actually have several backup files of the same database (taken at different times in the past). The newest ones are restored without any problems; however, one of them gives the following error:

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\MYDB_ABC.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.SmoExtended)

This is a x64 machine, and my database file(s) are in this location: c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL.

I do not understand why it tries to restore on MSSQL.1 and not MSSQL11.MSSQLSERVER.

Answer 1

Sounds like the backup was taken on a machine whose paths do not match yours. Try performing the backup using T-SQL instead of the UI. Also make sure that the paths you're specifying actually exist and that there isn't already a copy of these mdf/ldf files in there.

RESTORE DATABASE MYDB_ABC FROM DISK = 'C:\path\file.bak' WITH MOVE 'mydb' TO 'c:\valid_data_path\MYDB_ABC.mdf', MOVE 'mydb_log' TO 'c:\valid_log_path\MYDB_ABC.ldf'; 

Answer 2

When restoring, under Files, check 'Relocate all files to folder'