Exporting SQL Server 2005 Express Database Data to a Remote SQL Server: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
Exporting SQL Server 2005 Express Database Data to a Remote SQL Server

page 1 of 1

Published: 07 Jun 2006

Unedited - Community Contributed

Abstract
In this article the author describes how to export a SQL Server 2005 Express database data to a remote server running SQL Server 2005.

by Web Team at ORCS Web
Feedback

Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 16684/ 40

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

User Comments

Title: Thanks
Name: Mr Kabindra Prasad Sarma
Date: 2012-03-29 7:07:24 AM
Comment:
Thanks for your effort towards us to bestow knoledge.

Title: This is useful help for import and Export data
Name: Rahul Sinha
Date: 2011-05-06 8:21:45 AM
Comment:
Thanks ..

Title: Thanks
Name: Vishalatchi
Date: 2010-10-15 4:43:19 AM
Comment:
Thanks a lot for your valuable artical, Checking for this option for three days, Once again thanks a lot

Title: Useful option for MSSQLSERVER users
Name: 247
Date: 2010-01-18 1:32:45 AM
Comment:
Useful option . Thanks

Title: Exporting Local SQL DB to Remote DB
Name: Dattatray Dongare
Date: 2009-11-18 6:37:33 AM
Comment:
hi all you are mention above is right but how to select the remote database server

Title: import/export wizard not showing in SSEE
Name: Chandan
Date: 2008-11-30 6:43:13 AM
Comment:
Realizing that SQL Server Management Studio express does not provide you with the import/export options. I have been trying to find the link to download the full version of SQL Server Management Studio or at least the client tools which will provide the options, but can't find them. Can anyone provide a link that I can use to get either of these?

Title: possible for relationship ?
Name: Arpan
Date: 2008-09-29 8:24:11 AM
Comment:
I use your above technique, but i don't get relationship of source database to destination side..

if any solution then plz...

Title: the problem of import and export viewing
Name: Devansh Shah
Date: 2008-09-13 3:03:42 AM
Comment:
Friends the thing is such dat first in server management studio connect to database engine
then Expand DATABASE to System Database and then expand master after that select master and right click ,u will get a right click menu in which there will be option of tasks in dat u will find the option of import or export data

Title: Thanks
Name: SK
Date: 2008-08-05 10:46:35 PM
Comment:
Good for new learner

Title: good article
Name: raja
Date: 2008-06-23 7:14:57 AM
Comment:
i have inatalled sql server 2005 express edition
after installed import data option not showing

shall u give some tips

one question
how to import,export and restore data one database to another database on sql server 2005

pls send any url through this id varatharajan.uchimahali@wipro.com or uvraja@hotmail.com

Title: Re: export option is not available
Name: Desiree
Date: 2008-05-27 2:36:21 PM
Comment:
Hi,

Try this - http://sqlserver-qa.net/blogs/tools/archive/2007/04/23/use-import-export-wizard-with-sqlexpress-edition.aspx.

~Desiree

Title: Mr
Name: Kerryn
Date: 2008-05-26 2:35:15 AM
Comment:
Realizing that SQL Server Management Studio express does not provide you with the import/export options. I have been trying to find the link to download the full version of SQL Server Management Studio or at least the client tools which will provide the options, but can't find them. Can anyone provide a link that I can use to get either of these?

Title: very good
Name: harnish naik
Date: 2008-02-28 7:07:59 AM
Comment:
so nicely descrided...

Title: SQL Sever Import/ Export
Name: Faisal Qureshi
Date: 2008-02-16 3:20:57 PM
Comment:
hi,
its a nice link

Title: SQL IMport/Export
Name: Sunil Gupta
Date: 2008-01-31 5:17:34 AM
Comment:
Hi...
Have A Nice Day & appriatation for good practice on SQL Server.
Plz Help me...I import Database successful but all my views are created as Table on destination server...
How I can get viwes as same on source server..

Title: What does append means ? (Exactly)
Name: Karan
Date: 2007-12-06 8:00:16 AM
Comment:
Hi,
I want to know What does "Append to the existing database sets" Exactly means ?

Thanks in advance
Karan Sharma

Title: Importing some tables from one server to other.
Name: Abhijeet
Date: 2007-09-19 5:56:37 AM
Comment:
Hi,
I want to import some tables from one server to other server. Also this job should run daily so that i will get the updated data. So i have created a SSIS package and scheduled it but it's not working.
Can anyone knows the right way to do it...
Please tell me the steps and options i have to consider...
Thanks in advance..
Abhijeet.

Title: RE: Import Export Wizard is not available
Name: Desiree
Date: 2007-04-20 2:41:09 PM
Comment:
Hi,

If you are running SQL Server Management Studio Express, you will not have the option to Import/Export data. You will need to download the full version of SQL Server 2005 and install the client tools. I hope this helps.

~Desiree

Title: Import Export Wizard is not available
Name: Subhendu
Date: 2007-04-20 6:24:48 AM
Comment:
Dear Frnd..I have right clicked on the database..and choose all task but under that there is no option called import export...so pls suggest me what I have to do to get that option..is anything else needed to down load?

