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

[Lau Lei Cheong]

I supposed this is not quite an ASP.NET related question, it's more database
related.

So you should tell us what database you're using.And if you're using MSSQL,
you'll get better chance being answered if you're posting in
microsoft.public.sqlserver.server newsgroup.

"Morgan Bachu" <ds********@tiscali.co.uk>
???????:11**********************@g14g2000cwa.googl egroups.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

[Hans Kesting]

Morgan Bachu wrote:

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

If it's deleted from the database, then it is gone (no "recycle bin")!
Time to check if the backup procedure really works, I'm afraid.

Hans Kesting

[tom pester]

What database is it ? Sql server, Access, ...

Let me know if you have any more questions...

Cheers,
Tom Pester

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

[Lau Lei Cheong]

I remember that if it's MS Access or so, when the record is deleted but not
packed, there's some change to recover...

"Hans Kesting" <ne***********@spamgourmet.com> ¼¶¼g©ó¶l¥ó·s»D:e5**************@TK2MSFTNGP15.phx.g bl...

Morgan Bachu wrote:

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

If it's deleted from the database, then it is gone (no "recycle bin")!
Time to check if the backup procedure really works, I'm afraid.

Hans Kesting

[Juan T. Llibre]

What I would do is set up an identical set of tables, prefaced
"bk_thetablename" and store any records deleted in *that* set of tables.

Kind of like an audit trail.

That way, although you delete records,
you still keep all records, deleted or not, permanently.


Juan T. Llibre
ASP.NET MVP
http://asp.net.do/foros/
Foros de ASP.NET en Español
Ven, y hablemos de ASP.NET...
======================

"Morgan Bachu" <ds********@tiscali.co.uk> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.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

[tom pester]

In sql server you can undelete it by examening the transaction log. There
are tools to make it easy.

A general approuch it to take backups so you can go back in time or do the
undelete manualy with some copy & pasting.

Cheers,
Tom Pester

Morgan Bachu wrote:

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

If it's deleted from the database, then it is gone (no "recycle bin")!
Time to check if the backup procedure really works, I'm afraid.

Hans Kesting

[tom pester]

Or you could do a soft delete. When the user deletes a row you flip a switch
in a column, so :

delete from table where id = 8
becomes
update table set deleted = true

You can make it transparent for the developer by making a view of the table
like this :

Employees_All : a table that has also the deleted records
Employees : SELECT * FROM Employees where deleted = false

Let me know if you have any more questions...

Cheers,
Tom Pester

What I would do is set up an identical set of tables, prefaced
"bk_thetablename" and store any records deleted in *that* set of
tables.

Kind of like an audit trail.

That way, although you delete records,
you still keep all records, deleted or not, permanently.
Juan T. Llibre
ASP.NET MVP
http://asp.net.do/foros/
Foros de ASP.NET en Español
Ven, y hablemos de ASP.NET...
======================
"Morgan Bachu" <ds********@tiscali.co.uk> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.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

[LogicaLunatic]

Morgan,

This isn't so much of an ASP.NET question as it is an application design
question.

If an application I build allows the user to delete anything it is at most
only a logical delete. Basically I have a field in each table named
isDeleted. If set to true then that row is 'logically' deleted when it comes
to the application but the data is still there.

In one case I even created a Deleted Items page so the admins could go see
what's been deleted and 'un-delete' anything they wanted to.

"Morgan Bachu" wrote:

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

[Juan T. Llibre]

That is a viable database design idea, too.

Juan T. Llibre
ASP.NET MVP
http://asp.net.do/foros/
Foros de ASP.NET en Español
Ven, y hablemos de ASP.NET...
======================

"tom pester" <To********************@pandora.be> wrote in message
news:a1***************************@news.microsoft. com...

Or you could do a soft delete. When the user deletes a row you flip a switch in a
column, so :

delete from table where id = 8
becomes
update table set deleted = true

You can make it transparent for the developer by making a view of the table like this :

Employees_All : a table that has also the deleted records
Employees : SELECT * FROM Employees where deleted = false

Let me know if you have any more questions...

Cheers,
Tom Pester

What I would do is set up an identical set of tables, prefaced
"bk_thetablename" and store any records deleted in *that* set of
tables.

Kind of like an audit trail.

That way, although you delete records,
you still keep all records, deleted or not, permanently.
Juan T. Llibre
ASP.NET MVP
http://asp.net.do/foros/
Foros de ASP.NET en Español
Ven, y hablemos de ASP.NET...
======================
"Morgan Bachu" <ds********@tiscali.co.uk> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.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

[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 trAuditLogDelete
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 firstdependenttable where maintable.foreignkey = the one deleted
from the maintable
insert into originaldatabase.dbo.seconddep*endenttable
select *
from seconddependenttable
where firstdependenttable.foreignkey in (select primarykey from #temp1)

insert into originaldatabase.dbo.firstdepe*ndenttable
select *
from firstdependenttable
where primarykey in (select primarykey from #temp1)
insert into originaldatabase.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 firstdependenttable where maintable.foreignkey = the one deleted
from the maintable
insert into originaldatabase.dbo.seconddep*endenttable
select *
from seconddependenttable
where firstdependenttable.foreignkey in (select primarykey from
#temp1)
insert into originaldatabase.dbo.firstdepe*ndenttable
select *
from firstdependenttable
where primarykey in (select primarykey from #temp1)
insert into originaldatabase.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********@tiscali.co.uk> wrote in
news:11**********************@g14g2000cwa.googlegr oups.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