database restore problems in sql express 2005 using sql express management 2005

Posted on 2007-10-17
Last Modified: 2013-12-04
Thought you guys might have some insight on a restore problem I have run into in regards to the live database populated by dotnetnuke and trying to restore it on a local computer running sql express.

I reformatted my computer and than installed the .net framework up to 3.0 along with sql express and sql management express 2005.  When I try to restore the database now I get the following error:

restore failed for server cyboplus\dotnetnuke (
system.Data.SQlClient.sqlError: directory look up for the file e:\sqldata\Databasename_DATA.mdf failed with the operating system error 2 (the system cannot find the file specified).(Microsoft.sqlServer.Express.smo)

I recieve this error when I go to my sql managment express 2005 right click on database than left click on restore database type in the database file on the top and click on restore from device find the .bak file name within the c:\program Files\microsoft server\MSsql.1\mssql\backup\databasename.bak\

I know that it is getting this path from the options tab within the restore window.  On the left hand side their is a general tab and an options tab.  However when I try to change the location of where it wants to restore the original database file as it came off of the host server I get an error because it wants me also to deal with the path of a file called TheDataBase_log theDatabase being a generic name for the actual database file. It wants to find that in another drive called f: probably also a drive where it is on the actual host server.


My question in all of this is why is it looking to the path where the database file was backed up from to my server when I specifically specified that it ought to come from a device file located in the back up directory of my microsoft sql folder it is almost as for some reason it won't accept the fact that I want to back up the database from that location I specified but rather wants to somehow restore the original database file as it was called on the sql server of the host in the live environment.

I logged into sql as windows authentication when doing this.  I didn't run into this problem when I did restored the database before but only after formatting my computer and reinstalling windows xp pro with service pack 2 did I get these errrors.

By the way I looked and the sql services within in the admin tools services and found my instance of sql to be pointing to local machine not to that nt authentication\network services group.  That is the extent of troubleshooting I could think of and from there have just been going in circles trying to imagine what could have been different with sql server before formatting my computer and reinstaliling the database any way any help on this issue would be very appreciated thanks Lee or anyone else who might be reading this post.
Question by:cyborama
    LVL 18

    Accepted Solution

    Try running a restore script, looks what that result is. I thin kyou are looking for something like this:

    RESTORE DATABASE [dotnetnuke]
    FROM  DISK = N'D:\Path\To\Backup\dotnetnuke.bak'
    WITH  FILE = 1
    ,  MOVE N'dotnetnuke' TO N'D:\Path\To\Data\dotnetnuke_Data.mdf'
    ,  MOVE N'dotnetnuke_log' TO N'D:\Path\To\Data\dotnetnuke_Log.ldf'

    Hope this helps ...
    LVL 30

    Expert Comment

    >> why is it looking to the path where the database file was backed up from to my server when I specifically specified that it ought to come from a device file located in the back up directory of my microsoft sql folder

    I don't know... its always worked that way but it seems a bit of a pain to me. Perhaps its a hangover from SQL 6.5.

    LVL 18

    Expert Comment

    ... I think my suggestion would have solved it ?!
    And I don't come that many questions on dotnetnuke in the SQL zones ... maybe it's of some interest to the knowledge base even though the Q is actually about restoring a database on another server.

    LVL 30

    Expert Comment

    Yep I agree, give Yveau the points. Its a correct answer.

    Author Closing Comment

    I got the resolution but it was totally different than what you gave but since I can see how that would be a solution as well I've decided to give you the full 500 points

    LVL 18

    Expert Comment

    Thanks nmcdermaid and LeeTutor.
    ... and cyborama for the grade !

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Users of Windows 10 Professional can disable automatic reboots using the policy editor. This tool is not included in the Windows home edition. But don't worry! Follow the instructions below to install (a Win7) policy editor on your Windows 10 Home e…
    Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now