Rollback data

Question

Rollback data

prashant-507002

SSC Eights!

Points: 922
More actions
December 9, 2011 at 6:27 am

#247496

I have run the update query in ssms without doing begin transaction.
Are there any way to rollback the updated data?

Viewing 15 posts - 1 through 15 (of 58 total)

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

TedT Right there with Babe Points: 796 More actions · Answer 1

Do you have a back up of the database?

_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

prashant-507002 (12/9/2011)
I have run the update query in ssms without doing begin transaction.
Are there any way to rollback the updated data?

If you already ran the statement there is no way except restore database. SSMS has default commit for transactions.

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

prashant-507002 (12/9/2011)
I have run the update query in ssms without doing begin transaction.
Are there any way to rollback the updated data?

Hope it wasn't a production database.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

prashant-507002 SSC Eights! Points: 922 More actions · Answer 4

Are there any session point like oracle database to which we can rollback?

TedT Right there with Babe Points: 796 More actions · Answer 5

To be honest, I'm not sure you'll get the answer you're looking for here (i.e. Do steps x, y and z and you will magically have your original data back).

As Cadavare said - I sincerely hope it isnt a production system!

This can only be handled using a combination of regular backups and properly considering what you're doing before you hit F5!

_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

prashant-507002 (12/9/2011)
Are there any session point like oracle database to which we can rollback?

They are there but you have to define them in transactions. It's called save points.

SAVE TRANSACTION (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms188378.aspx

prashant-507002 SSC Eights! Points: 922 More actions · Answer 7

Actually this action was done by a developer.

And i have find out the solution for. Server is live and i can not restore the backup.

TedT Right there with Babe Points: 796 More actions · Answer 8

Restore the backup to a seperate database, identify the rows which were updated incorrectly, and write a query to reverse the action.

_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 9

prashant-507002 (12/9/2011)
Actually this action was done by a developer.
And i have find out the solution for. Server is live and i can not restore the backup.

I am sorry but both of you are in serious trouble... 🙁

Mike John SSCertifiable Points: 7216 More actions · Answer 10

I think your only option is restore most recent backup to a different database, and then extract the lost data from the restored backup and re-insert it to the original database.

Or if you are in full recovery model investigate one of the commercial log readers to rescue what you deleted from the logs - if you are in simple recovery this is not an option.

Good luck

Mike

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 11

TedT (12/9/2011)
Restore the backup to a seperate database, identify the rows which were updated incorrectly, and write a query to reverse the action.

Restore means FULL + Diff (if any) + Logs (Point-in-Time before the update command)

prashant-507002 SSC Eights! Points: 922 More actions · Answer 12

prashant-507002

SSC Eights!

Points: 922

December 9, 2011 at 6:47 am

#1419295

Are there any tools available to rollback data?

Mike John SSCertifiable Points: 7216 More actions · Answer 13

Before you worry anout tools what recovery model is the db in?

Redgate do one - http://www.red-gate.com/products/dba/sql-log-rescue/

I have not used it personally but have heard good reports

Mike

TedT Right there with Babe Points: 796 More actions · Answer 14

There are various things you can do to monitor what is happening on your database, and give yourself enough information to roll back mistakes.

Try looking into:

Change Data Capture (CDC) http://msdn.microsoft.com/en-us/library/bb522489.aspx

Using triggers for auditing:http://www.codeproject.com/KB/aspnet/ImplementingAudit.aspx

_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain