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

<pre style="background-color: #ffffff; margin: 0em; width: 100%; font-family: consolas,&#39;Courier New&#39;,courier,monospace; font-size: 12px">C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<span style="color: #0000ff">&lt;</span><span style="color: #0000ff">&lt;</span><span style="color: #800000">DATABASE</span><span style="color: #ff0000">_NAME</span><span style="color: #0000ff">&gt;</span>&gt;.mdf
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<span style="color: #0000ff">&lt;</span><span style="color: #0000ff">&lt;</span><span style="color: #800000">DATABASE</span><span style="color: #ff0000">_NAME</span><span style="color: #0000ff">&gt;</span>&gt;.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

3 Responses to "Restoring a database backup in .BAK format using SQL Server 2005"

  1. Jinsinger S.   December 1, 2009 at 12:14 am

    Great information. Solved my problem immediately

    Reply
  2. windows 7 settings   March 3, 2010 at 8:37 am

    Much appriciate for your helpful publish. I have been looking for this for a long time in many sites. Windows 7 is a amazing software ever. I suggest Win7Zilla to tweak Windows 7 effective

    Reply
  3. thatgirl1974t   May 12, 2010 at 4:51 pm

    This article had many good points, but I’m going to retweet it to my co-worker and see what they think. I’m always getting stuff in my email from them, so I might as well share some cool things I find. Thanks,

    L. Shany
    Custom software development

    Reply

Leave a Reply

Your email address will not be published.