Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
R
Constraint Violating Yak Guru
328 Posts |
 Posted - 2009-02-18 : 04:33:51
|
Today I was thinking about a problem that might happen in SQL, and realised I don't actually know the answer to it. Please consider the following backup schedule for a database:SUNDAY 20:00:00 FullMONDAY 20:00:00 DifferentialTUESDAY 20:00:00 DifferentialWEDNESDAY 20:00:00 DifferentialTHURSDAY 20:00:00 DifferentialFRIDAY 20:00:00 DifferentialSATURDAY 20:00:00 DifferentialEVERY DAY, EVERY HOUR Transaction Log Suppose that on Tuesday at 22:00:00 a client logs into the database and 'accidentally' deletes some of their data. They don't realise this until Thursday, and then send me an email asking for help. In the meantime, other users have continued to use the database and have inserted/edited lots of records.How does a DBA go about retrieving this deleted data? Is it actually possible to do?I would imagine you would restore Tuesday's backup to a copy of the original database, then manually find the deleted records and insert them back in. That's just a guess though - I would welcome some real expert feedback...!  |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-02-18 : 06:36:12
|
| There is no way of doing this automatically since adding the data that was accidentally deleted back again could cause inconsistencies (there is no way of foreseeing what could have happened to that data if they had not been deleted). So you're left with two options one of which you have already suggested:- Restore a copy of the database and retrieve the data from there- Obtaining sql server log reading softwareI know that red-gate has a free log reader for sql server 2000 and if I remember correctly quest has one for both 2000/2005, but it's really e x p e n s i v e !- Lumbago |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-18 : 08:35:36
|
| You can restore from Sunday Full backup,Tuesday Differential and transaction log backup till 22:00 (With STOPAT option) with different database name. You can compare your records with your original database to find out what was deleted. It is possible. |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2009-02-18 : 09:21:31
|
HiThank you for replying.quote:
There is no way of doing this automatically since adding the data that was accidentally deleted back again could cause inconsistencies
quote:
You can restore from Sunday Full backup,Tuesday Differential and transaction log backup till 22:00 (With STOPAT option) with different database name. You can compare your records with your original database to find out what was deleted. It is possible.
I appreciate that. Suppose in an ideal world however, that I had performed the backup and I could see the differences in data between the two tables. Is there a way in T-SQL to achieve the following:- Select the deleted data from the backed up table,
- If so, can a table variable copy data from one database to another
- Remove the index and primary key constraints on the destination table
- Insert the data from the temporary table, then reactive the index & primary key constraints on the active table
Sorry for all those questions(!) |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-18 : 09:28:35
|
| Yes you can. You can restore from old backup and compare with recent table and insert IF NOT Exist to the destination table. Also you can use STOPAT option to nail down when delete occurred. |
|
|
|
|
|
|
|
|
Restoring selected data in between backups