locked
Restoring a DB thru the log file. RRS feed

  • Question

  • Thru my neglect last night (I was very tired to the point of exhaustion and still greedily continue working) I screwed one of my DBs up thru mishandling it via C# code. There was a bug that apparently caused the DATA file disappear from the partition. The log file remains in a different partition. There was no statemetns that were supposed to delete the file. I anyone has any insight as to how that could happen I would appreciate it.

    It is not a major disaster for me. I can restore this DB by other means in about 10-20 minutes. What I want to know if it is possible to do thru Smo?

    Again, the task is: there is a supposedly intact LOG file for a DB but the mdf file is absent. There are some other important pieces of inforamtion.

    The DB has about a dozen tables. I can actually work with all of them but one. That table lost all rows. Otherwise I can query other tables, insert and delete rows manually in SSMS or thru code, I can connect to DB, you name it. There is no mdf file. It is so bizarre.

    I should have made a snapshot last night but I did not. This whole server activity is actually setting it up for production because I cenverted my VFP Dbs to Sql and now doing a tune up.

    Thanks.
    AlexB
    Saturday, May 16, 2009 4:57 PM

Answers

  • It looks like you still have MDF file otherwise application should not be able to see tables. You said , one table lost all rows, it means that there is some delete statement on this table.  So the first thing is go to Management studio check where the db file is and take a backup. If the database is in full recovery model, then take TL backup too and restore the database using POINT INTIME RESTORE option(provided you have full backup and other TL backup in place). You can restore it as a temporary database and only push the data for the table for which you have data loss.

    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Sunday, May 17, 2009 2:45 AM

All replies

  • Hi Alex,

    This seems like a really bizarre case with mdf missing and yet you are able to view all tables but one.
    Coming to question of recovering the database using ldf file , I don't think it is possible you need backup files to recover your database.
    There is a restore class in SMO for this but it only provides basic restore facilities only.

    May be you can post this question in engine forum how can database is  working even when there is no mdf.

    Regards,
    Alok Parmesh
    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Saturday, May 16, 2009 10:07 PM
    Answerer
  • It looks like you still have MDF file otherwise application should not be able to see tables. You said , one table lost all rows, it means that there is some delete statement on this table.  So the first thing is go to Management studio check where the db file is and take a backup. If the database is in full recovery model, then take TL backup too and restore the database using POINT INTIME RESTORE option(provided you have full backup and other TL backup in place). You can restore it as a temporary database and only push the data for the table for which you have data loss.

    Madhu
    MCITP, MCTS, MCDBA,MCP-- Blog : http://experiencing-sql-server-2008.blogspot.com/
    Sunday, May 17, 2009 2:45 AM
  • Thanks for your advice. I haven't done the backups yet. I am setting up my DBs and doing all sorts of experimentation. I can potentially restore the table and the entire DB since I created it recnetly and transferred all data from a parallel VFP DB (xBase). I was just wondering what went wrong, if people seen such weird things, how in the world coudl I screw it up so badly, etc. It has educational value for me. I will start doing backups in a few days full steam but now they are not set up yet.
    AlexB
    Monday, May 18, 2009 12:15 AM