SQL Server – Restoring Database with Data Compression

compressionrestoresql serversql-server-2008-r2

In our environment our production server is using MS SQL Server 2008 Enterprise Edition. We have a development and test server that runs MS SQL Server Standard Edition and the table structure between all three environments is exactly the same.

We frequently refresh our dev and test environments from our production database.

I'm looking into turning on data compression in our production environment, but reading this technet article it sounds like we will no longer be able to perform these refreshes. Am I reading this correctly?

Quote from article:

Databases with compressed tables or indexes cannot be restored, attached, or in any way used on other editions.

Will I be forced to upgrade our dev/test DB to Enterprise Edition to continue to refresh our data?

Best Answer

Correct, an error will be raised when attempting to RESTORE.

Same thing goes if other Enterprise features, such as table partitioning, are used. For those finding this answer in search, usage of Enterprise features are listed in sys.dm_db_persisted_sku_features.

What I would recommend is to use Developer Edition in your development and testing environments, if you don't support Standard in production (it sounds like your app is in-house only). This edition includes the Enterprise engine; it just isn't licenced to be used in production, and will be able to restore the Enterprise backups just fine.

Related Solutions

Restore DB2 Database – Cannot Connect After Offline Backup

Found the answer here.

I needed to run the command

db2 rollforward db database_name to end of backup and complete;

This commits everything to the logs and places the database in a no longer pending state, thus allowing connection to it. I needed to do this for each database restored.

EDIT: Found this nifty nugget while attending a DB2NightShow episode. With 9.7 FP2, they introduced a registry variable called DB2_RESTORE_GRANT_ADMIN_AUTHORITY. This is handy if you are restoring a backup from one instance to another, as it automatically grants instance owner SECADM, DBADM, DATAACCESS, and ACCESSCTRL on the databases restored. With 9.7 FP5, the setting is dynamic and does not require bouncing of the instance. I realize my original question had nothing to do with security, but it can be encountered and thought that this would be a handy tidbit placed here.

EDIT #2: I recently found out that if the backup is an offline backup based on an archival logged database, I could have included the clause WITHOUT ROLLING FORWARD in my RESTORE command. This would have taken the database out of roll forward pending state immediately upon successful restore, which would mean that I would not have had to then issue the ROLLFORWARD DB command.

Sql-server – DBCC CheckDB run into early hardware failure

When you first kick off a DBCC CHECKDB it will create a database snapshot to allow the database to stay online during the process. There are some things you can look at if you are running into problems with this:

Defragment you NTFS system, there is an issue where if the NTFS storage is heavily fragmented it will not allow SQL Server to create the database snapshot. This is because the snapshot can only have a certain amount of fragments.This is documented on the microsoft support center in OS errors 1450 and 665 are reported for database data files. (I believe your problem lies here)
Check to see if anyone has updated an NTFS filter driver as there can be a problem with alternate filter streams.

Additionally, you can always attempt to create a database snapshot of the database on a less fragmented part of the NTFS and run CHECKDB there to hopefully.

Best Answer

Related Solutions

Restore DB2 Database – Cannot Connect After Offline Backup

Sql-server – DBCC CheckDB run into early hardware failure

Related Question