October 19, 2009 at 4:28 pm
Folks,
I am trying to restore a SQL Server 2008 database from an Enterprise Edition Production Server to a Standard Edition UAT Server.
The Enterprise Edition database has row level compression turned on for a few tables and while restoring the database to Standard edition it fails with the following message.
Restoring ODS (database) from:
E:\SQLBackup\abc_20091016_080002.sqb
SQL Server error
SQL error 909: SQL error 909: Database 'abc' cannot be started in this edition of SQL Server because part or all of object 'abc_1245' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage
format are only supported on SQL Server Enterprise Edition.
Processed 16643392 pages for database 'abc', file 'abc' on file 1.
Processed 5 pages for database 'abc', file 'abc_log' on file 1.
SQL Backup exit code: 1100
SQL error code: 909
Updating SQL Server information - Pending
-----------------------------------------
Operation pending.
This is clearly because Standard edition doesn't support data compression. I want to now go ahead and disable the data compression on the Production DB Server (Enterprise Edition), but i have no idea how many tables are being created with DATA_COMPRESSION = ROW property.
Is there any query that would tell me how to find compressed tables?
Thanks in advance!
Amol
Amol Naik
October 19, 2009 at 5:54 pm
I figured out the query myself.
SELECT O.[name],[data_compression_desc], [partition_id]
FROM sys.partitions P (NOLOCK)
INNER JOIN sys.objects O (NOLOCK)
ON O.Object_id = P.object_ID
WHERE data_compression > 0
Amol Naik
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply