Restoring a database from SQL Server Enterprise Edition to any other edition of SQL Server June 20, 2012Posted by fofo in Sql Server, SQL Server 2008.
In this post I would like to demonstrate with a hands-on example that is not always possible to restore a database to any Edition of SQL Server.We assume of course that the version of SQL Server remains the same. In my machine I have various instances of SQL Server running.I have an instance of SQL Server 2008 R2 Developer Edition and an instance of SQL Server 2008 R2 Standard edition.
- If I have a database running on a (SQL Server 2008 R2 Developer Edition-Enterprise Edition) that has no enterprise features and take a backup of that database, I can restore it to a SQL Server 2008 R2 Standard edition istance.
- If I have a database that has enterprise features (data compression,transparent data encryption,partitioning,change data capture) and try to restore it to an SQL Server 2008 Standard Edition instance I will fail.
Let’s move on to our actual demonstration.In this example I will create a database and then create a table. I will insert some rows into the table. I will then compress the rows in the table.Data compression is an Enterprise feature only.Finally I will take a full database backup.
I will connect to this instance of SQL Server 2008 R2 Developer edition and in a new query window I will type
USE master; GO IF DATABASEPROPERTYEX ('MyDB', 'Version') > 0 DROP DATABASE MyDB; GO CREATE DATABASE MyDB ON (NAME = MyDB_Data, FILENAME = N'C:\SqlData\MyDB_data.mdf') LOG ON (NAME = MyDB_Log, FILENAME = N'C:\SqlData\MyDB_log.ldf'); GO USE MyDB; GO CREATE TABLE MyCompressionTable (ID INT, phrase1 CHAR(100),phrase2 CHAR(100)) GO INSERT INTO MyCompressionTable VALUES (1, 'SQL Server is the best RDBMS','I love T-SQL') GO 15000 -- DATA_COMPRESSION = ROW ALTER TABLE MyCompressionTable REBUILD WITH (DATA_COMPRESSION = ROW); GO BACKUP DATABASE MyDB TO DISK = N'C:\SqlData\MyDB.bak' WITH INIT; GO
I am going to connect to the SQL Server 2008 R2 Standard Edition running in my machine.I will connect to this instance and try to restore the backup I have just taken.
USE master; GO RESTORE DATABASE MyDB2 FROM DISK = N'C:\SqlData\MyDB.bak' WITH MOVE N'MyDB_Data' TO N'C:\SqlData\MyDB_data2.mdf', MOVE N'MyDB_Log' TO N'C:\SqlData\MyDB_log2.ldf', REPLACE, STATS = 10; GO
I will receive an error after executing the query above. You can see below (a part) the error message
RESTORE could not start database ‘MyDB2’.
RESTORE DATABASE is terminating abnormally.
Database ‘MyDB2’ cannot be started in this edition of SQL Server because part or all of object ‘compressed’ is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Database ‘MYyDB2’ cannot be started because some of the database functionality is not available in the current edition of SQL Server.
To recap,if you’re not using the persistent Enterprise-only features (Partitioning, Data Compression, TDE,Change Data Capture), an Enterprise edition database can even be restored event to SQL Server Express edition, if of course is small enough.
I think DBAs should be very careful.It happens quite often (because of the cost) to buy licenses of Standard SQL Server.A DBA can have this instance as part of his disaster recovery strategy.When the disaster happens and then tries to restore the backup to the SQL Server Standard Edition instance, that will fail if the database has enterprise features.
There is a dynamic management view in SQL Server 2008 that can help us identify if a database has Enterprise features. This is the sys.dm_db_persisted_sku_features dynamic management view.
In any query window you can type the following query.
SELECT * FROM sys.dm_db_persisted_sku_features;
In my example the output was
Hope it helps!!!