locked
Attaching an MDF File RRS feed

Answers

  • Hello Dave,

    The Best option for sharing the database is to share the backup file as suggested by Grunt.

     

    Incase the Logfile is lost and the database is shutdown properly, the options you have are :

    1) sp_attach_single_file_db: to attach from a .mdf file of a database with single datafile. Remember that this cant be used for Databases with multiple datafiles. Also this option is deprecated and might not be available in future versions. 

    2) create database  for ATTACH_REBUILD_LOG : Check out BOL for a better idea on using this option which might be your best choice for a SQL 2005 SP3. 

    3) Undocumented DBCC REBUILD_LOG : This is an undocumented DBCC command which you can look for on the web to make use of it.

    Sasi.


    Sasi | http://honeyvirus.wordpress.com/
    • Proposed as answer by FCS128 Monday, July 26, 2010 1:05 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 10:14 AM
    Saturday, July 24, 2010 5:43 PM
  • Error:

    Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\data\ProficySPC_Main_log.LDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft SQL Server, Error: 5133)

    Cause:

    Operating system error 3(The system cannot find the path specified.)

    The ldf file of the database "C:\Program Files\Microsoft SQL Server\MSSQL\data\ProficySPC_Main_log.LDF" is missing on this path.

    Resolution:

     Option 1:  Get the .ldf file and place it on the path above and try attaching it with .mdf & .ldf.

     Option 2:  Use sp_attach_single_file_db , attaches a database that has only one data file to the current server.

     Option 3:  Use CREATE DATABASE database_name FOR ATTACH_REBUILD_LOG.

    FOR ATTACH_REBUILD_LOG requires the following:

    • A clean shutdown of the database.
    • All data files (MDF and NDF) must be available.

    If a read/write database has a single log file that is currently unavailable, and if the database was shut down with no users or open transactions before the attach operation, FOR ATTACH automatically rebuilds the log file and updates the primary file.

    In contrast, for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database whose log is unavailable, you must provide the log files or files in the FOR ATTACH clause.

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Sunday, July 25, 2010 2:33 AM

All replies

  • It means you don't have the ldf file - the log file for the database.
    He needs to give you that or better yet why not send a backup of the
    database which you can restore?
     
     
    On 7/24/2010 11:43 AM, DaveLindstrom wrote:
    > I received an .mdf file from a freind of mine that I would like to add
    > to my SOL 2005 server. I'm using SQL Server 2005 Standard Edition as is
    > my friend. He has sent me a .mdf file. When I go to attach this through
    > management studio I get the following:
    >
    > TITLE: Microsoft SQL Server Management Studio
    > ------------------------------
    >
    > Attach database failed for Server 'home'. (Microsoft.SqlServer.Smo)
    >
    > For help, click:
    >
    > ------------------------------
    > ADDITIONAL INFORMATION:
    >
    > An exception occurred while executing a Transact-SQL statement or batch.
    > (Microsoft.SqlServer.ConnectionInfo)
    >
    > ------------------------------
    >
    > Directory lookup for the file "C:\Program Files\Microsoft SQL
    > Server\MSSQL\data\ProficySPC_Main_log.LDF" failed with the operating
    > system error 3(The system cannot find the path specified.). (Microsoft
    > SQL Server, Error: 5133)
    >
    > For help, click:
    >
    > ------------------------------
    > BUTTONS:
    >
    > OK
    > ------------------------------
    >
    > Can anyone explain to me what this message is telling me and how to
    > resolve it so that I may access the information in this database?
    >
    > Thank you
    >
     

    xxxx
    Saturday, July 24, 2010 4:12 PM
  • Hello Dave,

    The Best option for sharing the database is to share the backup file as suggested by Grunt.

     

    Incase the Logfile is lost and the database is shutdown properly, the options you have are :

    1) sp_attach_single_file_db: to attach from a .mdf file of a database with single datafile. Remember that this cant be used for Databases with multiple datafiles. Also this option is deprecated and might not be available in future versions. 

    2) create database  for ATTACH_REBUILD_LOG : Check out BOL for a better idea on using this option which might be your best choice for a SQL 2005 SP3. 

    3) Undocumented DBCC REBUILD_LOG : This is an undocumented DBCC command which you can look for on the web to make use of it.

    Sasi.


    Sasi | http://honeyvirus.wordpress.com/
    • Proposed as answer by FCS128 Monday, July 26, 2010 1:05 AM
    • Marked as answer by Tom Li - MSFT Sunday, August 1, 2010 10:14 AM
    Saturday, July 24, 2010 5:43 PM
  • Error:

    Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\data\ProficySPC_Main_log.LDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft SQL Server, Error: 5133)

    Cause:

    Operating system error 3(The system cannot find the path specified.)

    The ldf file of the database "C:\Program Files\Microsoft SQL Server\MSSQL\data\ProficySPC_Main_log.LDF" is missing on this path.

    Resolution:

     Option 1:  Get the .ldf file and place it on the path above and try attaching it with .mdf & .ldf.

     Option 2:  Use sp_attach_single_file_db , attaches a database that has only one data file to the current server.

     Option 3:  Use CREATE DATABASE database_name FOR ATTACH_REBUILD_LOG.

    FOR ATTACH_REBUILD_LOG requires the following:

    • A clean shutdown of the database.
    • All data files (MDF and NDF) must be available.

    If a read/write database has a single log file that is currently unavailable, and if the database was shut down with no users or open transactions before the attach operation, FOR ATTACH automatically rebuilds the log file and updates the primary file.

    In contrast, for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. Therefore, when you attach a read-only database whose log is unavailable, you must provide the log files or files in the FOR ATTACH clause.

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Sunday, July 25, 2010 2:33 AM
  • One more option to what Siva had added, may be one of the option he has meant to be this i.e., use management studio our of box UI to attach the db. When you attach, it asks for a log file. Delete that log file entry from the send data grid using remove option. It will ask if you want to create a fresh log file. SELECT the yes option and your database gets restored with approx 512 MB size of a log file.


    Phani Note: Please mark the post as answered if it answers your question.
    • Proposed as answer by FCS128 Monday, July 26, 2010 1:06 AM
    Sunday, July 25, 2010 3:38 AM
  • Hello Dave,

    Check the following article Attach a SQL Server database with a missing transaction log which explain how to attach a database with a missing transaction log.

    Thanks
    Ashish Kumar Mehta

    Please click the Mark as Answer button if a post solves your problem!

    Monday, July 26, 2010 10:42 AM
  • Thanks a ton, Ashish. The article has the solution. Need to remove the log file while attaching the DB. It Works!!

    Link I got for the article search is: http://www.mssqltips.com/tip.asp?tip=1894

    Sunday, June 26, 2011 10:55 PM