How to "undo" a database delete

[Morgan Bachu]

Hello everybody,

I have an intranet application which is mostly a bunch of data editing
forms.

The database has about 20 tables all related together.

Recently somebody deleted a "the wrong record" and the cascade delete
in sql server did its work nicely :(

When they asked me to put the data back, I looked pretty stupid
as I had no idea how.

How is this normally done in ASP.NET app?

Morgan

[John Rivers]

Hi Morgan,

I have recently been trying to solve this problem too.

(I am assuming you are using SQL Server)

This is what I came up with:

Using "IsDeleted" type approach is OK but try and use a timestamp
so null = not deleted, not null = time of deletion
this enables you to purge records that really should be deleted
as they are only taking up space

the biggest problem with this approach is it doesn't work very well
for more complex schemas with cascade deleting etc.

it can work but it gets complicated very quickly

to solve this you can combine a table which logs the deletes with a
timestamp
a trigger on the tables you want to protect
and then a script that does a point in time restore of the database (to
a new database)
and then simply copies the records back

here is some example code:

--example table

create table LogDeletions (
LogID int primary key identity
, LogDate datetime not null default getdate()
, TableName varchar(255) not null
, RecordID int not null
)

-- example trigger

create trigger trAuditLogDelet e
on auditlog
for delete
as
--
insert into LogDeletions (LogDate, TableName, RecordID)
select getdate(), 'AuditLog', AuditLogID
from deleted
--(end)

-- example point in time restore

drop database Recovery
go
restore database Recovery
from disk = 'filename of last full backup', norecovery
go
restore log Recovery
from disk = 'filename of suitable log file', stopat = '2005-05-05
12:01:32.435'
go
--example insert of related records

select primarykey into #temp1
from firstdependentt able where maintable.forei gnkey = the one deleted
from the maintable
insert into originaldatabas e.dbo.seconddep *endenttable
select *
from seconddependent table
where firstdependentt able.foreignkey in (select primarykey from #temp1)

insert into originaldatabas e.dbo.firstdepe *ndenttable
select *
from firstdependentt able
where primarykey in (select primarykey from #temp1)
insert into originaldatabas e.dbo.maintable
select *
from maintable
where primarykey = the one deleted from the maintable

this isn't a fully automatic restore solution
but more of an accelerated disaster recovery
solution

i hope it helps,

it works fine for me

and, except for the triggers is "non-invasive"
ie: i didn't have to rewrite 100 queries etc.

All the best,

John Rivers

[tom pester]

The most unobtrusive way I can come up with is the use a tool that scans
the transaction log and can selectively rollback certain transactions.
This requires no architectural changes.

There is actually a faq about it:

http://www.aspfaq.com/show.asp?id=2449

These are the products that get mentioned in the article:

Apex SQL Log

Log Explorer

Log P.I.

SQL Log Rescue

Shortcomings:
- No web interface to let savvy site users do a rollback
Cheers,
Tom Pester

Hi Morgan,

I have recently been trying to solve this problem too.

(I am assuming you are using SQL Server)

This is what I came up with:

Using "IsDeleted" type approach is OK but try and use a timestamp
so null = not deleted, not null = time of deletion
this enables you to purge records that really should be deleted
as they are only taking up space
the biggest problem with this approach is it doesn't work very well
for more complex schemas with cascade deleting etc.

it can work but it gets complicated very quickly

to solve this you can combine a table which logs the deletes with a
timestamp
a trigger on the tables you want to protect
and then a script that does a point in time restore of the database
(to
a new database)
and then simply copies the records back
here is some example code:

--example table

create table LogDeletions (
LogID int primary key identity
, LogDate datetime not null default getdate()
, TableName varchar(255) not null
, RecordID int not null
)
--example insert of related records

select primarykey into #temp1
from firstdependentt able where maintable.forei gnkey = the one deleted
from the maintable
insert into originaldatabas e.dbo.seconddep *endenttable
select *
from seconddependent table
where firstdependentt able.foreignkey in (select primarykey from
#temp1)
insert into originaldatabas e.dbo.firstdepe *ndenttable
select *
from firstdependentt able
where primarykey in (select primarykey from #temp1)
insert into originaldatabas e.dbo.maintable
select *
from maintable
where primarykey = the one deleted from the maintable
this isn't a fully automatic restore solution
but more of an accelerated disaster recovery
solution
i hope it helps,

it works fine for me

and, except for the triggers is "non-invasive"
ie: i didn't have to rewrite 100 queries etc.
All the best,

John Rivers

[Morgan Bachu]

I checked out Log Explorer once

it was very slick but quite expensive

i feel for all the people who paid
for such software unnecessarily
as i almost did.

The author of the article you mentioned
also missed the fact that you can
backup a database after you mess it up
and still restore to a point in time before the backup.

Of course Lumigent will never admit that if you ring them - they
totally lied to me
on the phone - you would think a case of mushroom management of staff
(keep them in the dark and feed them sh*t) but i could hear they were
lying so
I researched further and solved my problem without it - great product
but don't
be tricked into buying it unnecessarily - knowledge is power :-)

[James Doughty]

For SQL Server I would suggest creating z-Tables. Anytime a record is
deleted (and I would also suggest for updated) just insert a record into
your backup table. If you doing eveything through stored procs you can do a
simple insert, if your doing direct table access I would suggest using
triggers to do the insert.

"Morgan Bachu" <ds********@tis cali.co.uk> wrote in
news:11******** **************@ g14g2000cwa.goo glegroups.com:

Hello everybody,

I have an intranet application which is mostly a bunch of data editing
forms.

The database has about 20 tables all related together.

Recently somebody deleted a "the wrong record" and the cascade delete
in sql server did its work nicely :(

When they asked me to put the data back, I looked pretty stupid
as I had no idea how.

How is this normally done in ASP.NET app?

Morgan