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?

enter image description here

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.

  • Okay, just as I posted this, the Pre-executing phase succeded and the copying phase started to copy the data. Huuuhhh, I almost stoped it. But it would be nice if there was an explanation on why it took 3 hours for wizard to start exporting? – dmh Nov 11 '15 at 11:40

From your issue, I can see that your choice of tool is a problem.

Below are 2 options that you can use :

  1. 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).

  2. 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.

| improve this answer | |
  • Hi @Kin, thanks for the advice. I'm really not such an expert which would require more "step-by-step" how to perform the options you are talking about. – dmh Nov 12 '15 at 7:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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