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.

  • 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 '13 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 '13 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 '13 at 16:36
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.

  • 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 '13 at 20:06

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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