Post reply

oops.... i deleted a table

  • October 20, 2005 at 4:01 am

    #188130

    hi

    im new to the query analyzer (using sql server 2000)

    ive noticed that its very easy to delete a table.

    (by right clicking above the table i want to delete and pressing delete)

    but what if a second after i want to undo this command???

    should i do a full restore ?? or is there a simple way to "rollback"

     

     

  • October 20, 2005 at 4:33 am

    #598607

    If you put your database in FULL recovery mode (rather than SIMPLE recovery mode), you can take a full backup at some time before the table is deleted.  Then, if you do delete a table, you should

    a) Take a transaction log backup - also known as an incremental backup

    b) Restore your full backup from the earlier point in time

    c) Restore any other incremental backups up until the one you took after the mistake - restore these (if you have any) with the NORECOVERY option

    d) Restore the final transaction log (the one you took after the mistake) and use the STOPAT option to have the restore restore all operations up until the specified point in time.  You would specify that point in time to be shortly before the mistake occurred.

    e) Be more careful in future hehehe

    Note - this won't work if you are in SIMPLE recovery mode as you cannot take transaction log backups.  It is also too late to switch from SIMPLE -> FULL after the accident has occurred.

    Hope that puts your mind at ease

  • October 20, 2005 at 4:54 am

    #598612

    i dont get y microsoft makes it so easy to delete tables

    any how

    when i do a restore - cant i just restore the final transaction log file ??

    ( probably NO but worth asking  )

    and do i have to shut down the server in order to do a restore ??

    ( probably YES but worth asking &nbsp

    i know from oracle exp. that a restore can be done without shuting down the server

  • October 20, 2005 at 7:26 am

    #598695

    You don't need to "shut down" the server, although the DB is inaccessible during the restore for obvious reasons.  SQL 2005 has some improvements in that area I think.

    To restore just the latest transaction log would be more like doing differential backups - which transaction log backups are not...  You would need to restore all xaction logs since the last full backup (like any sort of incremental backup)...

    And hey, better to ask and be answered than to not ask and find out you could've saved a LOT of time if you were right

  • October 20, 2005 at 8:29 am

    #598738

    thx

  • October 21, 2005 at 9:50 am

    #599179

    Best practices

     

    BEGIN TRANSACTION

    COMMIT TRANSACTION

    ROLLBACK TRANSACTION

  • October 24, 2005 at 7:40 pm

    #599704

    "i dont get y microsoft makes it so easy to delete tables"

    Hey if you choose to delete a table, who's microsoft to ask you twice!?

    Just be careful when you do things, and like everyone else has been suggesting, make backups, and work in manual transactions whenever you can, just to be sure.

    Julian Kuiters
    juliankuiters.id.au

  • October 26, 2005 at 9:06 am

    #600228

    You could restore to a temporary database and then copy the table from there to the current database.

    Steve

  • October 26, 2005 at 4:25 pm

    #600420

    I would say "You SHOULD restore to a temporary database and then copy the table from there to the current database."

    Then you're not impacting users or any other data in the database.  The best answer depends on if this is a production or test database, and if the deleted table would have changed since the last backup (requiring transactions).

  • October 27, 2005 at 12:09 am

    #600469

    the DB is almost static  - i add a record about once a week

    so theres no chance to loose data

     

  • October 27, 2005 at 9:30 am

    #600618

    With great power comes great responsibility.

     

Viewing 11 posts - 1 through 10 (of 10 total)

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