Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Princess Anne, MD
    Posts
    3

    Exclamation Unanswered: need help rebuilding a DB from .sql files!

    I've got this problem, I was handed an MSDE 7 DB that could no longer be connected to (corruption possibly). There were no backups of any kind. I found a utility that "recovered" the data, but now I've got 87 data00xx.sql files, one commit.bat file, and a schema.sql file.

    I don't know what to do with these files, and my goal is to get back to an MDF that I can connect to. I tried opening the schema file in Query Analyzer and executing it, but there were many, many errors.

    Any suggestions would be greatly appreciated.

    P.S. - I've got nothing to lose so I'm willing to attempt anything to save this DB.

    Thanks

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    If you run the schema.sql repetidly, do the number of errors decrease?

    What's in the commit.bat file?

    Do the dataXXX.sql files just contain a bunch of insert statments?

    What was the name of the tool you used?
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Aug 2003
    Location
    Princess Anne, MD
    Posts
    3
    I ran the schema.sql again, and there's so many lines in the results that I can't tell if the amount is changing, they look consistent though. The tool I used was called MSSQLRecovery - downloaded it yesterday.

    Also - yes, the Dataxxx.sql files are just tons of INSERT statements.

    The majority of the messages I get when I run the schema are:

    Server: Msg 1913, Level 16, State 1, Line 2
    There is already an index on table 'MRIRPTP' named 'PROPERTIES'.

    yeah... lots of these

    Thanks for your help!

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Okay, what is in the commit.bat file?

    Sounds like your Schema.sql has the code to create all the objects but probably no code to test if an object exists prior to attempting a create. If this is the case you can run the schema.sql a few times to build all objects, dependant objects and foreign keys.

    When all your errors are regarding failure to create an object due to the object existsing, you can move on.

    Next, you will need to figure out which tables are parent tables and load their data first, then the next level parents, and so on. Or you can disable all the foreign keys, run all the inserts, restore the foreign keys and hope for the best. If the DB went suspect you could be loading corrupt data!

    shoot the b*****d the forgot to do backups!
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Aug 2003
    Location
    Princess Anne, MD
    Posts
    3

    Red face

    sorry, forgot the commit.bat file:

    @echo off

    if x"%1" == x"" Goto Usage
    if x"%2" == x"" Goto Usage
    if x"%3" == x"" Goto Usage
    if x"%4" == x"" Goto Usage

    isql -S %1 -d %2 -U %3 -P %4 -E -i schema.sql
    isql -S %1 -d %2 -U %3 -P %4 -E -i data0001.sql

    This continues for the next 87 data files and then exits.

    I'm running the batch file right now, and it appears to be populating something, with occasional messages that say "Colunm name "xxx" does not exist in the target table.

    I'll try your advise - and with my luck, I'll be recovering corrupted data, and all of this will just be a very frustrating excercise.

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I quess the MSSQLRecovery author thought you would only have the cmd prompt to work with.

    If the above mentioned errors are minimal you might just save someone a bunch of time. I suspect you can piece 99% of the data back together given enough time.

    Yup could be alot of work for little return.

    Good luck!
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •