Post reply

Moving a DB with compressed tables, what happens?

  • October 30, 2013 at 1:02 pm

    #395750

    I'm going to be migrating a DB to a new server in the next couple weeks, and have a question. The DB in question has several tables / indexes compressed (page compression) to keep the physical size down.

    Currently, I migrate by running a backup, taking the original DB offline, copying the backup to the new server and restoring.

    So, simple question. Will the compression remain "on" if I move the DB that way?

  • October 30, 2013 at 1:04 pm

    #1662830

    A restore recreates a database exactly as it was at the time of the backup.

    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

  • October 30, 2013 at 1:28 pm

    #1662835

    Cool, thanks.

    Which, now that I think about it, I should've realized. It'd be outright silly for it *not* to be exact, up to and including compressed items...

  • October 31, 2013 at 1:22 pm

    #1663244

    Only one other thing to add to this, just be sure you are migrating to an Enterprise edition SQL Server, as I do not believe that compression is supported in Standard Edition (yet).

  • November 1, 2013 at 1:35 am

    #1663351

    Matt Crowley (10/31/2013)

    Only one other thing to add to this, just be sure you are migrating to an Enterprise edition SQL Server, as I do not believe that compression is supported in Standard Edition (yet).

    Correct. The restore will fail if attempted on Standard Edition.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • November 1, 2013 at 5:26 am

    #1663381

    One nice thing about my place of work...

    Everything is (mostly) standardized. Enterprise Edition SQL 2008 R2, Enterprise Ed Server 2008R2 (or just plain 2008 Enterprise Ed, which is what we're migrating off of)

    So at least I know everything that was supported on the old server, is on the new.

    (It also helps that I'm the one who installed SQL, so I know what version I loaded 😀 )

    Thanks,

    Jason

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

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