7

I have been working on thinking of ways to move a 40gb table from one SQL Server box to another SQL Server box. They are at separate companies without an intranet linkage and obviously without linked servers.

Initially, I thought the quickest way to do this was to pull the nightly backup file and get this over to the other server. Create a database from the backup and then transfer my table from that database to the existing.

After thinking about this though, I can't have all the data in that particular database at the other company due to security reasons. Whereas the table I am after is computational and unrelated to any type of company specific competitive edge.

Either way, what I am looking for is how I can export a table of that size quickly and efficiently and get it into a database that is in no way connected to the existing database.

This is my first time researching something along this line, and I am pretty sure I don't want to script the table. Initially, I thought the bak file was my best bet, but can't do the entirety of the db.

Improve this question
1

2 Answers 2

Reset to default
9

A couple of feasible options, in order of my preference:

Option 1

  1. Create a new, empty database locally - maybe make the log file large enough, at least temporarily, to accommodate the entire set of data you are moving without growth
  2. Use SELECT INTO, the Import/Export "Wizard", or Red Gate SQL Data Compare to copy this table to the new database (note that if you use SELECT INTO you'll have to create any indexes, constraints etc. manually)
  3. Back up the new database, and send that .bak over (note that the log may be bigger than you expect due to the initial data movement)

Option 2

  1. Back up your database locally
  2. Restore as a copy
  3. Drop everything but this one table
  4. Back up the copy, and send that .bak over
Improve this answer
4
  • Aaron as always your answers are as good as gold
    – User Smith
    Commented Apr 14, 2015 at 21:37
  • Why not set the new, empty database to Simple recovery model, to avoid log size problems? It's a throwaway database by definition, so you don't care if it dies in the middle of the operation -- you just start over with a new one.
    – Ross Presser
    Commented Apr 22, 2015 at 14:20
  • @RossPresser Simple recovery model doesn't mean "don't log" - I can very easily blow up a log for an empty database even in simple recovery as I am populating it.
    – Aaron Bertrand
    Commented Apr 22, 2015 at 14:26
  • Oops, you are of course correct. I read "No log backups" and thought "the log is never written." Obviously that's wrong.
    – Ross Presser
    Commented Apr 22, 2015 at 14:28
2

As kin mentioned above. The easist way seems to be bcp out and in.

Script out your table to a file ( The create table script )

Run the following command

Export Data at command line using

bcp "Database.Schema.Table" out "D:\filename.dat" -N -S "SQLServer\Instance" -T

Once you copy that file / move to other system create the table you need then do the bcp in.

bcp "Database.Schema.Table" in "D:\filename.dat" -N -S "SQLServer\Instance" -T

Link: https://msdn.microsoft.com/en-us/library/ms162802.aspx

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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