Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2007
    Posts
    13

    Unanswered: DB restore problem

    A SQL 7 database where I work that is used for an app called Clarify lost the transaction log and went into suspect mode. They only had a 15 day old backup , so they restored the backup and put the failed DB in emergeny mode. They then did a export of all objects and data into the good restored copy. They selected drop all tables first and selected every option except sql logins which were already there.

    The export appeared to work, but the app no longer functions correctly. It throws an error about invaild objects. The only thing I noticed different in the DB is all table dependencies are missing, but the good restored copy has them before data is imported. The sysdepends table is there and it matches the good copy exactly so I'm not sure why sql no longer sees the dependencies.

    Any ideas on what's wrong here?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What do you mean by Lost the transaction log?

    And you only do backups every 15 days?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Was the database renamed to it's original name? Also, do the error messages say anything about what objects are invalid? you may be able to run Profiler to get more information about what the application is attempting to do.

  4. #4
    Join Date
    Sep 2007
    Posts
    13
    Quote Originally Posted by Brett Kaiser
    What do you mean by Lost the transaction log?

    And you only do backups every 15 days?
    It's not my DB. I back mine up daily. I was told it was "lost". I'm just helping out on this one.

  5. #5
    Join Date
    Sep 2007
    Posts
    13
    Quote Originally Posted by MCrowley
    Was the database renamed to it's original name? Also, do the error messages say anything about what objects are invalid? you may be able to run Profiler to get more information about what the application is attempting to do.
    It's the same name and the profiler returned no errors. It says nothing about why it's invalid.

    I told them to try to delete all data from the user tables after restoring from a good backup, then to import in the data using append without dropping the tables first. I don't know if it will work but it's worth a try.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    It may be good to take this one step at a time. Try this. Restore the backup, then point the application to it to make sure that the 15 day old backup works as advertised. If not, then there may be deeper problems.

    Oh, and how did the log get "lost"? Was it deleted or something? If the database is back in Emergency mode, you should probably try to generate a script of all the tables, procedures, objects, etc. Divide this script into tables, then triggers and foreign key constraints. Create a brand new database, create the empty tables, transfer the data from the old database to the brand new one, then run the trigger and foreign key constraint script.

  7. #7
    Join Date
    Sep 2007
    Posts
    13
    The restored backup works fine. They never told me how the tlog was lost. I'll tell them about scripting everything that might work better than the import wizard they have been using.

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by wvdb
    The restored backup works fine. They never told me how the tlog was lost. I'll tell them about scripting everything that might work better than the import wizard they have been using.
    Wizards suck, hate 'em, everything I script out. I always use a script to backup and restore, never the wizard.

Posting Permissions

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