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.
Add a comment
|
1 Answer
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 outandBULK INSERTin 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. Commented Jul 31, 2013 at 20:06