Post reply

Single mdf file attach in sql 2005

  • February 4, 2010 at 6:28 am

    #144756

    Good day all.

    I lost my drive due to some hardware fault.

    My log files were on the faulty drive.

    i do not have access to a backup now.

    i have the mdf file though,

    The ldf files are gone.

    Can i attach in sql 2005 to a single mdf file

    The DB was not detached clean,

    i tried the

    exec sp_attach_single_file_db 'XXX','H:\AAA\xxx.mdf'

    Msg 5173, Level 16, State 1, Line 1

    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.

    Log file XXX.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.

    Msg 5173, Level 16, State 1, Line 1

    One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.

    File activation failure. The physical file name "xxx.ldf" may be incorrect.

    Msg 1813, Level 16, State 2, Line 1

    Could not open new database 'XXX'. CREATE DATABASE is aborted.

    i also cant rebuild the log as that feature is not in sql 2005,

    Please if someone had this problme , how did you get around, i need to attach my mdf , without ldf .

  • February 4, 2010 at 6:43 am

    #1113706

    Hello,

    Do you have any chance of getting backed up information that is going to be the best bet but it sounds like that is a long shot. Have you tried to use the sp_attach_single_file_db procedure...the syntax is below.. hope it helps

    EXEC sp_attach_single_file_db @dbname = 'AdventureWorks',

    @physname = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\AdventureWorks_Data.mdf';

    This might be able to bring you some resoultion

    -D-

    DHeath

  • February 4, 2010 at 6:47 am

    #1113708

    Yes i have tried that.

    My main posting has the results,

    Thank you for your reply anyway.

  • February 4, 2010 at 6:49 am

    #1113711

    If the databases still shows in your databases list, the first thing to do is drop it. To do this, take it offline and then detach.

    After that attach the mdf through ssms but remove the entry for the log. SQL should then creat a new log for you. Code below:

    USE [master]

    GO

    CREATE DATABASE [DATABASE] ON

    ( FILENAME = N'PATHE TO MDF FILE')

    FOR ATTACH

    GO

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • February 4, 2010 at 7:09 am

    #1113732

    This is the reply i get

    USE [master]

    GO

    CREATE DATABASE DBname ON

    ( FILENAME = N'H:\XXX.mdf')

    FOR ATTACH

    Result

    File activation failure. The physical file name "f:\XXX.ldf" may be incorrect.

    File activation failure. The physical file name "f:\XXX.ldf" may be incorrect.

    The log cannot be rebuilt because the database was not cleanly shut down

    The f:\ drive refers to the drive that was lost.

    When trying to attach the mdf , it reads the location of the f drive that is no longer there.

  • February 4, 2010 at 8:03 am

    #1113772

    Was the database still shwon in the databases list?

    Have you tried attaching via th ssms gui (removing the entry for the log)?

    Did you google the output?

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • February 4, 2010 at 8:08 am

    #1113779

    imtiazm (2/4/2010)

    This is the reply i get

    ...

    File activation failure. The physical file name "f:\XXX.ldf" may be incorrect.

    File activation failure. The physical file name "f:\XXX.ldf" may be incorrect.

    The log cannot be rebuilt because the database was not cleanly shut down

    ....

    ouch, it looks like you cannot attach the mdf because of that issue....the database was not cleanly shut down. I think that's why you are having the errors.

    I'm sure you already looked at your backups, how recent are they?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 4, 2010 at 8:17 am

    #1113784

    This seems to address your issue (i found it with a quick google).

    Try what it suggests (make a copy of you mdf first) and get back to us.

    sorry forgot link: http://social.msdn.microsoft.com/Forums/en/sqldisasterrecovery/thread/189be01f-23de-48b0-96cc-8f1292c13c54

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • February 4, 2010 at 8:22 am

    #1113788

    Have a look at this blog post. Step by step for getting a DB back when the log's deleted. The process for when it's detached starts just past half way through.

    http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply