Post reply

How do you restore a data

  • April 3, 2007 at 6:20 am

    #116774

    Hi,

    In a backup schema where full backups are done every night and transaction backups are performed every hour. If a user deletes few hundred rows, what's the best way to restore the data under the scenario that,

    a.) User realizes (within an hour) that (s)he accidentally deleted and requests that the data be restored?

    b.) User realizes after couple days and needs the data restored only in that table.

    How do a DBA go about handling these tasks in 2000 and 2005.

    Thanks.

  • April 3, 2007 at 7:46 am

    #698280

    Hi Ram,

    the process you use is the same whether you use SQL 2005 or 2000.

    a) Your best bet is to restore the full database backup onto another database (and server is possible) and then restore all of the tran log backups up to the point prior to which the user deleted the data.  Remember to use norecovery when restoring these backups.  You'll then want to perform a point-in-time restore of the log backup that was taken immediately after the user performed the delete, specifying the point in time immediately prior to the delete operation.  However, you must be aware of the fact that any transactions between this point-in-time oand the time at which the user actually performed the delete will be lost.  And those transactions might be important (or not).

    b) You cannot restore data from just one table (unless you use a third-party product).  So as long as you retain your backups for more than a couple of days you'll be fine running the above process.

    Note you could potentially restore data from just one table if that table was on a seperate file group but I won't go into that as it's likely you don't have it on a seperate file group.

    Hope that helps,

    Karl
    source control for SQL Server

  • April 3, 2007 at 8:26 am

    #698293

    Your best bet is to follow the advice from Karl and restore to another database and copy the data back. I've done that often, even had to this year

    However, you need to be aware of which tables you're working with. If it's s lookup table, no problem. If it's some other type of table, like orders, then you want to be sure that restoring the rows doesn't affect the data in other parts of your database. Also be sure that you didn't lose related data through cascading deletes.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply