When ASP.NET v2.0, Visual Studio 2005, and Visual Web Developer were released to manufacturing, ORCS Web began supporting those technologies, making them immediately available to our clients.  Visual Web Developer 2005 has a very cool feature that will allow you to dynamically create a SQL Server Express database for use when developing web applications, which is great for developers.  But, after the web application is developed, how is the database schema and data uploaded to your production database on a remote SQL Server?  What a dilemma!  It turns out, though, that there are a few options.

Option #1: Update the web.config of your application to point to your remote SQL Server database and develop using it directly.

Option #2: Send a copy of your .mdf/.ldf files to your web host and have them attached directly to your production SQL Server, replacing your existing database.

Option #3: Develop it on your local machine and then use the Import and Export Wizard of SQL Server Management Studio to copy the data to your remote SQL Server database.

Option #1 is acceptable, but I recommend that you do not use a production database for development purposes.  You should have a separate database for development and production. 

Option #2 will also work, but it does not give the flexibility to manage your data directly.  Option #3 is a good solution for development and also provides the flexibility to export data to a remote SQL server when necessary.  Here I will explain how to use Option #3 to export database data from your local PC to a remote SQL Server database.

Before I begin, it is important to understand that the SQL Server 2005 Import and Export Wizard discussed here only provides minimal transformation capabilities.  It will only allow for setting the name, the data type, and the data type properties of columns in new destination tables and files; SQL Server 2005 Import and Export Wizard does not support any column-level transformations.  This means that any constraints, indexes, triggers, and primary/foreign keys will not be transferred to the remote database.  The best way to handle this situation is to generate a script of the database objects and then run it on the remote server before exporting the data.

Following the instructions below will allow data to be transferred from your local PC to a remote SQL server.

1.      Open SQL Server Management Studio to connect to the SQL Server Express Database Engine server and expand Databases. 

2.      Right-click the database that has the data that needs transferred, point to Tasks and then select Import Data or Export Data.

3.      Choose a Data Source and a Destination.

Figure 1

On the next two screens you will set the options for the type of destination that you selected.  If the destination is a SQL Server database you can specify the following:

A. The Specify Table Copy or Query page will allow you to choose whether to copy data from tables or views or to copy query results.

Figure 2

B. The Select Source Tables and Views page will allow you to select one or more tables and views to copy.

Figure 3

C. Optionally, you can click the Edit button to access the Column Mappings screen to change the following:

·         Indicate whether the destination table is dropped and then recreated, and whether to enable identity inserts.

·         Indicate whether to delete rows or append rows in an existing destination table.  If the table does not exist, the SQL Server Import and Export Wizard will automatically create it.

·         Optionally, you can change the mappings between source and destination columns, or change the metadata of destination columns.  You will have the option to: 

·         Map source columns to different destination columns. 

·         Change the data type in the destination column. 

·         Set the length of columns with character data types. 

·         Set the precision and scale of columns with numeric data types. 

·         Specify whether the column may contain null values. 

Figure 4

5.  On the Save and Execute Package screen ensure Execute Immediately is checked. Optionally, you can save the package on your local SQL Server 2005 installation.

Figure 5 

 

6. Click Finish to run the package.

Click here for more information on SQL Server 2005 Integration Services (SSIS).

 

Resources

SQLAdvice Forums

SQLAdvice Mailing Lists