Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163

    Unanswered: Get data from Transaction Log

    I wrong delete all contents of a table.
    I have only have the actual Transaction Log.
    With Log Navigator i found the lines that i wish/need to roll back.
    Can you please give me any ideas of how to recover/uncommitted/rollback data from the transaction log.

    Thanks,
    Paulo

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not that I've used it..but look at lumigent log explorer

    But don't you have a backup, that you can restore to another database?

    Apply the logs up to the point of the delete and copy the data out...

    The second idea is not possible because you have no backups...right?
    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
    Feb 2003
    Location
    @ home
    Posts
    163
    The full backup that i have it's one month old because the last ful backup file is corrupt.
    So i can't restore the full backup and then the Transaction Logs backup because i have 3 weeks difference.
    The trial version of Log Explorer from Lumigent only works for the pubs and northwind database, so i can't use it (can't trial it).

    Thanks,
    Paulo

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Don't you have all the trns logs since the last good backup?

    Don't know what else to tell ya...

    start typing?
    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.

  5. #5
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163
    No i don't have more transaction log backups.
    Do you have any version of Log Explorer?


    Paulo

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Read it again...they will mail you a product key for a limited time eval

    http://www.lumigent.com/downloads/
    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.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And start taking nightly backups....NOW!
    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.

  8. #8
    Join Date
    Jul 2002
    Posts
    58

    Weakness

    I've always felt that the worst weakness of SQL Server was that you can only have (essentially) one full backup of the your database. Of course you can copy the file, but if it's corrupted as it's being written, you're SOL and may not even know it.

    I wish they had designed the recovery so that you could start from any full backup for which you had all the interveining transaction log dumps. That way, if you had a sequence of dumps such as...

    Full 1
    Log 1
    Log 2
    Log 3
    Log 4
    Full 2
    Log 5
    Log 6
    Log 7

    ... and it turned out that backup Full 2 was lost/corrupted/bad tape, you could restore Full 1 and apply all 7 tx log dumps and be fine. But of course they didn't. Or, alternatively, it would be nice to have an option to suppress the log flush at the end of a full backup. That way you could do this.......


    Full 1 (no log flush)
    Full 2 (with log flush)
    Log 1
    Log 2
    Log 3

    and the DB could be restored to the end of Log 3 with either of the two full backups. Actually, I'll bet they could do that one without too much work.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Steve: I just tried an experiment with the northwind database (gotta love those sample DBs, eh?). I was able to do a full backup of a DB, a transaction log bkup, another full, then two more transaction logs after that. Naturally, I threw in some dummy data to a couple of tables to make sure the whole thing works. I was able to restore the database with only the first full and the transaction logs. Usually the tools will make restoring from a previous full backup difficult. I think Enterprise manager suffers from that (as well as a host of other maladies), and I know HP's Omniback hides the previous backups pretty well.

    In EM if you have two full backups, you have to manually select the device that has the prior backup, then you can restore away from there.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's what I thought....

    But he still doesn't have the interval back to the last non corrupt dump...

    pity

    dump every night with tranny dumps every 15 minutes...

    delete files older than 4 weeks........

    what's not to like about HUGE hard drives...hey you can ever create a sproc to archive older files for you....
    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.

  11. #11
    Join Date
    Jul 2002
    Posts
    58
    Originally posted by MCrowley
    Steve: I just tried an experiment with the northwind database (gotta love those sample DBs, eh?). I was able to do a full backup of a DB, a transaction log bkup, another full, then two more transaction logs after that. Naturally, I threw in some dummy data to a couple of tables to make sure the whole thing works. I was able to restore the database with only the first full and the transaction logs. Usually the tools will make restoring from a previous full backup difficult. I think Enterprise manager suffers from that (as well as a host of other maladies), and I know HP's Omniback hides the previous backups pretty well.

    In EM if you have two full backups, you have to manually select the device that has the prior backup, then you can restore away from there.
    Really? I'm gonna have to try that myself. that ability is certainly not documented in the BOL, or anywhere else I've seen.

    If PAL19 has all his transaction log from the three week old backup he does have, this could be a solution.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'd say he doesn't have them

    So i can't restore the full backup and then the Transaction Logs backup because i have 3 weeks difference.
    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.

  13. #13
    Join Date
    Feb 2002
    Posts
    2,232
    Pal19 - What transaction logs do you have - you mention that you have the current log - but how far back does that log go ?

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can always rename the database to Northwind and use the eval version against it.

  15. #15
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    You can always rename the database to Northwind and use the eval version against it.
    That is definitely a clever solution .
    Get yourself a copy of the The Holy Book

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

Posting Permissions

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