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.seconddependenttable
select *
from seconddependenttable
where firstdependenttable.foreignkey in (select primarykey from #temp1)
insert into originaldatabase.dbo.firstdependenttable
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