Post reply

Restore part of the data

  • February 27, 2008 at 5:34 pm

    #120551

    I have a following request from the user: to have an option to restore part of the database, based on the data.

    A little explanation: the user deals with projects, which will have some other relevant objects. The Project data is just one row of one table, but there are some other tables/rows, assosiated with it. If one of the users accidentally deletes a project or just does something wrong with it, they want to be able to restore the yesterday version of the project and all relevant data from the backUp file

    What approach to the problem would experts suggest?

    My idea is to restore the back up to the different database and than move relevant data to the production database. It might have problems, relevant to the speed and extra disk space required.

    Thank you to anybody answered

    DB Developer

    Can anybody suggest something more clever?

  • February 27, 2008 at 7:31 pm

    #782954

    Rumor has it that creating a table on a different filegroup will do what you ask.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 27, 2008 at 8:47 pm

    #782965

    Can you, please give more details?

  • February 27, 2008 at 11:20 pm

    #782995

    Rumour also has it that database snapshots may allow what you want, without the need to restore a database at all.

    Re filegroups - You can restore individual filegroups in SQL Server 2005, however to bring the filegroup online, you have to apply all the tran log backups (iirc) and so it may not be effective for restoring just part of a DB to a specific point. DO test this though, I haven't done much with filegroup backups before.

    Database snapshots are a new SQL 2005 feature. they act like a read-only point-in-time view of a database. If you create a snapshot at 7am, thenmake changes to the source DB, the snapshot will still show the data as it was at 7am. You can use that to retrieve data that's been deleted or changed in the source DB.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 28, 2008 at 1:09 am

    #783024

    Hi

    Jeff , Gail -

    Does this mean creating each table in a diff file group . This way user will have option to restore only the table that user requires to be restore ? Or have i misunderstood ?

    "Keep Trying"

  • February 28, 2008 at 6:28 am

    #783127

    I believe that is correct... I say "believe" because I've never had to do it... I've only read about it. Gails snapshot option is also viable.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 28, 2008 at 3:13 pm

    #783482

    Thanks, everybody.

    I believe the filegroups options will not be OK for me, because I will need to restore not one or two tables, I will need to restore number of RECORDS from different tables.

    But the snapshot option will work, I think.

Viewing 7 posts - 1 through 6 (of 6 total)

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