Restoring SQL2008 Enterprise Edition db into SQL2008 Standard edition question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Restoring SQL2008 Enterprise Edition db into...

12 posts, Page 1 of 2

1 2 »»

Restoring SQL2008 Enterprise Edition db into SQL2008 Standard edition question

Rate Topic

Display Mode

Topic Options

Author

Message

Markus

Posted Monday, October 15, 2012 1:10 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 1,048, Visits: 2,215

A vendor of ours is going to give us a database that is in SQL2008 Enterprise Edition for us to restore into our environment. However, we do NOT have Enterprise Edition. If they backup the database without compression I should be able to restore it into SQL2008 Standard Edition shouldn't I?

We are not going to use this database, we will take some of the information out of it and it will go into a newly created database. We only want the table structure and data.

In SQL2008 I know backup compression is only available in Enterprise Edition so I specifically told them to NOT backup the database with Compression.

Post #1372914

bangsql

Posted Monday, October 15, 2012 1:21 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 27, Visits: 368

go-ahead..no issue

Post #1372920

Markus

Posted Monday, October 15, 2012 1:22 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 1,048, Visits: 2,215

Thanks, that is what I thought but wanted to throw it out there and see.

Smile

Post #1372921

Lowell

Posted Monday, October 15, 2012 2:01 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

you might want to buy a copy of developer so you can restore and test teh client backup, even if they are using enterprise only stuff like compression;

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1372925

jasona.work

Posted Monday, October 15, 2012 2:05 PM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 469, Visits: 2,358

Actually, even if they back it up with compression, you can restore it. I tested this recently here at my work, as we have clients who (tightwads) use SQL Express.

BUT one thing to be wary of, is to make sure the Vendor isn't using Enterprise only features in their DB. Things like partitioned tables comes to mind.

Jason

Post #1372928

GilaMonster

Posted Monday, October 15, 2012 2:07 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

It will work, providing the database contains no persistent enterprise-only features (partitioning, compression, TDE). If the DB uses any of those, the restore will fail *at the end*

Backup compression isn't a problem, while only Enterprise/Developer edition can create compressed backups, any edition can read compressed backups and restore the DB.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1372929

Markus

Posted Tuesday, October 16, 2012 5:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 1,048, Visits: 2,215

Thanks everyone. I will double check with the vendor about the Enterprise Edition special features.

If, any of those are in there can I restore this into SQL2008R2 Standard Edition? I can't remember if Partitioning is in SQL2008R2 Standard.

Post #1373158

GilaMonster

Posted Tuesday, October 16, 2012 5:51 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

Markus (10/16/2012)

If, any of those are in there can I restore this into SQL2008R2 Standard Edition?

No, the restore will fail.

The DMV to check is sys.dm_db_persistent_sku_features (or something like that)

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1373172

Markus

Posted Tuesday, October 16, 2012 5:54 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 2:16 PM
Points: 1,048, Visits: 2,215

GilaMonster (10/16/2012)

Markus (10/16/2012)

If, any of those are in there can I restore this into SQL2008R2 Standard Edition?

No, the restore will fail.

The DMV to check is sys.dm_db_persistent_sku_features (or something like that)

Thanks,.... I was afraid of that...

I have fired off an email asking them to verify that they are not using any of those features. I guess I should have done a lookup on the MSFT website for editions comparision before I posted as all that information was there. The database is 270gig so there is a possibility that they are using partitioning.

Post #1373175

vikingDBA

Posted Wednesday, October 17, 2012 7:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:19 AM
Points: 189, Visits: 863

Here is the DMV mentioned:

/*can identify all Enterprise-edition-specific features that are enabled within a user database by
using the sys.dm_db_persisted_sku_features Dynamic Management View */
--SQL Server 2008 only
USE <DBNAME>
GO

SELECT * FROM sys.dm_db_persisted_sku_features

I have this as 2008 only. I assume it will also work for 2008 R2. Maybe it was talking about the previous versions (2005 and before).

Post #1373783

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions