Post reply

How to revert back the change

  • July 1, 2009 at 9:13 pm

    #134019

    Hello SQL Experts

    One of my friends had to update the changeid of one user(userid =1000) to 100. But while updating he forgot to include

    "begin tran .. commit\rollback" and missed to include the "where ..." statement and it updated aginst all the users with changeid as 100.

    Is there any way to rolback this change?

    begin tranupdate tableset changeid = 100where userid = 1000commit

    Please assist.

    Regards

    Sourav

    Thanks.

  • July 1, 2009 at 10:51 pm

    #1018884

    Hello Sourav,

    Does your friend not have a backup that he can recover from? Alternatively, does a DB Snapshot exist?

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • July 1, 2009 at 10:51 pm

    #1018885

    Sourav Mukherjee (7/1/2009)

    Hello SQL Experts

    One of my friends had to update the changeid of one user(userid =1000) to 100. But while updating he forgot to include

    "begin tran .. commit\rollback" and missed to include the "where ..." statement and it updated aginst all the users with changeid as 100.

    Is there any way to rolback this change?

    begin tranupdate tableset changeid = 100where userid = 1000commit

    Please assist.

    Regards

    Sourav

    You cannot rollback directly however there are workarounds

    1. restore a clean backup of the database with a different name and import the affected table to your database

    OR

    2. if you have full and tran log backups, restore the database to point in time using STOPAT clause just before that statement was fired.

    Refer BOL for details on restore.


    Pradeep Singh

  • July 1, 2009 at 10:53 pm

    #1018886

    -

    www.sql.lu
    SQL Server Luxembourg User Group

  • July 1, 2009 at 10:54 pm

    #1018887

    -

    www.sql.lu
    SQL Server Luxembourg User Group

  • July 1, 2009 at 11:36 pm

    #1018900

    Thanks Pradeep for your response.

    Option:2 is fine, but the drawback is: If my T-log backups are taken in 15 mins (generally stad way of taking it) interval then going for this option will not sustain the other transaction done on other tables.

    What do you say on this?

    Option:1

    "restore a clean backup of the database with a different name and import the affected table to your database"

    Can you please elaborate on the point:

    "import the affected table to your database"

    Many thnaks for checking and replying to me. 🙂

    Best Regards

    Sourav

    Hyderabad

    Thanks.

  • July 1, 2009 at 11:41 pm

    #1018902

    Sourav Mukherjee (7/1/2009)

    Thanks Pradeep for your response.

    Option:2 is fine, but the drawback is: If my T-log backups are taken in 15 mins (generally stad way of taking it) interval then going for this option will not sustain the other transaction done on other tables.

    What do you say on this?

    Yep, in such a scenario, you'll lose the changes made to other table after that incident.

    Option:1

    "restore a clean backup of the database with a different name and import the affected table to your database"

    Can you please elaborate on the point:

    "import the affected table to your database"

    Many thnaks for checking and replying to me. 🙂

    Best Regards

    Sourav

    Hyderabad

    In this case you can restore your database from recent backup to a new database(say db2). assuming ur original database's name is db1, you need to delete that table from db1 and import this table from db2. I suggest u proceed with this approach.


    Pradeep Singh

  • July 1, 2009 at 11:52 pm

    #1018905

    That's great. Thank you.

    Regards

    Sourav

    Thanks.

  • July 1, 2009 at 11:54 pm

    #1018908

    thanks for the feedback 🙂


    Pradeep Singh

  • July 2, 2009 at 12:38 am

    #1018928

    Hello Sourav,

    assuming ur original database's name is db1, you need to delete that table from db1 and import this table from db2

    I would not recommend deleting and recreating the table in this kind of situation. It is often safer to use a tool like RedGate’s SQL Data Compare to synchronise the data in your “Live” table with the one restored from backup.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • July 2, 2009 at 12:43 am

    #1018931

    John Marsh (7/2/2009)

    Hello Sourav,

    assuming ur original database's name is db1, you need to delete that table from db1 and import this table from db2

    I would not recommend deleting and recreating the table in this kind of situation. It is often safer to use a tool like RedGate’s SQL Data Compare to synchronise the data in your “Live” table with the one restored from backup.

    Regards,

    John Marsh

    yes, thats a better approach. Thanks for pointing that out John.


    Pradeep Singh

  • July 2, 2009 at 12:53 am

    #1018939

    Thank you John. Actually I didn't use any 3rd party application so far , so not sure whether this could be good. But per my knowledge many backups\restore tools are there (redgate,light speed, and so on) which helps is improving the performance.

    I will keep this suggestion also in mind and try to implement if I face such situation in my production environment

    Many thanks for your response.

    Reards

    Sourav

    Thanks.

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

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