none
How to restore delete data and keep new add data

    Question

  • I need to delete some old data from DB.

    But I still want to backup them once they need to be restored anytime. Maybe some user need those data after I deleted them, thus, I have to restore them ASAP.

    Once I delete the data, there still have new data add into DB.

    I know full backup + transaction Log can store the delete data. But look like it only store the DB to the status before I delete. That means, all new add data will lose if I retore DB in this way.

    Is there any way I can backup those delete old data, and restore it anytime, and keep the new add data?

    Any feedback is really appreciate!

    Friday, July 19, 2013 6:56 PM

Answers

All replies

  • Hi,

    I'd recommend you to do some research on database archiving tools.

    The easiest solution would be to take read-only by-the-side copies of your database at fixed points

    of time(let's say every January 1st at midnight), but that tends to be quite innefficient from a disk

    usage point of view.

    Of course, there are 3rd party tools that deal with this problem in a more elegant way.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, July 19, 2013 7:05 PM
  • You can create readonly database snapshots for the same

    Thanks- Prajesh Please mark the post as answered if it answers your question

    Friday, July 19, 2013 7:10 PM
  • Thanks for your reply. But looks like, the update data after database snapshots creation, still will lose if restore database from snapshots, right? Is that possible to keep the update data, and restore delete data?
    Friday, July 19, 2013 9:14 PM
  • In that case create archive tables and use SQL Server Compression to compress the archive tables. Add a archiving SSIS job to do the same and schedule it.

    Thanks- Prajesh Please mark the post as answered if it answers your question

    • Proposed as answer by Prajesh Monday, July 22, 2013 11:09 AM
    Friday, July 19, 2013 9:32 PM
  • Why not having a dedicated database for Archived data... You can use the below technique,

    --SQL2008
    insert into Archive..tblArchive 
    select getdate(),d.*
    from (delete top (10000) 
            from db..tbl
            output deleted.*) d
            go


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, July 21, 2013 9:03 AM