jump to navigation

Restoring a database from SQL Server Enterprise Edition to any other edition of SQL Server June 20, 2012

Posted by fofo in Sql Server, SQL Server 2008.
trackback

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

Compression   100

Hope it helps!!!

About these ads

Comments»

1. Dot Net Rules : Restoring a database from SQL Server Enterprise Edition to any other edition of SQL Server - June 20, 2012

[...] of SQL Server 2008 R2 Developer Edition and an instance of SQL Server 2008 R2 Standard edition.(read more) Share Posted: Τετάρτη, 20 Ιουνίου 2012 10:11 μμ από το μέλος [...]

2. datzme - March 9, 2014

Super share, will share this with others as well :)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,793 other followers

%d bloggers like this: