473,246 Members | 1,667 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 software developers and data experts.

Copy databases from SQL 2005 server to SQL Express

Two Windows 2003 server,
one with SQL 2005 server,
another with SQL Express.

Is it possible to copy databases from SQL 2005 to SQL Express?

Thanks.
Nov 6 '06 #1
6 5712
Thanks (Th****@work.com) writes:
Two Windows 2003 server,
one with SQL 2005 server,
another with SQL Express.

Is it possible to copy databases from SQL 2005 to SQL Express?
Yes, but I don't think you can use the Copy Database Wizard, but you will
have to do it by hand. Which is not very difficult anyway.

On source machine backup database, and make note of the logical
filenames. (You see these with sp_helpdb).

On target machine to do:

RESTORE db FROM DISK = 'pathgoeshere'
WITH MOVE 'logicalfilename1'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.mdf',
WITH MOVE 'logicalfilename2' TO
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\db.ldf',
REPLACE

I've used MSSQL.2 for the target, but you use the number for your
Express instance. You find out which is which by looking around in
the directories.

This does not migrate logins, and mappings user and logins are likely
to be out of whack. You can use sp_change_users_login to fix.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 6 '06 #2
Thanks.

Same system setup as before.
Computer A with SQL 2005
Computer B with SQL Express

Is it possible to connect the SQL Express in B from the A using the SQL
2005?

I tried it, but failed.

If I can do this, that means I can use Import function in SQL 2005 to import
Access databases into SQL Express.
Nov 7 '06 #3
Am_I_right (Am********@world.com) writes:
Same system setup as before.
Computer A with SQL 2005
Computer B with SQL Express

Is it possible to connect the SQL Express in B from the A using the SQL
2005?

I tried it, but failed.
How did you try? How did it fail?

You can always set up a linked server, but it's unclear to me if that
is what you tried.
If I can do this, that means I can use Import function in SQL 2005 to
import Access databases into SQL Express.
No, that is what you can do with a linked server. You could however
import you Access database into SQL 2005 non-Express, and then
copy the database with backup/restore as I described in my previous
post.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 7 '06 #4

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Am_I_right (Am********@world.com) writes:
>Same system setup as before.
Computer A with SQL 2005
Computer B with SQL Express

Is it possible to connect the SQL Express in B from the A using the SQL
2005?

I tried it, but failed.

How did you try? How did it fail?
Computer A and Computer B are linked with network cable through a network
switch.
Both using MS Win 2003.
Thanks.
You can always set up a linked server, but it's unclear to me if that
is what you tried.
>If I can do this, that means I can use Import function in SQL 2005 to
import Access databases into SQL Express.

No, that is what you can do with a linked server. You could however
import you Access database into SQL 2005 non-Express, and then
copy the database with backup/restore as I described in my previous
post.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Nov 8 '06 #5
(Th****@work.com) writes:
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
>Am_I_right (Am********@world.com) writes:
>>Same system setup as before.
Computer A with SQL 2005
Computer B with SQL Express

Is it possible to connect the SQL Express in B from the A using the SQL
2005?

I tried it, but failed.

How did you try? How did it fail?

Computer A and Computer B are linked with network cable through a network
switch.
Both using MS Win 2003.
Just because you connect the computers with a network cables does not
mean that the two SQL Servers are connected. You must also set up a
linked server. Although it depends on what you want to do. Linked servers
are needed only if you want to access tables on server from the other.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 8 '06 #6
I have tried your method, but could not work on my machine.
Sorry, I am actually have zero knowledge on SQL

Set up SQL sever is ok, import Access to SQL is also oK for me.
but couldn't figure out how to import Accees into SQL Express.
either directly or through another SQL 2005 server.

Next I am going to try import Access into MySQL by using Premiumsoft
Navicat.
If that work, I will decide to use MySQL, because it is open source and
lower cost.
Nov 12 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: QLD_AU | last post by:
Has anyone see the following error ? VS 2005 Installs ok, however the SQL Mobile Edition (part of a full install) fails with the following error ? With Thanks Jason
1
by: Byron | last post by:
Hi, I have(had) an old Win2k Server server with about 30 web site databases (SQL 2000) that just went under due to hardware problems. Thankfully, I have backups of all the databases plus the MDF...
1
by: Peter | last post by:
I've purchased VS.NET 2005 Standard and have tried to install SQL Server 2005 Express, but get the following error in the error log. Please could someone help me.... Microsoft SQL Server 2005...
1
by: Paolo | last post by:
I have been following a few walkthroughs that came with VS 2005, and they work fine (I had to change the machine.config files to work with my SQL 2005 Pro version, instead of the SQL 2005 Express)....
1
by: sck10 | last post by:
Hello I am using Visual Web Developer 2005 Express. Whenever I use the "copy web site" function, it creates a "publishform" button on my task bar that doesn't go away. So if I publish 3...
3
by: tlyczko | last post by:
Can anyone refer me to good 'recipes' or sources of information on this topic?? I have Visual Studio Tools for Office, which installs SS 2005 Express locally to my XP box, and I want to develop...
9
by: fniles | last post by:
I would like to copy a table(s) from SQL Server 2005 to a CVS file and vice versa. I was thinking to use the BCP command line utility, but I have a few questions: 1. The machine where I am...
9
by: dpatel75 | last post by:
I am trying to copy a database from a SQL 2000 SP3 Windows 2000 server to a 2005 SP2 Windows 2003 server. I am trying to use detach and attach method (have tried both within Management Studio and...
6
by: AAaron123 | last post by:
I need to move a database from sql server 2008 to sql express 2008. Will backing up the sql server 2008 database to a file and then using that file to restore to sql express 2008 work? I don't...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.