June 15, 2005 at 3:10 am
Dear All,
by mistake i have updated all the records with the same date in one particular table. now i want to revert/rollback the update command.
Please help
June 15, 2005 at 4:11 am
My understanding is you can only Rollback a transaction if the command was issued inside a transaction.
For example:
begin transaction
update whatever
rollback transcation
will allow you to roll back.
just an update command meens its been commited and you will probably need to get it back from a backup.
Someone else might have a better idea.
Jon
June 15, 2005 at 4:35 am
Assuming you backup regularly
to reinstate the old dates :
Do a RESTORE of database1 with its latest backup to a new database2
then update database1 setting database1.datecolumn = database2.datecolumn where database1.IDcolumn = database2.IDcolumn.
NOTE dates inserted/updated since the latest backup will not be recovered
June 15, 2005 at 5:01 am
i do not have the backup and begin transaction command was not issued before update. I have heard that updates can be rolled back through transaction logs.
June 15, 2005 at 5:07 am
if you have full recovery mode on the transaction logs you can, but not sure exactly how to do this. It should say in the SQL Server help.
June 15, 2005 at 5:10 am
You need to restore the transaction log against a backup of the database. I'm not aware of any way of undoing the changes. I take it your running the database in the Full Recovery model?
June 15, 2005 at 5:49 pm
To put together what others have suggested:
If the database is using Full recovery model and you have a full database backup from a point prior to the time when the UPDATE statement ran, you can backup up the log now. Then, restore the full backup to a new database with NORECOVERY, restore the log backup with RECOVERY, and update the table as Brian described.
If you have no backups, I'm afraid you're out of luck.
Greg
Greg
June 16, 2005 at 3:32 pm
shakti,
SQL Server do not provide any tool or utility to recover data updated or deleted accidently. for this reason companies hire DBA to take bakups so that in case of some mistake, data could be recovered. however no need to be so disappointed. there are some sites who claim that by using them you can restore data even if you do not have a backup of it. Look for it on Google or yahoo. I will add the link if i find one.
Xeesh.
June 16, 2005 at 4:18 pm
Check out Lumigent Log Explorer. You may be able to get an eval version and fix the problem.
There are a couple of other products that do the same thing, but I can't remember their names.
June 16, 2005 at 9:52 pm
Log PI is another tool. I believe they also provide a free evaluation. We ended up buying Lumigent Log Explorer where I work. It's a nice tool.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply