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 |
|
nic
Posting Yak Master
209 Posts |
 Posted - 2007-01-25 : 12:06:16
|
| Ok, I have a client who accidentally ran a delete script against the wrong database. The delete script contains a number of delete statements:delete from table1delete from table2delete from table3After he ran it, he realized his mistake and cancelled the execution. Table1 has no records but table2 does. The problem is table2 has a ton of foreign key constraints with cascading deletes. If the process is cancelled half way through, are all the child records restored? (i.e. if we cancel the table2 delete any child records that were to be deleted, are they restored back?) I don't think so, but was hoping if someone could verify. The entire script wasn't in a transaction but know individual sql statements inheriently run in a transaction. Would deleted child records be rolled back?If not, we will need to revert to backups... a quick reply would be appreciated.Thanks,NicNic |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-25 : 13:30:13
|
| get those backups ready to restore. entire batch will not be rolled back. |
 |
|
|
nic
Posting Yak Master
209 Posts |
Posted - 2007-01-25 : 14:19:56
|
| Thats what I thought but I wanted to verify. We rolled back to the previous backup. There was some data loss, but at least we know the database is stable.Thanks,NicNic |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-26 : 02:46:14
|
| If your database is using FULL Recovery Model you could have taken a TLog backup, and then restored to point-in-time just before the accidental deletes.Failing that I would have taken a Full backup, then did the Restore you did, and then restored the extra backup to a "new" database and used that to pick and choose data to copy across - or at least to produce lists of missing data for the data entry folk to recreate.An alternative might have been to restore the backup you used to a new temporary database and copy across the missing data to table1, table2, etc. ... depends a bit on how heavily those tables have been used since the previous backup.Kristen |
|
|
|
|
|
|
|
|
Urgent: deleted wrong records (ugh)