I recently ran into a problem attempting to restore a database that I’d backed up using SQL Server 2005 Express Edition from a Windows XP (32 bit) operating system. I’d go through all the steps that I’d used in the past when attempting to restore a database backup in this manner. I would select “Restore” and point to the device (backup file) and assume all was set for a successful restoration; however I was wrong. I kept being haunted by the following error message.
Directory lookup for the file failed with the operating system error 3 "failed at c:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyBackUp.mdf”
Think about this error! I kept assuming that something must be wrong with my backup and even backed up the database AGAIN and found myself with the same error message when I tried to restore for a second time. I’ve said this before, but this is a really good time to bring up the same point. Pay attention to the error message! If I’d done this, I would have saved myself a lot of time and grief. The error message is clearly stating what is wrong. On a 64-bit operating system, the fact is that the path mentioned in the error message above DOES NOT EXIST. The backup actually needs to be restored to “c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\”. The “(x86)” part of the path is critical and seems to be created for applications that are developed specifically for 32-bit operating systems that still need to run on a 64-bit operating system. Take a look at your c:\ drive. You’ll notice that you have both a “c:\Program Files” and a “c:\Program Files (x86)”.
Anyway, here is a step-by-step guide to help you restore a database backup to a Windows Vista 64-bit operating system.
1. Within “Microsoft SQL Server Management Studio Express”, right-click on “Databases” from the object explorer (typically located on the far left side of the screen) and select “Restore Database”.
2. You’ll be shown the following dialog. You’ll need to select the source of your database backup in order to restore it. In my example, I will be restoring a database backup that was generated from within this same interface on a different machine. My example generated a “.bak” file (backup file).
3. Click the radio button labeled “From device” and then select the “…” button on the right. This will display the following dialog. You need to specify the location of your backup file.
4. Click the “Add” button and using the file browser displayed, select the source of your database backup file. Click “OK” and you’ll see the next screen.
5. Ensure that the database backup is selected (checked) and that the name of the database is listed in the dropdown above. You will need to manually perform both of these steps. If the backup was performed correctly, the database name should be in the drop-down list even if the database does not yet exist on your database server.
6. The most important step comes next. Select “Options” and you’ll be shown the following dialog.
7. The most critical step you must perform is to ensure that the “Restore As” location for both the MDF and LDF files is set to a valid path. That path needs to be “c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\YourDatabase.mdf” and “c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\YourDatabase_log.ldf”. You can also select to “Overwrite the existing database” by ensuring the box is checked. This is a personal preference and depends on your own needs. When I’m typically restoring a database, I’m also doing it because the state it is in trumps that of any database that I may already have on my server; therefore I would be checking the box.
8. Click “OK” and the database restoration should begin.
That is all! I hope this helps.