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
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
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.
instructions below will allow data to be transferred from your local PC to a
remote SQL server.
Open SQL Server Management Studio to connect to the SQL Server Express
Database Engine server and expand Databases.
Right-click the database that has the data that needs transferred, point
to Tasks and then select Import Data
or Export Data.
Choose a Data Source and a Destination.
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.
B. The Select Source Tables and Views
page will allow you to select one or more tables and views to copy.
C. Optionally, you can click the Edit
button to access the Column Mappings screen to change
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.
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
6. Click Finish to run the package.
Click here for more information on SQL Server 2005 Integration