Solved

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

Posted on 2010-11-25
27
318 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
  • 14
  • 5
  • 4
  • +2
27 Comments
 
LVL 5

Accepted Solution

by:
logideepak earned 150 total points
Comment Utility
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 76

Assisted Solution

by:arnold
arnold earned 100 total points
Comment Utility
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 150 total points
Comment Utility
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
 

Author Comment

by:dwoolley3
Comment Utility
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
Comment Utility
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
Comment Utility
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 150 total points
Comment Utility
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
Comment Utility
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 150 total points
Comment Utility
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 150 total points
Comment Utility
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 50 total points
Comment Utility
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 150 total points
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:dwoolley3
Comment Utility
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 76

Assisted Solution

by:arnold
arnold earned 100 total points
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:arnold
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Query Assistance 4 30
SQL Server R2 Stored procedure performance 8 31
sql query 7 34
T-SQL Using IN with a subquery 3 12
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now