Title: Production server in a different location
Name: Desiree
Date: 2007-04-17 6:56:21 PM
Comment:
Hi Amey,

You would connect with either the SQL Server IP address or the server name but not both. And, it also matters if it’s a default or a named instance (http://support.microsoft.com/kb/313225).

I hope that helps.

~Desiree

Title: Production server in a different location
Name: Amey Bordikar
Date: 2007-04-17 11:20:45 AM
Comment:
Hi Shams,
Nice article. I am currently stuck doing the same. The problem is that the production server is based in a different location, where port 1433 is open, but I am not sure how to connect using an IP address and SQL Server name. I tried, remote_ip, 1433, & it does not work.
Any inputs would be of great help.

Thanks,
- Amey

Title: Re: Export Data
Name: Desiree
Date: 2007-02-14 12:07:31 PM
Comment:
Hi Shams,

You should be able to export your data directly to an Excel spreadsheet. The link below should help. I hope this helps.

http://msdn2.microsoft.com/en-us/library/ms141209.aspx

~Desiree

Title: Export Data
Name: Shams
Date: 2007-02-13 8:23:48 PM
Comment:
Hi All,
Can anyone please tell me how can we use the Export Wizard to export data out of database and store it in some excel file? i am using sql server 2005...I just want to copy my data to excel file.Can i do that?
I tried to do that but not able to export data to excel!

Regards
Shams

Title: Option #2
Name: Desiree
Date: 2006-11-29 5:50:29 PM
Comment:
> From our recent investigations attempting to open a SQL Server 2005
> Express .mdf from SQl Server 2005 Pro will NOT work.
> I would be very interested in getting this to work.
Hi,

It can definitely be done. Hopefully, this link will help - http://msdn2.microsoft.com/en-us/library/ms247257(vs.80).aspx.

Good luck!
~Desiree

Title: RE: Primary/Foreign Key
Name: Desiree
Date: 2006-09-29 8:57:08 AM
Comment:
Hi,

That is one of the downfalls of the Import Export Wizard. The best way to address the column level transformations is to generate a script of the database objects and then run it on the remote server before exporting the data. I hope this helps.

~Desiree

Title: Primary Key's Are not Imported
Name: Sandeep Patil
Date: 2006-09-27 6:37:11 AM
Comment:
Respected Sir/Madam

When we import the data from one database to another
we get only the data and data table are created if it doe's not exists.The Primary or foregin Key what we define won't get imported and also the Stored Procedures.
So how do we do it so if anyone can help me out it would be a great pleasure for me.

if u have a solution you can mail me to:
sandeeppatil13@gmail.com,sandeepp@cislworld.com

Title: RE: error when exporting
Name: Desiree
Date: 2006-06-30 7:26:31 AM
Comment:
Hi Shaz,

I haven’t seen this error before, but a couple things come to mind that you should check.

1. Ensure port 1433 is open. You can test this by opening a command prompt and typing ‘telnet remote_server_ip 1433’ (ensure you replace ‘remote_server_ip’ with the actual server IP address).

2. Ensure remote connections to the server is enabled (This can be done with SQL Server 2005 Surface Area Configuration tool).

3. Ensure you have appropriate permissions to access the database.

I hope this helps.

~Desiree

Title: error when exporting
Name: Shaz
Date: 2006-06-21 8:28:37 AM
Comment:
Hi,

After setting all the variables to import a table into my database, I click on Finish but am faced with an error. Setting source connection fails and I am given the error number 0xc0202009 - could not connect source component.

The server I am importing from is a remote server and I can remote desktop to this without a problem. The server I am importing to is a local server. Someone please help!!!

Title: RE: What about exporting schema updates?
Name: Desiree
Date: 2006-06-15 2:54:27 PM
Comment:
Hi Paul,

In that situation it's probably best to use SSIS versus the Export Import Wizard. The Export Import Wizard has limited capabilities and will not give you the option to update the database schema. You’ll find more information on SSIS here, http://msdn2.microsoft.com/en-us/library/ms141263.aspx.

~Desiree

Title: What about exporting schema updates?
Name: paul
Date: 2006-06-14 4:00:48 AM
Comment:
Any thoughts on how to automatically update the remote database with database schema updates?

Exporting a full database is nice for version 1.0 ... from that point, we have two 'threads'.
1=Users using the application and entering data in the 'production' database.
2= Developers continue working on the software and database, resulting in schema updates.

How to handle these updates?
paul@reddingssloep.nl
Paul

Title: Export Option Not Available
Name: Desiree
Date: 2006-06-12 12:45:02 PM
Comment:
Hi,

If you are running SQL Server Management Studio Express, you will not have the option to Import/Export data. You will need to download the full version of SQL Server 2005 and install the client tools. I hope this helps.

~Desiree

Title: export option is not available
Name: Gail
Date: 2006-06-12 10:22:53 AM
Comment:
I right clicked on the db name and all that came up was shrink db and backup. Nothing about export. Is there some update i need to dload for sql express?