April 12, 2011 at 10:22 am
Customer issues delete. Initally asked to restore data that was deleted from one table but continues to use the system.
Full backups everynight and logs every 4 hours.
Not thinking I run a restore to just before the 'thought to be delete date/time'. Well now we have the data that was deleted but what about all the data that was entered into the system in between the restore to time and present? Need to get those from the logs.
Only now the customer realized the delete, deleted a lot more than data in a single table but many tables instead. This is getting a little confusing honestly and i'm having a hard time figuring out what needs to happen in order to only apply the missing data.
I was able to restore to several databases naming them the date and time of the last log file. I then would try and query to compare the data in each database to production to see which one had more records available. This is getting screwy.
Here is what I need to do...
For each table, of which we will call Live Table = L.Table and Backup Tables = B.Tables
I need to compair B.Tables to L.Tables and synchornize them in a manner that I can merge all data that exisits in B.Table but do NOT exsist in L.Table and add only those rows to L.Table.
I think that is what needs to happen. What is the best way to accomplish this?
April 12, 2011 at 10:34 am
Redgate has a data compare tool you could consider.
Or write queries comparing all the fields in all the table affected.
Give customer read-only permission.
April 12, 2011 at 10:37 am
Hi,
what version of sql server you have? And are the live tables connected to each other via foreign keys?
If you want to merge all tables I would suggest to write a sql script with a cursor through all tables which take advantage of the MERGE statement in a dynamic sql statement.
Greets
Patrick Fiedler
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
April 12, 2011 at 12:45 pm
The thing to do is restore to a moment in time before the event, but do the restore on a separate database. But then, you have to figure out how to merge the data from the old stuff to the new. Honestly, the easiest way I can think of to do that is to use Red Gate SQL Compare (my company, so I'm trying to be up front about that). You can write queries to do it, but it's going to take quite a long time and the business is waiting for you.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply