I'm using the Import/Export Wizard in SSMS 2012 to store some data into a flat file, as I have to move these data from one SQL server to another. I've tried with direct connecting to the server through the export wizard but it fails after som time when the export begins due to some connection-timeout, therefor I stick to the flat-file method. Even if the direct-connection method is really nice.
I've been using the flat-file method with an row-count of ~92+ million rows, but now I'm reaching 1.1 billion (calculated) and the Executing and Pre-Executing phase in the Export wizard doesn't seem to cooperate at all. I've started to export 3 hours ago, and it's still looking as you see on the picture below. Any Idea?
I have seen similar waiting-time for the Pre-execute and executing phase to get done, where it started after 10-20 minutes, and the rows started to count in the pre-execute phase, but 3 hours, I've never tried that before.
Best Answer
From your issue, I can see that your choice of tool is a problem.
Below are 2 options that you can use :
Backup on the source server with COMPRESSION and Restore it on the destination server. Make sure you have Instant file initialization enabled. Once Restore is done, you can delete unwanted objects (if you want).
Use BCP OUT in native mode on source server, copy the file to destination (you may zip the file as well) and BULK INSERT into destination table. Make sure to create the table on destination database. I have written this script that will help you.