locked
Help with an error message RRS feed

  • Question

  • I keep getting this error message when I try to restore a database backup that I have made.

    System.Data.SqlClient.SqlError: Directory lookup for the file "D:\Databases\GTT_Data.MDF" failed with the operating system error 5(error not found). (Microsoft.SqlServer.Express.Smo)

     

    What I have done is created a backup of my sql sever database and I am now trying to restore it into sql server express for someone to play around with without  it affecting my actual database. This is where the error messages are coming from.

    Please help

    Friday, October 6, 2006 10:35 AM

Answers

  • Hi,

    look in your BOL (Books online, the Online help of SQL Server)

    There is a point Restore a database and move Files which will help you

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/877ecd57-3f2e-4237-890a-08f16e944ef1.htm

    RESTORE DATABASE MyAdvWorks
       FROM MyAdvWorks_1
       WITH NORECOVERY,
          MOVE 'MyAdvWorks' TO
    'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf',
          MOVE 'MyAdvWorksLog1'
    TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf'
    RESTORE LOG MyAdvWorks
       FROM MyAdvWorksLog1
       WITH RECOVERY

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Friday, October 6, 2006 3:12 PM

All replies

  • Are you getting this during the restore ? If the machines are not the same for backup and restore you might not have the drives / folders available as they are on the original system. If so you either have to create those folders or Restore the database with the WITH MOVE option to specify a new destination. (thats because the original location is tored in the backup file).

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Friday, October 6, 2006 11:50 AM
  • Yes its during the restore. Yes the machines are not the same. Please could you give me further details on how to restore the databse with the WITH MOVE option.

     

    Thanks for your help

    Friday, October 6, 2006 12:18 PM
  • Hi,

    look in your BOL (Books online, the Online help of SQL Server)

    There is a point Restore a database and move Files which will help you

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/877ecd57-3f2e-4237-890a-08f16e944ef1.htm

    RESTORE DATABASE MyAdvWorks
       FROM MyAdvWorks_1
       WITH NORECOVERY,
          MOVE 'MyAdvWorks' TO
    'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.mdf',
          MOVE 'MyAdvWorksLog1'
    TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewAdvWorks.ldf'
    RESTORE LOG MyAdvWorks
       FROM MyAdvWorksLog1
       WITH RECOVERY

    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Friday, October 6, 2006 3:12 PM
  • It took me an hour to find out that. When you backup database on one machine and then restore it on another it could happen that *******.mdf files are situated in different folders (e.g. on another drive). Then you need to change them to appropriate directories on "Options" tab in Microsoft SQL Server Management Studio.

    For example my college have created a backup for me. He have his SQL Server installed on C: drive, while I have E: system drive, so path was different. I had to change it from "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\*******.mdf" to "E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\*******.mdf" on options tab.

    Note that this could be not the only problem. It also happens that some permissions should be assigned to files. Usually I solve this problem by giving "Full control" to Everybody user coz this is my home computer, but it's not very good for security - so i wouldn't recommend to do so if this is public computer.
    Monday, November 26, 2007 9:52 AM
  • I have to say this worried me when I received the error message.  I was also a little confused by the earlier postings.  Personally I found the problem to be this:  I moved my MS Accounting .sdb file from my work computer and wanted to use it on my laptop.  The above error message came up on my laptop.  It transpired that it did not like importing my .sbd file from my flash disk, and instead I had to put my file into the relevant folder in MS SQL Server.  Just search for the sample .sbd files and put your own .sbd file in the same place.  Then everything seemed to work happily.

     

    Sunday, August 31, 2008 11:50 AM
  • Giving full permissions to Everyone user for the folder "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" worked for me
    Thursday, September 17, 2009 11:40 PM