Post reply

Restoring File Group back up onto another Server?

  • June 22, 2016 at 8:41 am

    #327386

    I'd like to split a DB into 2 filegroups, PRIMARY & HISTORY

    HISTORY would contain one large table, this table > 99% of the total DB size.

    The DB is in simple recovery mode.

    I think the answer is No, but would like confirmation.

    Is it possible to restore a filegroup backup onto another Server?

    I often have to copy this data to another server, being able to copy 200MB rather than 20GB is really advantageous.

    Thanks for reading my post

  • June 22, 2016 at 8:48 am

    #1886283

    Yes, you can, but you need to restore the full backup first, so I suspect that would defeat the object. You might consider putting your HISTORY table in a separate database instead.

    John

  • June 22, 2016 at 9:01 am

    #1886297

    Thanks John, you are right, another DB would be best, but not an option 🙁

    The large big db gets backed up daily.

    Say I restore monday's full backup (30GB) to my local machine. On Friday I take a filegroup backup and restore to my local machine, with monday's data, will this work?

    Are you saying I would need Friday's Full backup too. Which in this case does defeat my purpose.

    Cannot think straight on this one, how can you restore only some filegroups if there are referential constraints between tables in the 2 filegroups? there aren't in my case..

    Thanks

  • June 22, 2016 at 9:19 am

    #1886308

    terry999 (6/22/2016)

    Is it possible to restore a filegroup backup onto another Server?

    Only if you start by restoring primary and, since the DB is in simple recovery, all the filegroups you restore have to be part of the same backup (log backups are needed when restoring from multiple different filegroup backups)

    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

  • June 22, 2016 at 9:23 am

    #1886313

    terry999 (6/22/2016)

    Say I restore monday's full backup (30GB) to my local machine. On Friday I take a filegroup backup and restore to my local machine, with monday's data, will this work?

    No, as you're in simple recovery model.

    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

  • June 22, 2016 at 9:30 am

    #1886316

    As per Gail, https://msdn.microsoft.com/en-us/library/aa337540.aspx

    Read this before you do anything.

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

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