Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Restoring SQL2008 Enterprise Edition db into SQL2008 Standard edition question Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 1:10 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:02 PM
Points: 1,289, Visits: 2,842
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
Posted Monday, October 15, 2012 1:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 11:56 AM
Points: 49, Visits: 592
go-ahead..no issue
Post #1372920
Posted Monday, October 15, 2012 1:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:02 PM
Points: 1,289, Visits: 2,842
Thanks, that is what I thought but wanted to throw it out there and see.





Post #1372921
Posted Monday, October 15, 2012 2:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:44 AM
Points: 12,905, Visits: 32,158
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
Posted Monday, October 15, 2012 2:05 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 1:12 PM
Points: 730, Visits: 5,308
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
Posted Monday, October 15, 2012 2:07 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:45 AM
Points: 40,174, Visits: 36,568
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
Posted Tuesday, October 16, 2012 5:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:02 PM
Points: 1,289, Visits: 2,842
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
Posted Tuesday, October 16, 2012 5:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:45 AM
Points: 40,174, Visits: 36,568
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
Posted Tuesday, October 16, 2012 5:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:02 PM
Points: 1,289, Visits: 2,842
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
Posted Wednesday, October 17, 2012 7:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 16, 2014 3:51 PM
Points: 191, Visits: 899
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 »

Add to briefcase 12»»

Permissions Expand / Collapse