Restoring a database backup in .BAK format using SQL Server 2005

In most cases, you will be able to properly restore a SQL Server backup file in .BAK format using SQL Server 2005. But sometimes you will encounter the following error

Restore failed for Server ‘YOUR_COMPUTER_NAME’.  (Microsoft.SqlServer.Smo)

System.Data.SqlClient.SqlError: Directory lookup for the file "DRIVE:\SQL 2005\MSSQL.1\MSSQL\DATA\<<YOUR_DATABASE_NAME.mdf>>" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

Follow the steps mentioned below to resolve the above mentioned error.

(1) Right click Databases tree and select Restore Database

(2) Enter a database name in "To database" field

(3) Select From Device option and click the Ellipse. Click Add button and select the database file name in .BAK format

(4) Check mark the checkbox below the Restore column

(5) Select Options section and you will find two colums titled "Original File Name" and "Restore As".

(6) The error can be fixed by changing the Restore As path for both data and log file. The correct path should be

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<<DATABASE_NAME>>.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<<DATABASE_NAME>>.ldf

(7) You will now be able to restore the backup without any problem.

NOTE: You need to follow the steps 5, 6 and 7 only if you encounter the above mentioned error. Otherwise, you can finish the task in Step 4 itself.

sql server, sql server 2005, database, backup, restore