Partitioning and restoring to Standard Edition

Question

Partitioning and restoring to Standard Edition

JeepHound

SSCrazy

Points: 2963
More actions
October 31, 2014 at 8:51 am

#298012

I am restoring a 3rd party Vendor database from SQL2008R2 Enterprise to SQL2008R2 Standard. I get an error that I can't restore because there is a partition function. When I look at the dependencies it shows a table. When I look at the properties of that table it shows that table is not partitioned. Knowing that partitioning is not supported in Standard and it looks like partitioning is disabled on this table. Could I safely drop the partition function and scheme?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

JeepHound (10/31/2014)
I am restoring a 3rd party Vendor database from SQL2008R2 Enterprise to SQL2008R2 Standard. I get an error that I can't restore because there is a partition function. When I look at the dependencies it shows a table. When I look at the properties of that table it shows that table is not partitioned. Knowing that partitioning is not supported in Standard and it looks like partitioning is disabled on this table. Could I safely drop the partition function and scheme?

Curious.... if you can restore it, how are you able to drop the partition function and scheme?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

JeepHound (10/31/2014)
I am restoring a 3rd party Vendor database from SQL2008R2 Enterprise to SQL2008R2 Standard. I get an error that I can't restore because there is a partition function. When I look at the dependencies it shows a table. When I look at the properties of that table it shows that table is not partitioned. Knowing that partitioning is not supported in Standard and it looks like partitioning is disabled on this table. Could I safely drop the partition function and scheme?

The partition function(s) and scheme(s) need to be dropped on the originating DB then the backup redone before you can restore the database to a SQL Server 2008 Standard Edition installation. The other choice, if that can't be done on the source system, is to restore the database to Enterprise Edition instance under a different name, drop the partition function(s) and scheme(s), and then do a backup of that database.

JeepHound SSCrazy Points: 2963 More actions · Answer 3

That is what I was thinking, it seems easy enough. But is there any other reason why removing the function and Scheme would cause problems. This database was originally on a standard edition and upgraded less than a year ago to Enterprise.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

If the partition function(s) and scheme(s) aren't being used, then there is no reason they couldn't be removed.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Also, this may be useful:

http://www.mssqltips.com/sqlservertip/2108/identify-database-features-restricted-to-a-specific-edition-of-sql-server-2008/

JeepHound SSCrazy Points: 2963 More actions · Answer 6

That is a nice DMV I had not idea that existed. Forgive my ignorance, partitioning is a mystery to me. Is there any other way besides dependencies that I can verify if this partition scheme and function are not in use?

JeepHound SSCrazy Points: 2963 More actions · Answer 7

So I restored to a test enterprise instance and I cannot drop either because they are dependent on a table. THe table does not seem to have partitioning enabled in the properties. Any Idea where I am going wrong here?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

DEfinition of the table and all indexes?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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