View Single Post
  #1 (permalink)  
Old May 23rd, 2007, 10:23 PM
englere englere is offline
Friend of Wrox
Join Date: Jun 2003
Location: Atlanta, Georgia, USA.
Posts: 917
Thanks: 0
Thanked 0 Times in 0 Posts
Default Database Deployment to Shared Servers; SQL Script

Many people use shared-hosting sites and they have great difficulty populating a remote database if they aren’t in the SA role. I will explain the newest solution from Microsoft shortly, but first I want to discuss the problems with the other options. First, VS cannot generate SQL scripts for data. Second, the free Express version of SQL Server Management Studio doesn't have the import feature to suck data into a target DB. Third, even if you have a full version of SQL Server, the SQL Server Management Studio's Import feature has a nasty bug that prevents it from importing the identity values correctly. You can make your own SSIS package to do the job, but it's not nearly as easy as the import feature.

To work-around these problems I wrote a small console program in C# that can connect to the local and remote DB’s and it can copy the records over correctly without corrupting the identities:

Although this program seems to work fine, and many people have used it, it would be far more convenient if we had a tool that could create a huge SQL script that could create the objects AND populate the data tables. There are several commercial tools that can do this, but they’re expensive and they don’t always work as advertised.

Thankfully, Microsoft has now developed a free solution for us that will create a huge SQL script from an existing database! But as you might expect, there are a couple small wrinkles. I’ll explain more in the next message of this thread.