2

I have a little problem. I'm using a SQL Server database version 11. I have to change database edition from enterprise to standard.

I overlooked fact that standard edition doesn't have partitioned function. But I moved all .mdf files to new server. It's about 200Gb of data.

I want to ask you question, if it's possible to attach database without partitioned tables. That tables i can moved over internet. It's about 40Gb, it's ok to moved that file over internet.

Thanks for answer.

Improve this question
1
  • You moved the MDF's right? Not backup-based. With backups you probably could have shipped a few tlogs over to efficiently ship incremental updates (such as removing partitioning).
    – usr
    Commented Mar 12, 2015 at 13:07

3 Answers 3

Reset to default
0

This is an older question. But Microsoft just gave you a great new answer if you are using SQL Server 2016 and still in Standard.

Partitioning (among many other features like Compression, Columnstore, In-Memory OLTP, Row Level Security, etc.) is available in SQL Server standard.

Probably too late for this question. But not too late for someone finding this question today. SQL Server 2016 Service Pack 1 is where this change was introduced.

You can read more on Microsoft's post about it.

Improve this answer
1

You do have at least a couple of options:

  1. On the source server, create a new empty database. Size it and set to Simple Recovery model. Create an un-partitioned copy of your table into this database using SELECT ... INTO yourNewDb.dbo.yourTableName FROM yourOldDb.dbo.yourTableName. Backup this database using backup compression (which does work with Standard Edition). Move this nice compact backup to your new server, restore and copy the data into the required database using INSERT. This option is similar to @spaghettidba's suggestion above but does not involve removing partitioning from your current live table.
  2. or use bcp (with the out option) to create file copies of the table using the -n (native) format. You could even consider using the queryout option of bcp to create a file per partition. This will create smaller files which may be easier to move. These actually zip up quite nicely and will be easy to move. Create an empty non-partitioned table on the target and use bcp in or BULK INSERT. Additionally use the TABLOCK option to attempt a minimally logged operation. More details here.
  3. or if the two servers can 'see' each other, use SSIS to transfer the data between the two. Create an empty non-partitioned table on the target. Use a Data Flow Task to copy the data.

All of these approaches allow you to leave in place the current partitioning scheme. Work out which one is best for you.

Improve this answer
3
  • Ok. I know that i have a options to do the move of database. But our servers are in another town. So I need to travel to that city and move data physicaly. That consume me all day. I want to ask you, if someone was dealing with same problem and if is there any solution which doesn't include physical travel to servers.
    – JAttanonRadar
    Commented Mar 12, 2015 at 17:29
  • How about secure FTP site to transfer the bcp files? Or, now you've done the move, temporarily install Developer edition on the target, restore and do the downgrade, then remove it?
    – wBob
    Commented Mar 12, 2015 at 17:41
  • It crossed my mind. If there is no other way i contact a administrator of the target and waiting if he can install developer edition temporalily, so i can remove that partitions and attach to standard edition database.
    – JAttanonRadar
    Commented Mar 12, 2015 at 18:16
1

You cannot restore a database that contains partitioned tables in Standard Edition. You also cannot attach a database without supplying all the data files. You could restore individual files or filegroups, but it won't achieve what you're after.

Your only option is to get rid of partitioning before you take a backup and move it to Standard Edition.

BTW, if you query sys.dm_db_persisted_sku_features you'll get a list of all the active features in the database that might impact your migration.

EDIT 2017-09-15: not true any more since SQL Server 2016 SP1.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.