Posted by Erland Sommarskog on 11/06/06 23:17

Thanks (Thanks@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, 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
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]