Moving a DB with compressed tables, what happens?
-
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?
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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...
-
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).
-
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.
-
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