September 25, 2008 at 6:11 am
Hi All,
I have an inventory database.
The problem is accidentally i have deleted one row from ItemStock table.
After that there are some sales bill done which are stored in Sales Table. The complete scenario is like this
At 10:30 I have deleted the row From ItemStock Table.
At 10:46 And 10:50 I have inserted Two rows in Sales Table.
Now I want to restore that deleted row To ItemStock Table and also i want to retain
the two rows of Sales Table.
How can i do this. I ve taken the full backup of database at 10:00 , If i restore i can get the row in
ItemStock Table but i will loose the rows of sales table.
Please help me i dont know much about backup and restore concepts.
September 25, 2008 at 6:47 am
One option you have is to restore a copy the database under a different name up the point just before you made the deletion. you can then copy the deleted row back into the main database table...thus keeping the trasnactions that occured after the deleteion in the main database.
Unfortunately native sql backup do not support row or table restores, if you try retsoring over the top of the existing database you will only be able to retore to the point in time prior to the deletion the deletion. All transactions after that point will be lost.
If you have a database snapshot which includes the deleted row you could copy the row from there.
Hope this helps
Gethyn Elliswww.gethynellis.com
September 25, 2008 at 6:56 am
Thanks for your help.
I have restored the database in another machine an the copied the row into the original table.
It worked.
But my worry is in case of multiple rows deleted in many tables then it will be difficult search and restore those rows. Wether the differential backups solve my problem?
Or is there any other methods to solve this problem?
September 25, 2008 at 7:16 am
Unfortunately not. Native sql backup does not support row or tables restores. You can only restore a database to a point in time.
Gethyn Elliswww.gethynellis.com
September 25, 2008 at 10:25 am
Prasad (9/25/2008)
Thanks for your help.I have restored the database in another machine an the copied the row into the original table.
It worked.
But my worry is in case of multiple rows deleted in many tables then it will be difficult search and restore those rows. Wether the differential backups solve my problem?
Or is there any other methods to solve this problem?
RedGate has a Data Compare tool, or SQL has a TableDiff I believe to compare tables and sync
It may be worth a try
September 25, 2008 at 10:58 am
They do have tools for that... so this maybe a possibility if you want to do row or tables restores, but you still need a full backup and the transaction logs to get a copy of the the db into a state before the deletions to get those products to perform as neccessary
Gethyn Elliswww.gethynellis.com
September 25, 2008 at 11:05 am
apexsql.com (apex log tool)
September 25, 2008 at 12:58 pm
Prasad -
The question you pose is one that usually comes up in evolving SQL Server environments. The answers you've received are solid answers to help you with fixing your problem.
But you made a comment about a what-if scenario - if more than one entry is deleted, how to identify all the deleted items.
Try this scenario instead: How do you PREVENT such deletions from occurring? Answering that question will help you sleep at night.
Partial answers:
Developers CANNOT touch production data. They can only touch development, and occasionally, QA data.
A QA process is required before system changes can be implemented. The QA process is designed to identify 'rogue' transaction processes that can introduce the type of problem you experienced, and return the change as failed, so production is not affected.
At least one other person, presumed competent, is involved in anything that changes the database other than normal production processes.
Users are not permitted to use 'back doors' to cover their mistakes in system operations. If the software does not support the user unwinding their transaction (preferably with audit trail), then LOTS of people have to get involved in agreeing with the proposed solution. Usually, having people looking over your shoulder will help minimize the risk of 'bonehead' errors. Won't eliminate the risk, but will minimize.
ok... the choir may continue their rehearsal - i'm done preaching. (and i know the list is incomplete)
September 26, 2008 at 2:59 am
Hi steve smith
Thanks for sharing your valuable inputs.
I will take care of user from deleting rows.
The data base and application is in testing phase and not gone into production.
I was testing for this kind of possibilities where in which the user may delete the row
directly from the data base. Now i will think about preventing user from doing this kind of
destructive operations on data base.
Thanks again.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply