Copy SQL Server Database Schema to Another - March 2011

If you download this tip please consider making a modest donation.

I recently moved an application from one host to another. The application uses seven databases. There was no easy way to do this remotely. One of the databases is very large - about 1/2 gig. I was able to create the databases remotely then I wrote some code to copy the schema from a local machine's databases to the remote SQL server's databases. No data is copied. If you need to copy data see Copy Database with Data Using DAO or ADO the December 2002/July 2003 tips-of-the-month. This code becomes the March 2011 tip of the month. The application is called:

CopyDBsFromServerToServer.

The Application

The web application is written using VB.Net, Asp.Net, VS/2008, using SMO. Application execution screen captures are used to document the process:


Use this page to define and test the SQL Server connections.
Both connections must be successful before the 'Continue' button is enabled.
It may take a few seconds to populate the next page with database information from the SQL servers.


All existing databases, from both servers, are shown on this page.
Select the 'from' database to be used.
The schema from the 'from' database is used as the source.
In this example the 'Intel.qad' database is selected.


Normally the 'to' database will be named the same as the 'from' database.
In this example the 'from' database's name is different than the 'to' database's name.
Select a name from the dropdown list if necessary.
You could use this process to add all the tables from one database to another, existing database.


Click the 'Do It' button to start the synchronization process.
It can take a very long time. Ten minutes or more.


The results. In this example there were no tables changed or columns added because
I had previously run the application using these databases.

How it Works.

  1. Drop all Relationships in the 'to' database for tables that also exist in the 'from' database.
  2. Drop all Indexes in the 'to' database for tables that also exist in the 'from' database.
  3. Add missing tables, with their columns, from the 'from' database to the 'to' database.
  4. Examine all existing tables and add missing columns from the 'from' database to the 'to' database if necessary.
  5. Copy all indexes from the 'from' database to the 'to' database for tables now in both.
  6. Copy all relationships from the 'from' database to the 'to' database for tables now in both.

Notes:

  1. Tables added in step 3 are not examined in step 4.
  2. No data is copied, just the schema.

Download the VB.Net Code

Download the source code by clicking on the zipped file name.

Download - Source Zipped File Size
VS/2008 VB.Net, ASP.Net Solution CopyDBsFromServerToServer.zip 118K

Previous Tip    Tip Index    Next Tip

Home

Updated 2011/04/16