Copy databases from SQL 2005 server to SQL Express

[Thanks]

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.

[Erland Sommarskog]

Thanks (Th****@work.co m) 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 'logicalfilenam e1'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\ MSSQL\Data\db.m df',
WITH MOVE 'logicalfilenam e2' TO
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.2\ MSSQL\Data\db.l df',
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****@sommarsk og.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

[Am_I_right]

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.

[Erland Sommarskog]

Am_I_right (Am********@wor ld.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****@sommarsk og.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

"Erland Sommarskog" <es****@sommars kog.sewrote in message
news:Xn******** **************@ 127.0.0.1...

Am_I_right (Am********@wor ld.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****@sommarsk og.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

[Erland Sommarskog]

(Th****@work.co m) writes:

"Erland Sommarskog" <es****@sommars kog.sewrote in message
news:Xn******** **************@ 127.0.0.1...

>Am_I_right (Am********@wor ld.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****@sommarsk og.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

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.