sql server (alternate): 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.

Thanks (Thanks@work.com) writes:
[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

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.

Am_I_right (Am_I_right@world.com) writes:
[quoted text, click to view]

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.

[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

[quoted text, click to view]

Computer A and Computer B are linked with network cable through a network
switch.
Both using MS Win 2003.
Thanks.

[quoted text, click to view]

(Thanks@work.com) writes:
[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

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.