How to "undo" a database delete

Discussion in 'ASP .Net' started by Morgan Bachu, Aug 30, 2005.

  1. Morgan Bachu

    Morgan Bachu Guest

    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
    Morgan Bachu, Aug 30, 2005
    #1
    1. Advertising

  2. 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" <>
    ???????:...
    > 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, Aug 30, 2005
    #2
    1. Advertising

  3. Morgan Bachu

    Hans Kesting Guest

    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
    Hans Kesting, Aug 30, 2005
    #3
  4. Morgan Bachu

    tom pester Guest

    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
    >
    tom pester, Aug 30, 2005
    #4
  5. 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" <> ¼¶¼g©ó¶l¥ó·s»D:e5Y5W$...
    > 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
    >
    >
    Lau Lei Cheong, Aug 30, 2005
    #5
  6. 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" <> wrote in message
    news:...
    > 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, Aug 30, 2005
    #6
  7. Morgan Bachu

    tom pester Guest

    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, Aug 30, 2005
    #7
  8. Morgan Bachu

    tom pester Guest

    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" <> wrote in message
    > news:...
    >
    >> 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, Aug 30, 2005
    #8
  9. 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
    >
    >
    =?Utf-8?B?TG9naWNhTHVuYXRpYw==?=, Aug 30, 2005
    #9
  10. 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" <> wrote in message
    news:...
    >
    > 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" <> wrote in message
    >> news:...
    >>
    >>> 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, Aug 30, 2005
    #10
  11. Morgan Bachu

    John Rivers Guest

    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
    John Rivers, Aug 30, 2005
    #11
  12. Morgan Bachu

    tom pester Guest

    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
    >
    tom pester, Aug 30, 2005
    #12
  13. Morgan Bachu

    Morgan Bachu Guest

    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 :)
    Morgan Bachu, Aug 31, 2005
    #13
  14. 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" <> wrote in
    news::

    > 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
    >
    >
    James Doughty, Aug 31, 2005
    #14
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Marco Liedekerken

    Undo in TextBox

    Marco Liedekerken, Jun 26, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    1,355
    Marco Liedekerken
    Jun 26, 2003
  2. john farrow

    How do I undo WebRequest.RegisterPrefix

    john farrow, Apr 13, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    831
    john farrow
    Apr 13, 2004
  3. tshad

    Undo formatting

    tshad, Apr 6, 2005, in forum: ASP .Net
    Replies:
    5
    Views:
    1,076
    tshad
    Apr 13, 2005
  4. Andreas Wachhold

    Undo/Redo Java?

    Andreas Wachhold, May 4, 2004, in forum: Java
    Replies:
    2
    Views:
    4,916
    Mykola Rabchevskiy
    May 4, 2004
  5. Adam

    Undo

    Adam, Sep 17, 2004, in forum: Java
    Replies:
    6
    Views:
    1,078
    Thomas Fritsch
    Sep 22, 2004
Loading...

Share This Page