April 3, 2015 at 3:08 pm
Hi,
I have an Enterprise edition of SQL2014 that is production. I try and restore my bak file to a standard edition and it won't let me. I researched it and it won't restore due to the use of 'partitioning'.
So I then run this script:
select object_schema_name(i.object_id) as [schema],
object_name(i.object_id) as [object],
i.name as [index],
s.name as [partition_scheme]
from sys.indexes i
join sys.partition_schemes s on i.data_space_id = s.data_space_id
That returns:
schemaobjectindexpartition_scheme
dboclaim_procedure_dta_index_claim_procedure_8_417850859_96917568_K1_K12_K3_K11_K93_K4_dta_ps__8059
dboclaim_procedure_dta_index_claim_procedure_8_417850859_96920536_K12_K1_K3_K20_dta_ps__9494
dboclaim_procedure_dta_index_claim_procedure_8_417850859_96930392_K3_K11_K4_K1_K12_dta_ps__2608
dboclaim_procedure_dta_index_claim_procedure_8_417850859_96918128_K4_K11_K3_K1_dta_ps__170
dboclaim_procedure_dta_index_claim_procedure_8_417850859_96912248_K1_K11_K4_K3_dta_ps__1912
dboclaim_procedure_dta_index_claim_procedure_8_417850859_96917736_K4_K11_1_3_78_dta_ps__4364
dboclaim_procedure_dta_index_claim_procedure_8_417850859_96917736_K11_K4_K3_K1_K12_K20_dta_ps__4364
I am thinking that my bosses will not want to pony up the dollars to purchase SQL enterprise - so I think I have to remove the partitioning in order to restore to standard?
How do I know that the partitioning is being used? (Based on above I can assume it is?)
I see that the 'partioning results' above are actually statistics on the claim_procedure table?!? Can I just right click on the table under statistics and remove the statistics, backup and then restore on standard edition?
April 3, 2015 at 3:47 pm
Don't remove the partitioning form the Production server. My recommendation (there may be better options one of the more experienced DBA's knows about), but I would restore the database to the production server, use a script to remove all partitioning, and then backup that DB and restore it.
If this is going to have to be done weekly or periodically, you can script this out for you into a stored procedure and then schedule it.
April 4, 2015 at 3:09 am
If the partitioning is working for your in production and you're actually using it, removing it doesn't make a lot of sense. If this other server is not a production server, you should be able to install the Developers edition of SQL Server which has all the functionality of Enterprise. Then the restore will work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2015 at 6:30 am
Quick note, use the sys.dm_db_persisted_sku_features to identify Enterprise features in use
😎
SELECT
PSF.feature_name
,PSF.feature_id
FROM sys.dm_db_persisted_sku_features PSF;
April 4, 2015 at 11:01 am
Hold on a minute folks. Restore to where? A Dev environment or what? I ask because it can make all the difference in the world for licensing and that's a large part of what Grant was driving at.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2015 at 6:19 pm
Grant Fritchey (4/4/2015)
If the partitioning is working for your in production and you're actually using it, removing it doesn't make a lot of sense. If this other server is not a production server, you should be able to install the Developers edition of SQL Server which has all the functionality of Enterprise. Then the restore will work.
Okay, so I have the standard edition install on the development server. Can I just install the dev version 'over' the standard edition? Or do I have to rebuild everything?
April 4, 2015 at 8:09 pm
krypto69 (4/4/2015)
Grant Fritchey (4/4/2015)
If the partitioning is working for your in production and you're actually using it, removing it doesn't make a lot of sense. If this other server is not a production server, you should be able to install the Developers edition of SQL Server which has all the functionality of Enterprise. Then the restore will work.Okay, so I have the standard edition install on the development server. Can I just install the dev version 'over' the standard edition? Or do I have to rebuild everything?
You DO need to follow the licensing for it. While it's a whole lot less expensive than the Enterprise Edition, you do need to follow the rules which are that each developer needs a legal license. It's only about $35 a seat.
I don't know if you can do an inplace upgrade over the standard edition or not.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2015 at 4:54 am
Jeff Moden (4/4/2015)
krypto69 (4/4/2015)
Grant Fritchey (4/4/2015)
If the partitioning is working for your in production and you're actually using it, removing it doesn't make a lot of sense. If this other server is not a production server, you should be able to install the Developers edition of SQL Server which has all the functionality of Enterprise. Then the restore will work.Okay, so I have the standard edition install on the development server. Can I just install the dev version 'over' the standard edition? Or do I have to rebuild everything?
You DO need to follow the licensing for it. While it's a whole lot less expensive than the Enterprise Edition, you do need to follow the rules which are that each developer needs a legal license. It's only about $35 a seat.
I don't know if you can do an inplace upgrade over the standard edition or not.
I'm pretty sure you can. I haven't tested it in a while (years), but I'm pretty sure it works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2015 at 2:17 pm
Grant Fritchey (4/5/2015)
Jeff Moden (4/4/2015)
krypto69 (4/4/2015)
Grant Fritchey (4/4/2015)
If the partitioning is working for your in production and you're actually using it, removing it doesn't make a lot of sense. If this other server is not a production server, you should be able to install the Developers edition of SQL Server which has all the functionality of Enterprise. Then the restore will work.Okay, so I have the standard edition install on the development server. Can I just install the dev version 'over' the standard edition? Or do I have to rebuild everything?
You DO need to follow the licensing for it. While it's a whole lot less expensive than the Enterprise Edition, you do need to follow the rules which are that each developer needs a legal license. It's only about $35 a seat.
I don't know if you can do an inplace upgrade over the standard edition or not.
I'm pretty sure you can. I haven't tested it in a while (years), but I'm pretty sure it works.
I've seen different threads regarding this. Doing the edition upgrade might work.
April 6, 2015 at 2:24 pm
JoshDBGuy (4/6/2015)
Grant Fritchey (4/5/2015)
Jeff Moden (4/4/2015)
krypto69 (4/4/2015)
Grant Fritchey (4/4/2015)
If the partitioning is working for your in production and you're actually using it, removing it doesn't make a lot of sense. If this other server is not a production server, you should be able to install the Developers edition of SQL Server which has all the functionality of Enterprise. Then the restore will work.Okay, so I have the standard edition install on the development server. Can I just install the dev version 'over' the standard edition? Or do I have to rebuild everything?
You DO need to follow the licensing for it. While it's a whole lot less expensive than the Enterprise Edition, you do need to follow the rules which are that each developer needs a legal license. It's only about $35 a seat.
I don't know if you can do an inplace upgrade over the standard edition or not.
I'm pretty sure you can. I haven't tested it in a while (years), but I'm pretty sure it works.
I've seen different threads regarding this. Doing the edition upgrade might work.
Quit certain that the path from Standard to Enterprise/Evaluation/Developers is not supported.
😎
April 6, 2015 at 4:32 pm
Thanks everyone!
We are purchasing the dev version.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply