?
Solved

Copying tables from SQL Server 2005 to another machine with SQL Server 2005 Express

Posted on 2010-11-25
27
Medium Priority
?
329 Views
Last Modified: 2012-06-21
How can I copy tables from my laptop that runs Windows XP Pro and SQL Server 2005 to my desktop running Windows XP Home and SQL Server 2005? Both have Visual Studio 2008 Pro. With the regular SQL Server 2005, I have ability to IMPORT/EXPORT through the Management utility, but I do not have this utility with SQL Server 2005 Express.
0
Comment
Question by:dwoolley3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 5
  • 4
  • +2
27 Comments
 
LVL 5

Accepted Solution

by:
logideepak earned 600 total points
ID: 34215463
you can generate the complete script of tables from sql server 2005 and import it into the sql server express.

you can also open the instance of sql server express on sql server 2005 management studio and then you will be able to perform all the activities.
0
 
LVL 79

Assisted Solution

by:arnold
arnold earned 400 total points
ID: 34215466
you could link the two database and then do:
insert into localtable (select * from linked_name.dbo.table)

You can also get the query. Copy and paste the results into excel and save it as a CSV and then use the bulk insert which you are familiar with based on your import/export item.

The problem with this method is the columns have data with commas, or whatever separator you pick instead of the comma separated values.
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 600 total points
ID: 34215479
If you mean to copy the database by restore, you can backup and restore the database from your laptop to desktop.
Or
If you mean just to copy some tables only create script of those tables only and execute it in your desktop sql server.

To generate insert script (copy data as well), try this
http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

Raj
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

Author Comment

by:dwoolley3
ID: 34215498
logideepak, please expand upon the exact menu options and tools I would use in SQL SERVER 2005 Express to import the scripts. I'm not as familiar with the Express version, having been spoiled by the Management Studio of Sql Server 2005. You also mentioned opening an instance of Express on 2005 Management studio. Can you explain this to me?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34215516
Here is a stored procedure to generate insert scripts. But it is using cursors. Also will not mind IDENTITY columns.
create proc generate_inserts @table varchar(20)
--Generate inserts for table @table
AS
declare @cols varchar(1000)
declare @col varchar(50)

set @cols=''

declare colcur
cursor for
select column_name
from information_schema.columns
where table_name=@table 

open colcur

fetch next from colcur into @col

while @@fetch_status=0
begin
select @cols = @cols + ', ' + @col

fetch next from colcur into @col
end

close colcur
deallocate colcur

select @cols = substring(@cols, 3, datalength(@cols))

--select @cols

declare @sql varchar(4000)
declare @colname varchar(100),
@coltype varchar(30)

select @sql = 'select replace(''insert ' + @table + ' (' + @cols + ') '

select @sql = @sql + 'values ('''

declare ccur
cursor for
select column_name, data_type
from information_schema.columns
where table_name=@table

open ccur

fetch from ccur into @colname, @coltype

while @@fetch_status=0
begin
if @coltype in ('varchar', 'char', 'datetime')
select @sql=@sql + ''''''


select @sql=@sql + ' + coalesce(convert(varchar, ' + @colname + '), ''null'') + '

if @coltype in ('varchar', 'char', 'datetime')
select @sql=@sql + ''''''
select @sql = @sql + ''', '''

fetch from ccur into @colname, @coltype
end

close ccur
deallocate ccur

select @sql=substring(@sql, 1, datalength(@sql)-3)

