CODEWITHSUNDEEP
c#sqlsql-serversql-server-express
Peter Olson
Peter Olson

Reputation: 143037

Migrate SQL Server Express tables to SQL Server database

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?

Upvotes: 2

Views: 538

Answers (4)

Justin Dearing
Justin Dearing

Reputation: 14978

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.

Upvotes: 0

JohnD
JohnD

Reputation: 14787

To generate database scripts from Sql Server Management Studio:

  1. Right click the database
  2. Select Tasks -> Generate Scripts
  3. (Click next if you get the intro screen)
  4. Select "Select specific database objects"
  5. Pick the objects to generate scripts for (tables, stored procedures, etc...)
  6. Click Next, then specify the output filename
  7. 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"

Upvotes: 2

p.campbell
p.campbell

Reputation: 100657

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

Upvotes: 4

TonySalimi
TonySalimi

Reputation: 8447

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.

Upvotes: 2

Related Questions