Exporting SSE Database Data to a Remote SQL Server
By Desiree Harris
April 15, 2006
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? Dilemma, dilemma. It turns out 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 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 doesn’t 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
doesn’t 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:
- 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 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.
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.
6. Click Finish to run the package.
Click here for more information on SQL Server 2005 Integration Services (SSIS)
Desiree Harris is a Lead Technology
Specialist with
ORCS Web, Inc.
- a company that provides managed hosting solutions for clients who develop and deploy their applications on Microsoft Windows platforms. Services include shared hosting, dedicated hosting, and webfarm hosting, with specialty in .Net, SQL Server, and architecting highly scalable solutions.