select @sql=@sql + ')'', ''''''null'''''', ''null'') from ' + @table

exec (@sql)

Open in new window

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23133

Raj
0
 

Author Comment

by:dwoolley3
ID: 34215533
Arnold, I do not think I have the ability to link the two databases from the two machines, or do I? If both were connected via an intranet or common server, I suppose I could do that suggestion.

Raj, whichever way gets the job done. I'll look into some of the suggestions.
0
 
LVL 5

Assisted Solution

by:logideepak
logideepak earned 600 total points
ID: 34215544
Open the sql server management studio and
click on connect  -- > Database Engine
select the name of sql server express instance name it should be <machinename>\sqlexpress

This will open the instance of sql server express on the sql server 2005 machine's management studio instance

Then right click on the sql server 2005 database and then tasks --> generate scripts

you can then select the tables for which you want to generate the scripts
 
once the scripts has been generated open a new query window for sql server 2005 express instance which is open in the management studio and then paste the script..

once you execute the script it will create the tables in the selected database
0
 

Author Comment

by:dwoolley3
ID: 34215564
logideepak, Thank you for your thorough explanation, and this is the process that I would use when I have other servers connected/linked to one another, such as at my work place. However, my laptop and my desktop are not connected in this fashion and thus I do not think they could use the same 2005 Management Studio, could they?

Can I generate scripts on the laptop, save them to a thumb drive, transport them to the desktop, and then import/execute the scripts on the desktop? If so, what utility and menu options do I use in SQL Server 2005 Express?
0
 
LVL 5

Assisted Solution

by:logideepak
logideepak earned 600 total points
ID: 34215569
yes you can copy the scripts and execute them on the desktop..

The sql server management studio for 2005 express is freely downloadable and can be downloaded from

http://www.microsoft.com/downloads/en/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 600 total points
ID: 34215578
Try this way that I suggested - http:#34215479

1. Generate the CREATE script of the required tables using 'Generate Scripts' in SQL Server Management Studio.
2. Create the INSERT script (for data)
3. Copy these scripts to Laptop

Then execute

Could you try this ?
Raj
0
 
LVL 8

Assisted Solution

by:Som Tripathi
Som Tripathi earned 200 total points
ID: 34215630
You can do a BCP-Out from Source to a regular file.
Copy the data file to laptop and BCP-In to the table you want.
0
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 600 total points
ID: 34215647
I think Sommer's suggestion seems to be more easy.

For more details about BCP command, please refer this link
http://blogs.techrepublic.com.com/datacenter/?p=319

Raj
0
 

Author Comment

by:dwoolley3
ID: 34218162
I seem to have a bigger issue now... my desktop seems to have crashed and I can't even get into safemode after selecting that option. I'll try out the suggestions after I get the desktop fixed (or buy a new one).
0
 

Author Comment

by:dwoolley3
ID: 34276188
Sorry for the long delay. I now have SQL Server Express 2008 along with VS 2010 running on Windows 7 on my desktop. I do not have the Management Studio with the SSE 2008, though I have it on my laptop with SQL Server 2008. I will look into obtaining the free version of Management Studio for SSE 2008. Perhaps if I have the software then I could pursue the ideas about transferring data.
0
 
LVL 79

Assisted Solution

by:arnold
arnold earned 400 total points
ID: 34278018
You can get SSMSE from the same place where you downloaded the SQL 2008 Express.

You could use the SQL 2008 media to install the SSMS 2008 only or download and install the express version from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b 
0
 

Author Comment

by:dwoolley3
ID: 34288767
I am having difficulties installing SSMSE for SSE 2008. First, I got the SSE 2008 automatically when I installed Visual Studio 2010. Second, I went to the link provided to download and install SSMSE, and it seems I have a 64-bit system, having a new computer with Windows 7. Checking one of the SSE menus leads me to belive it is 64-bit. Thus, I downloaded SQLManagementStudio_x64_ENU.exe and then ran it. I then get a compatibility message: This program has known compatibility issues. I then check for solutions online for MS SQL Server 2008 and it says:

Your version of SQL Server 2008 isn't compatible with this version of Windows.
A newer version of this software is available for download that solves this problem.

Then I clicked the link to download the lastest Service Pack for SSE at http://support.microsoft.com/kb/968382

To obtain Microsoft SQL Server 2008 Express SP2, visit the following Microsoft website:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=40acf55f-0715-4bb0-9735-d02fc2c8a7af&displaylang=en

I then downloaded SP2, it seems, but when I run it (SQLEXPR_x64_ENU.exe), it extracts and then I get a complex menu for SQL Server Installation Center with options for Planning, Installation, Maintenance, Tools, Resources, Advanced, Options.

Am I in the right area or should I be doing something else to get Management Studio?


0
 
LVL 79

Expert Comment

by:arnold
ID: 34292187
My Mistake, referred you to a download for the newer SSMSE versus the one that matches your installed SQL server version.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5d76230d-580d-4874-8c7d-93491a29db15
0
 

Author Comment

by:dwoolley3
ID: 34293165
Arnold, the link you just provided is for SQL Server 2005 express, but I do not have 2005 any more. With my new computer and my new software, I only have SQL Server 2008 Express.
0
 
LVL 79

Expert Comment

by:arnold
ID: 34293854
http://www.symantec.com/connect/articles/install-and-configure-sql-server-2008-express
Ignore the references to deployment or other extraneous items.

The item has a guide with images/illustration that might be helpful in assisting you with the install of the SQL 2008 express and then the addition of the SQL 2008 management studio express.

This might be better/newer http://www.microsoft.com/express/Database/
or better still http://www.microsoft.com/express/Database/InstallOptions.aspx
Download the installer that has both items or the ones that has all the options included.

It all depends on which features you need.  You could of course update what settings you have which might be what that install is offering i.e. you already have sql 2008 express installed, and the one you are running asks whether you are installing a new instance or whether you are updating the existing one and corresponding tools.
0
 

Author Comment

by:dwoolley3
ID: 34397728
Having installed SQL Server 2008 in the past with VS 2010, I notice that I have an option for SQL Server Import and Export Wizard. This is seen an an option when I go to All Programs --> Microsoft SQL Server 2008 --> Import and Export Data (64-bit). It seems similar to the Wizard used in the Management Studio. While I plan to try to work through my difficulties in getting/installing Management Studio for SQL Server 2008 Express, do you think this Import/Export Wizard will suffice in getting my database tables copied?
0
 

Author Comment

by:dwoolley3
ID: 34420748
I was able to install Sql Server 2008 Management Studio. Now I have issues getting past the login for "Connect to Server". When I use Windows Authentication and select (local) as server, I get error message "Cannot connect to (local)". Additional information: A network-related or instance-specific error occurred while establishing a connection to the SQL Server, etc. If I try browsing for servers, I get nothing under "Local Servers" or "Network Servers". Any ideas from here?
0
 
LVL 79

Expert Comment

by:arnold
ID: 34420869
Use the browse option to see the SQL instances you have on your system.
Unfortunately when you use local you are missing the instance reference local\sql2005 local\sql2008.
If they are on the same IP, did you adjust the port on which each will listen? Or you have not configured them for TCP connectivity using the microsft sql server configuration tool shared memory is enabled by default named pipes and TCP have to be enabled.  On a single IP the two sql server instances can not use the same 1433 port one has to be altered.
0
 

Author Comment

by:dwoolley3
ID: 34423058
Although I looked into the items mentioned, I decided to re-install SSE 2008 and carefully select the default options. As a result, I was able to get SSMS 2008 to access a server name of HP-OWNER. It also is able to access (local). Additionally, I created a database by the same name as that which exists on my laptop. Now that all the pieces are in place on my desktop, I can proceed with the original advice given in this post for copying the table structures and contents from my laptop to my desktop, which are not connected to each other.
0
 

Assisted Solution

by:dwoolley3
dwoolley3 earned 0 total points
ID: 34423692
I have successfully generated the Scripts for my dozen tables on the lap top (using SSMS 2005).
In Object Explorer, right click Database, click Tasks, click Generage Scripts, and follow the Wizard. I saved the script to a file called script.sql, which I copied to my thumb drive and then copied it from the thumb drive to my desktop, where I then executed the script within SSMS 2008. The tables are now defined wonderfully with columns, primary keys, and foreign keys, in addition to my views and stored procedures. Through research and experimentation, I found that SSMS 2008 has the ability to not only generate SQL for the structure but also for the data (within the Script Wizard, when Choosing Script Options, change the Script Data option from False to True). Since this excellent option does not exist in SSMS 2005, I need to find another way to copy the table data.

I looked briefly into the Bulk Copy Program (BCP) command-line utility that comes with Microsoft SQL Server. I'll look into some of the other  suggestions mentioned previously. If anyone would like to amplify or reinforce their suggestion for me to COPY the DATA, feel free to do so. (Also, thank you Arnold for sticking with me and helping me as I restored and enhanced my environment).
0
 

Assisted Solution

by:dwoolley3
dwoolley3 earned 0 total points
ID: 34424234
I got all 12 of my tables copied from SQL Server 2005 (on laptop) to SSE 2008 (on desktop).

Having already created the structures of the tables from generated scripts as mentioned previously,
the method I used was to simply cut/paste the records from SQL Server 2005 Management Studio into an Excel spreadsheet and save it with the default xlsx. I copied it onto a thumb drive and placed it into the desktop to be copied to a folder on the desktop. Opening a previously created table in SQL Server 2008 Express Management Studio, and then opening the corresponding Excel document containing rows on that table, I then cut/paste the rows from the Excel doc into the table. Since most of my tables are relatively simple, this worked fine for me. The only adverse effect that I encountered that I did not mind was that the Identity "autogenerated" numbers became values that differed from the original field value.

If I was copying from SSMS 2008 (instead of 2005), I would have had the option to copy both structure AND data. If I had the ability to connect the computers in such a way that Management Studio could access both databases, then I might have been able to import the data using SSE 2008 Management Studio (if the data from 2005 is compatible).

Having tried many options, after restoring my environment and obtaining SSEMS, I think everyone who posted a response has contributed towards my eventual resolution. Maybe I can split the points.
0
 

Author Comment

by:dwoolley3
ID: 34424253
Somehow the system gave credit to the first comment as being the "best solution", though I had chosen my last comment in that place. Thanks again for everyone's help.
0
 

Author Closing Comment

by:dwoolley3
ID: 34440985
While everyone contributed in guiding me to a solution (in using Management Studio on the desktop and in copying to Excel), the specific strategy and need to generate Scripts for the structure and to then copy data to regular Excel files to be cut/pasted into Management Studio was mine. If the "best" solution should be placed somewhere else, I am certainly open to changing it. I also think the Bulk Copy Program (BCP) has merit, and if I was concerned with retaining the exact value of the Identity "auto-generated" number in the new database, I would have pursued the BCP more than I did.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question