Take the 2-minute tour ×
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.

I'm given the task to export the content of a huge table from a production database and to import the data into a database at a remote location.

The Table has about 45,000,000 rows. using about 4 GB space in the database.

Its 10 Columns are of type int, datetime and varchar(n) <= 255, but some of the varchar fields contain newlines and the usual field separators used by bulkcopy.

I guess export to Excel is no option due to the number of rows. Export with SSIS to Flatfile is possible (took about 30 minutes), but reimport is not automatic, since some fields where spitted.

My current idea is

  1. create a new database on the production server (using same collations as the production database)
  2. Set its recovery mode to simple
  3. copy the data with Select * into copy_of_table from prod_db..big_table
  4. Detach mdf file from this new database and ship it to the other location
  5. Drop adhoc database

I assume that inserting into a database with simple recovery model generates only minimal logging on the new database and this way is one of the fastest possible to export the data and the impact to the production database is the least possible.

share|improve this question
2  
I've done that before - caveat: if source db isn't quite the same version (express / standard, patches, etc) they might have issues re-attaching... In my case never an issue as I controlled both sides. –  Martin S. Stoller Jul 24 '11 at 22:16
add comment

2 Answers

Your method will work fine, I would pre-allocate the data file on the new database so it won't need to autogrow. A backup would work just as well as detaching. And the destination server will need the same or higher version of SQL.

share|improve this answer
    
+1 cause great minds think alike :) –  Martin S. Stoller Jul 24 '11 at 22:17
add comment

I'd of thought BCP, with a format file rather than defaults, would be your best bet.

Creating a format file

share|improve this answer
    
I do not see how a format file could help here –  bernd_k Jul 24 '11 at 18:02
    
My understanding from your question was that you were excluding BCP due to having varchar fields that contain standard delimitors? –  Mark Storey-Smith Jul 25 '11 at 14:50
    
The varchar fields can contain any character. How do format files help? –  bernd_k Jul 25 '11 at 14:58
    
By allowing you to define any combination of characters as a delimiter. Something like ||^|| should do it. –  Mark Storey-Smith Jul 25 '11 at 23:22
    
+1 for combination of characters. But you can use them without format files. bcp dbname.dbo.mytable OUT test1.txt -S myserver -U sa -P 1234 -c -r '€\n' -t '€|' -- or bcp "SELECT * FROM dbname.dbo.mytable WHERE xx > 100" queryout bk_test2.txt -S myserver -U sa -P 1234 -c -r '€\n' -t '€|' –  bernd_k Jul 26 '11 at 5:32
add comment

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.