Backup/restore parition

  • I do partitions on all my tables based on a date column and the created 1 file for each month. Now i want to backup and restore only 1 year worth of data (12 files/ file groups), is that possible? if so how?

  • I believe it's as simple as backing up and restoring by file name. If you need to do a restore and the partition file isn't read_only, then you'll need to do a Tail Log Backup first and that won't take long at all.

    --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)

  • These two movies by Kimberly Tripp should help you a lot.

    https://technet.microsoft.com/en-us/sqlserver/gg545009.aspx

    https://technet.microsoft.com/en-us/sqlserver/gg545008.aspx

    --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)

  • is it possible to do file group restore keeping the database in "simple" recovery model? we always keep the databases in simple mode as the data is not changing on a daily basis, changes only once in a month.

  • No, not unless the file groups in question are read only.

    To restore a DB from file/filegroup backups, the following is required:

    1) If the DB is in simple recovery, then any filegroup backed up at a different time to PRIMARY has to have been read-only at the time of backup or it cannot be brought online

    2) If the DB is in full recovery, then log backups are needed over the interval from the oldest backup used to the newest.

    It's never a case of just restoring the file/filegroup. If you're restoring to a new database, the Primary filegroup has to be restored first, then any file/filegroup backups, the the log backups need to be restored in order to bring everything online. Restoring over an existing DB is even more complex.

    There's also restrictions on Enterprise vs Standard edition.

    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
  • Thanks Gail. I think i will have to go with your 1st option, here is what i think please correct me if i understood wrong

    1. My database is in simple recovery and i will make the entire database read only

    2. I will backup the full database as usual no differential or transaction backups as i we change data only once a month like a monthly load when users will not access the databases.

    3. restore only the file groups that is needed.

    4. yes i do have sql server 2005 enterprise. I will be backing up from 2008 R2 and restore on 2005 Ent.

  • GilaMonster (1/21/2015)


    No, not unless the file groups in question are read only.

    To restore a DB from file/filegroup backups, the following is required:

    1) If the DB is in simple recovery, then any filegroup backed up at a different time to PRIMARY has to have been read-only at the time of backup or it cannot be brought online

    2) If the DB is in full recovery, then log backups are needed over the interval from the oldest backup used to the newest.

    It's never a case of just restoring the file/filegroup. If you're restoring to a new database, the Primary filegroup has to be restored first, then any file/filegroup backups, the the log backups need to be restored in order to bring everything online. Restoring over an existing DB is even more complex.

    There's also restrictions on Enterprise vs Standard edition.

    You're not including online piecemeal restores in that, correct?

    --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)

  • Tara-1044200 (1/21/2015)


    I will be backing up from 2008 R2 and restore on 2005 Ent.

    No, you won't, because SQL databases cannot be downgraded. If you have a SQL 2008 R2 database you can restore to SQL 2008 R2, SQL 2012 or SQL 2014.

    If you're going to take a full database backup and then only restore some partitions, then the read-only requirement is not relevant. That requirement comes into play when you're backing up filegroup 1 on day 1, filegroup 2 on day 2, etc and then you want to restore those backups into a single database.

    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
  • Jeff Moden (1/21/2015)


    GilaMonster (1/21/2015)


    If you're restoring to a new database, the Primary filegroup has to be restored first, then any file/filegroup backups, the the log backups need to be restored in order to bring everything online. Restoring over an existing DB is even more complex.

    You're not including online piecemeal restores in that, correct?

    No, because the OP had no mention of restoring a bit now and a bit later. That's even more complex and is what has the Enterprise restriction.

    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
  • GilaMonster (1/21/2015)


    Jeff Moden (1/21/2015)


    GilaMonster (1/21/2015)


    If you're restoring to a new database, the Primary filegroup has to be restored first, then any file/filegroup backups, the the log backups need to be restored in order to bring everything online. Restoring over an existing DB is even more complex.

    You're not including online piecemeal restores in that, correct?

    No, because the OP had no mention of restoring a bit now and a bit later. That's even more complex and is what has the Enterprise restriction.

    Got it. Thanks, Gail. Thought I had reeled it out and stepped on it again.

    --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)

  • I am very frequently using "Ahsay software" and quite satisfied with their . Thus i am leaving below this comment just have a look at the link and the software too.

Viewing 11 posts - 1 through 10 (of 10 total)

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