Post reply

undo the query wrongly executed

  • October 11, 2010 at 12:19 am

    #241513

    I have executed the wrong query in sql server management studio express i,e executed the delete query instead of select query so i lost the data stored in the table. How can i undo the query or how can i get back the data into the table. Please help me.

    thanks

  • October 11, 2010 at 1:29 am

    #1234806

    Restore data from a good backup set. There's no "undo" command that you can issue.

    If your database is in full recovery model, you can restore to a point in time just before the time you ran the delete query.

    -- Gianluca Sartori

  • October 11, 2010 at 12:38 pm

    #1235180

    ROLLBACK is an 'undo', but you had to have used the BEGIN TRAN before you ran the DELETE statement.

    For future reference, backup the data and make use of transactions when running risky code.

    If I have some manual fix type process to be run, I do something like this:

    -- Get a count of records to be deleted

    select count(*) from TableA

    where status = 'ToBeDeleted'

    -- Save the records about to be deleted

    select * into TableA_Deleted

    from TableA

    where status = 'ToBeDeleted'

    -- Run the delete. Rollback if counts do not match

    begin tran

    delete from TableA

    where status = 'ToBeDeleted'

    rollback

    commit

    Depending on the situation & timing, you may have more records that qualify for deletion by the time you run the delete. As an extra precaution, your delete statement can join to the TableA_Deleted table, so that you only delete those that have been archived.

    Also, if I accidentally press F5, having the rollback before the commit will ensure the delete gets "undone".

Viewing 3 posts - 1 through 3 (of 3 total)

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