Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a database with several partitioned tables on MS SQL Server 2005 Enterprise, and I have to restore this DB on MS SQL Server 2012 Standard. Since Standard doesn't support partitioning - it's a bit complicated to restore all the data to the new server. I have to find a simplest and quickest solution.
My first idea is to remove partitions on 2005 DB, then make a backup and restore it on SQL Server 2012. But my database is large and to merge it's partitioned data takes couple of hours for just one table.
The quickest way, from my point of view, would be the data transfer from old to new DB, but it will probably also take too much time.
Please share your ideas - may be I'm not aware of some features in 2012 that might be helpful.

share|improve this question
1  
check my answer at dba.stackexchange.com/a/43232/8783. You can use BCP out and Bulk Insert using native mode which will be a lot faster. – Kin Jul 25 at 12:56
1  
It isn't possible to restore a database that uses Enterprise edition features on an instance that doesn't support those features. You'll have to either remove partitioning and then use backup/restore, or export/import the data. – Jon Seigel Jul 25 at 13:21
Thank you for your answers. #Kin, I'll definitely try out your solution - it seems like the quickest solution yet. – hotfusion Jul 25 at 16:36

1 Answer

up vote 1 down vote accepted

Community Wiki answer compiled from comments to the question†

Jon Seigel:

It isn't possible to restore a database that uses Enterprise edition features on an instance that doesn't support those features. You'll have to either remove partitioning and then use backup/restore, or export/import the data.

Kin

See this answer on another question that shows how to use bcp out and BULK INSERT in native mode to transfer the data quickly.

†Comments may be deleted and are not searchable.

share|improve this answer
BCP OUT and BULK INSERT are actually working faster then any of the insert-select queries on the same (!) database (on tables with more than 20 mln. rows). And I've tried different loops with different amount of rows inserted at once. So with bulk insert - it's MUCH more faster than anything else and it saved me really much time. Thank you, Kin, very much for the idea. – hotfusion Jul 31 at 20:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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