restoring deleted rows in a table
-
A dts script got executed unexpectedly and deleted some rows in a table.
Please tell me how to recover the rows .
i put roll back . but it is asking for begin transaction command..
-
Is the database in full recovery mode?
Have you got database and transaction log backups?
If so you could restore them to antoher database and then extract the rows out that you have accidently deleted!
-
Good advice from Chris. You couldn't roll back the transaction because it had already been commited in the package. You can only roll back a transaction that you explicitly started.
Greg
-
Good advice from Chris.
I agree. Not much you can do in this situation. Anyway you look at it there is a potential risk of data loss. I would restore the back as a new database and figure out exactly what the package did and try to restore just those records.
I hope you are in full recovery mode with frequent tran log backups. :pinch:
-
Yes i have some back up logs.
like those mentioned below.
pubs_db_200803041737.BAK
XLANG_db_200803041739.BAK
Northwind_db_200803041737.BAK
msdb_db_200803041736.BAK
model_db_200803041736.BAK
master_db_200803041736.BAK
InterchangeSQ_db_200803041736.BAK
InterchangeDTA_db_200803041736.BAK
InterchangeBTM_db_200803041736.BAK
I tried to retrieve the log and revert the transaction using RED gate's rescue log tool .
But i couldn;t get any recent transaction in all these files. even though these are all taken yesterday evening.I dont know why. any how i am trying some other means also.
-
I tried to retrieve the log and revert the transaction using RED gate's rescue log tool .
I believe Log Resuce only works for 2000, unless Redgate has a new release.
-
If you can't use a log reader tool, then follow Chris's advice and restore the database with a new name. Then you can select the rows you want and re-insert them into the original database.
Greg
-
Those look like Full database backups. Log backups are very different. First check to see if you are running in Full mode, then check to see if you are running different types of backups, such as log or diff. If running in full mode, and not running log backups, then the first step would be to perform a log backup immediately (hopefully you are not shrinking the log file). once that is done, you can then follow the suggestion submitted by Chris. With a log backup, you can do point in time recovery so you can actually recover up to the minute prior to the DTS Package.
Don't forget, once you recover to that point, any transactions after the DTS package will also have to be reapplied, hence, why you don't want to overwrite the existing database.
If not running in Full mode, you can try to use a Third Party tool to read the logs, but no guarantee that the data would be in the logs.
If you find that you do have log backups, or that you are running in Full mode, and you still need help, just post back and there are plenty of great SQL Server types here that could walk you thru the entire process.
Good Luck
MD
Marvin Dillard
Senior Consultant
Claraview Inc -
hello,top buddy.
there are 2 ways:
1:use tools.
http://www.yiii.net/app/servlet/net.yiii.club.DownloadServlet?Information_Id=I00023471
2.since now,you can create a new table
sql: select * into newtable from oldtable where 1 <> 1
then create a trigger on oldtable,every operation on the table you can insert the data to the newtable
-
I think, you should follow method recommended by Chris.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
-
yes, I followed that.
I have deleted only the records which are inserted between 2001- 2005 .
I was able to get an old back up and I got the data from that . It was a log table so no updation happened.
So I restored from the back up . now I am safe.
Thanks a lot for all your support.I came to know a lot. As I am working in sql performance tuning , it would be very helpful.
thanks once again.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply