What is easiest way to transfer tables between MSSQL databases without a network connection?

Question

I have a small app/site on a dev server with a data table in SQL Server [varchar(40),varchar(40),varchar(MAX)]. I want to take the data table and push it to the live server. I have tried exporting in txt, Excel and Access but every time there is an error on import related to not being able to parse due to the data in the varchar(MAX) field or unicode to non-unicode conversion from Access.

I feel like I'm overlooking a good solution here. This doesn't need to be automated at the moment. I believe there are ways to transfer tables between databases connected through a network/internet connection but our live database is not accessible from our office. I've used a data export in MySQL before that creates a script to run on another database to insert all data records, but I don't believe that is available in MSSQL.

Any suggestions?

Answer 1

You could use sql server backup and restore features (details here)

If you want to limit the backup to a single table (because the database is too big for example), You can put your table in its own file group and use the backup feature of sql server on that file group.

If you don't know what a filegroup is, you will find some information here

Answer 2

I would BCP it out in native format and then bcp it in again. This is from memory but the syntax is something like this:

BCP MyTable OUT MyTable.dat -n -Usqlusername -Psqlpassword -Ssqlservername

and then to get it back in again.

BCP MyTable IN MyTable.dat -n -Usqlusername -Psqlpassword -Ssqlservername

Answer 3

I've used a data export in MySQL before that creates a script to run on another database to insert all data records, but I don't believe that is available in MSSQL.

Aha! You can do this in Microsoft SQL, basically. A little-known Microsoft utility, the "SQL Server Database Publishing Wizard," exists and does what you're talking about.

http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Why this isn't a standard part of SQL Server, I have no idea.

Answer 4

You could use a tool like EMS. It will generate for you a SQL script, including database creation and, if requested, data inserts.

Update the database name if needed, save the script, take it to you new server, and execute it. You're done.