Post reply

exclude specific tables during the database restore

  • July 23, 2013 at 4:41 am

    #296667

    Hi All,

    Is there a way to exclude specific tables during the SQL database restore?

    Thanks

  • July 23, 2013 at 4:51 am

    #1634866

    No.

    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

  • July 23, 2013 at 5:03 am

    #1634873

    An possible alternative could be (keep in mind that I'm not a restore expert) is to put the tables you do not want to restore on a different filegroup. Then restore only the filegroup that you want.

    (this won't help of course with your current backup)

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 23, 2013 at 5:05 am

    #1634875

    thanks 🙂

  • July 23, 2013 at 5:08 am

    #1634876

    i think i'd export the tables out to a different database, restore, and then delete and insert back into the original table...but if you've got foreign keys involved, that would most likely complicate the issue enormously.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 23, 2013 at 5:16 am

    #1634879

    Koen Verbeeck (7/23/2013)

    An possible alternative could be (keep in mind that I'm not a restore expert) is to put the tables you do not want to restore on a different filegroup. Then restore only the filegroup that you want.

    I just knew someone was going to suggest this...

    It doesn't quite work that way. Sure, if the table you don't want restored is on a filegroup and you don't restore that filegroup, then the table's not taking any space. It's still in the system catalogue though, SQL still thinks it's a valid table that exists, however you can't do a thing to that table (including dropping it) with the filegroup the table's in offline. Hence you will be permanently left with a phantom table that causes errors if ever referenced and which can't be removed.

    Oh, and you always have to restore PRIMARY. Other filegroups can be left out, PRIMARY cannot.

    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

  • July 23, 2013 at 5:36 am

    #1634887

    GilaMonster (7/23/2013)

    Koen Verbeeck (7/23/2013)

    An possible alternative could be (keep in mind that I'm not a restore expert) is to put the tables you do not want to restore on a different filegroup. Then restore only the filegroup that you want.

    It doesn't quite work that way. Sure, if the table you don't want restored is on a filegroup and you don't restore that filegroup, then the table's not taking any space. It's still in the system catalogue though, SQL still thinks it's a valid table that exists, however you can't do a thing to that table (including dropping it) with the filegroup the table's in offline. Hence you will be permanently left with a phantom table that causes errors if ever referenced and which can't be removed.

    So you're saying that if a restore a filegroup (primary and maybe others), all the other filegroups are taken offline?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • July 23, 2013 at 5:38 am

    #1634889

    Koen Verbeeck (7/23/2013)

    So you're saying that if a restore a filegroup (primary and maybe others), all the other filegroups are taken offline?

    They can't be online, because they're not there. The actual state of the files is RECOVERY_PENDING, but any attempt to access anything in those filegroups gets you an error saying that the filegroup is offline.

    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

  • July 23, 2013 at 5:56 am

    #1634896

    GilaMonster (7/23/2013)

    It doesn't quite work that way. Sure, if the table you don't want restored is on a filegroup and you don't restore that filegroup, then the table's not taking any space. It's still in the system catalogue though, SQL still thinks it's a valid table that exists, however you can't do a thing to that table

    The whole things does of course boil down to why Sqlsavy do not want to restore those tables. If these are big archive tables, and he want to improve is RTO, Koen's suggestion is valid. Of course, Sqlsavy will need to restore the filegroup at some point, as presumably the tables fills some purpose.

    If Sqlsavy wants to restore a copy of a production database in a test/developepment environment, and preserve space by exluding the archive tables, this works well, as long as there is no need to access these tables in the other environment.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 9 posts - 1 through 8 (of 8 total)

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