Migrate SQL Server Express tables to SQL Server database

Question

I have a local SQL Server Express database that I want to copy to a SQL Server database on a server. I do not have full permissions to the server, so I cannot directly attach it. Somebody told me I could do this by using the SqlBulkCopy^[MSDN] class.

I can't figure out how to use it. I haven't done enough database programming to be familiar with how this sort of thing is done.

Basically, I want to copy all the tables in one database to another. There are relationships between the tables, I don't know if that will make a difference in the copying or not. There aren't very many, so it wouldn't be a big deal to just copy the data and then manually reassign the relationships.

This is as far as I could go

var localDatabase = new SqlConnection("connection string");
var serverDatabase = new SqlConnection("other connection string");
SomehowCopyAllTables(localDatabase, serverDatabase);

but unfortunately, Mr. C# didn't want to acknowledge the existence of the SomehowCopyAllTables method.

How can I copy all the tables from one database to another?

@p.campbell Thanks for checking up on me! I actually finally did yesterday. I got the scripts to copy the tables, but I had to export data to a CSV and then import the data from the CSV into the database on the server. — Peter Olson
– Peter Olson, Commented Sep 18, 2011 at 15:00

p.campbell · Accepted Answer · 2011-09-09 17:18:12Z

4

Use SQL Server's Generate Scripts command within SQL Server Management Studio.

right click on the database; Tasks -> Generate Scripts
select your tables, click Next
click the Advanced button
find Types of data to script - make the choice as you like: include data or not.
you can then choose to save to file, or put in new query window.
results in INSERT statements for all table data selected in bullet 2.
copy/paste that output in the new query window onto your target server.

SQL Server Generate Scripts Data

answered Sep 9, 2011 at 17:18

p.campbell

101k70 gold badges265 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TonySalimi · Accepted Answer · 2011-09-09 17:10:27Z

2

From my point of view, the best way is to create SQL scripts in your SQL Express and execute the generated scripts on your server. The whole data will be copied.

answered Sep 9, 2011 at 17:10

TonySalimi

8,5034 gold badges39 silver badges69 bronze badges

2 Comments

Peter Olson Over a year ago

Sure, but how would I do that?

Paige Cook Over a year ago

You can generate scripts by loading up Sql Server Management Studio open your Express instance in the Object Explorer, select your database, right click and choose Tasks->Generate Scripts... Please see msdn.microsoft.com/en-us/library/ms178078.aspx for more details.

JohnD · Accepted Answer · 2011-09-09 17:21:48Z

2

To generate database scripts from Sql Server Management Studio:

Right click the database
Select Tasks -> Generate Scripts
(Click next if you get the intro screen)
Select "Select specific database objects"
Pick the objects to generate scripts for (tables, stored procedures, etc...)
Click Next, then specify the output filename
Click Finish to generate the script

This will generate the schemas only. If you want to do data generating scripts as well, in step 6) click the Advanced button and scroll down to the "Types of data to script" and change it from "Schema only" to "Data only" or "Schema and data"

answered Sep 9, 2011 at 17:21

JohnD

14.9k4 gold badges42 silver badges54 bronze badges

Comments

Justin Dearing · Accepted Answer · 2011-09-09 21:10:04Z

0

It might be easier to simply use Atlantis Interactive's Schema Inspector and Data Inspector rather than program it yourself. Simply compare the schema to a blank database on your current system.

answered Sep 9, 2011 at 21:10

Justin Dearing

15.1k24 gold badges94 silver badges166 bronze badges

Collectives™ on Stack Overflow

Migrate SQL Server Express tables to SQL Server database

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related