March 30, 2011 at 8:01 am
Need advice...
Backup Schedule: Full Every Night at 11pm | Log Every 4 hours
Tuesday developer deletes data out of two tables and doesn't say anything until the following week.
Now two weeks of additional data have been going into the database but he wants only what was deleted. How can can restore what was deleted while retaining all the data that has been going into the database since.
Thanks!
March 30, 2011 at 8:05 am
Do you still have the full backup from before the developer deleted stuff? Do you still have log backups from that point up til when he deleted the data?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2011 at 9:19 am
I have Full backups from:
3/14:2300 | 3/15:2300 and on
And Logs from:
3/15:0000 | 3/15:0400 | 3/15:0800 |3/15:1200 |3/15:1600 |3/15:2000
The developer deleted that data on Tuesday 3/15 around Noon I believe.
Being the genious that I am the first thing I did was run a restore to 'Most Recent Time Availabe'
only to discover that the same tables were missing the same data. I then realized that this was because his command to delete the data was recorded on one of the log files and all I did by restoring to present time was to reissue the same delete statement which brought me back to square one. So From what I have read I need to restore the database as it was just before the data was deleted to another database (Lets call it database 'PAST'). Then export the data from the table in database 'PAST' and import the data into Database 'Present' while merging the data. Is this what I need to do?
March 30, 2011 at 10:43 am
Vertigo44 (3/30/2011)
So From what I have read I need to restore the database as it was just before the data was deleted to another database (Lets call it database 'PAST'). Then export the data from the table in database 'PAST' and import the data into Database 'Present' while merging the data. Is this what I need to do?
Absolutely.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2011 at 6:10 am
Thanks Gila!
I am not seeing the missing transactions. Let's say the developer doesn't actually remember what day/time he issued the delete statement. Is there a way in SQL 7 that I can search for deletes issued to a particular database so I can try and pinpoint this data deletion event?
March 31, 2011 at 7:06 am
SQL 7?
From the forum you posted in I assumed 2005.
You can restore the logs with standby and inch the STOP AT time forward bit by bit. Tedious, but that's about the only way. Unless you had a trace in progress at the time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2011 at 8:23 am
GilaMonster (3/31/2011)
SQL 7?From the forum you posted in I assumed 2005.
You can restore the logs with standby and inch the STOP AT time forward bit by bit. Tedious, but that's about the only way. Unless you had a trace in progress at the time.
Ughh. Yeah I think I'm going to read up on taking away delete rights next! 😀
Is there a way I can let the developers *Think they have deleted data but just hide it from them instead?
March 31, 2011 at 8:46 am
Vertigo44 (3/31/2011)
Ughh. Yeah I think I'm going to read up on taking away delete rights next! 😀
If this is a production server, why the hell do they have any rights whatsoever?
Is there a way I can let the developers *Think they have deleted data but just hide it from them instead?
No, but I find 'Delete Permission Denied' to be very effective (along with a SQL manual to hit them with when they come whining)
p.s. I am a developer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2011 at 9:11 am
GilaMonster (3/31/2011)
Vertigo44 (3/31/2011)
Ughh. Yeah I think I'm going to read up on taking away delete rights next! 😀If this is a production server, why the hell do they have any rights whatsoever?
Is there a way I can let the developers *Think they have deleted data but just hide it from them instead?
No, but I find 'Delete Permission Denied' to be very effective (along with a SQL manual to hit them with when they come whining)
p.s. I am a developer.
:blink: What! You're also a developer??!?? and you know this much about administration? So how are those 38 hour days on your planet?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply