August 7, 2017 at 4:59 am
Hi All,
Is there a way to recover previous values after committing an update on a table , apart from recovery from backup ? There is no auditing on the tables too. Any inputs ? Thank you in advance.
Arshad
August 7, 2017 at 5:16 am
Arsh - Monday, August 7, 2017 4:59 AMHi All,
Is there a way to recover previous values after committing an update on a table , apart from recovery from backup ? There is no auditing on the tables too. Any inputs ? Thank you in advance.Arshad
No, you will have to restore a previous backup.
Thanks
August 7, 2017 at 5:58 am
Arsh - Monday, August 7, 2017 4:59 AMHi All,
Is there a way to recover previous values after committing an update on a table , apart from recovery from backup ? There is no auditing on the tables too. Any inputs ? Thank you in advance.Arshad
The entire idea behind the COMMIT is to push the data to disk as part of a transaction. You can't then undo it. There is a way to read from the transaction log in order to figure out what was done and then you can issue a DELETE statement (or whatever) to clean it up. However, this isn't easy or quick. Read up on it here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2017 at 5:58 am
If the database recovery mode is Full then do a log backup, restore last good full backup and restore the log to a point before the committed updates.
Far away is close at hand in the images of elsewhere.
Anon.
August 7, 2017 at 6:20 am
David Burrows - Monday, August 7, 2017 5:58 AMIf the database recovery mode is Full then do a log backup, restore last good full backup and restore the log to a point before the committed updates.
But, with the knowledge that any other data changes on other tables will also be rolled back to the point of the restore.
If going this route, a good approach would be to restore to a new database, then compare between the old and new to identify just the changes you want to remove from the database.
Undoing committed data changes isn't easy or straight forward.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2017 at 6:25 am
Grant Fritchey - Monday, August 7, 2017 6:20 AMDavid Burrows - Monday, August 7, 2017 5:58 AMIf the database recovery mode is Full then do a log backup, restore last good full backup and restore the log to a point before the committed updates.But, with the knowledge that any other data changes on other tables will also be rolled back to the point of the restore.
If going this route, a good approach would be to restore to a new database, then compare between the old and new to identify just the changes you want to remove from the database.
Undoing committed data changes isn't easy or straight forward.
Thanks Grant, I should have stated that since it is what I have done in the past :blush:
Far away is close at hand in the images of elsewhere.
Anon.
August 7, 2017 at 6:59 am
David Burrows - Monday, August 7, 2017 6:25 AMThanks Grant, I should have stated that since it is what I have done in the past :blush:
Oh yeah, I figured you did. I just wanted to add the detail.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2017 at 11:52 am
Hi,
Thank you everybody for the valuable advices. Would like to know if it's feasible to see if one can use the statistics data in this situation. Please put some light on that method that's not straight forward but workable. Thank u so much.
Arshad
August 7, 2017 at 12:58 pm
Arsh - Monday, August 7, 2017 11:52 AMHi,Thank you everybody for the valuable advices. Would like to know if it's feasible to see if one can use the statistics data in this situation. Please put some light on that method that's not straight forward but workable. Thank u so much. Arshad
You can simply query the data. There's an example at the link. If it did an INSERT, you can do a DELETE. If it did an UPDATE, you change what it did. Etc. There's nothing automatic about it. You have to find what was done and then do the reverse.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 9, 2017 at 6:47 am
Grant Fritchey - Monday, August 7, 2017 12:58 PMArsh - Monday, August 7, 2017 11:52 AMHi,Thank you everybody for the valuable advices. Would like to know if it's feasible to see if one can use the statistics data in this situation. Please put some light on that method that's not straight forward but workable. Thank u so much. ArshadYou can simply query the data. There's an example at the link. If it did an INSERT, you can do a DELETE. If it did an UPDATE, you change what it did. Etc. There's nothing automatic about it. You have to find what was done and then do the reverse.
Hi Grant,
Thanks. Is there a way or any tool for reading the transaction log contents , I mean the DML entries (I understand that the VLFs should be there to be read before being reused) ?
Arshad
August 9, 2017 at 6:59 am
Arsh - Wednesday, August 9, 2017 6:47 AMHi Grant,
Thanks. Is there a way or any tool for reading the transaction log contents , I mean the DML entries (I understand that the VLFs should be there to be read before being reused) ?Arshad
Redgate used to have such a tool, but it didn't sell enough copies to make it worth maintaining. There might be some other company that sells one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 9, 2017 at 8:34 am
Grant Fritchey - Wednesday, August 9, 2017 6:59 AMArsh - Wednesday, August 9, 2017 6:47 AMHi Grant,
Thanks. Is there a way or any tool for reading the transaction log contents , I mean the DML entries (I understand that the VLFs should be there to be read before being reused) ?Arshad
Redgate used to have such a tool, but it didn't sell enough copies to make it worth maintaining. There might be some other company that sells one.
Thank you everybody , thanks Grant for sharing knowledge.
Arshad
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply