i need to migrate from sql 2005 express to sql workgroup edition

hello,

i have no idea what i am doing here and could use some help from someone who knows what they are doing.

i have a proliant ml350 g3 server running windows 2003 server with sql express 2005 software.

all the databases are running in compatibility sql 7 mode.

I would like them to be in sql 2005 mode if possible. Is there a way to upgrade the databases in the process?

here's a plan I came up with:

take sql workgroup 2005 edition evaluation, install it on a machine.

Mount all databases on the machine.

Wipe out the sql express 2005 machine, reinstall workgroup edition on the new server.

but here is my question

is there a way to migrate the data from the workgroup edition over to the new server through a DTS copy process, and if so, will it create a new version of the databases with the proper collation or the proper english?

please let me know. if this works, i will be thrilled!

First, you can upgrade in place by installing workgroup over Express on the same machine. Make sure you indicate you want to upgrade by selecting the Express instance name at the appropriate point during the install.

If you do want to switch machines as part of the upgrade, you can either copy the database files (both .mdf and .ldf) manually or use DTS. Either way, I suspect you're likely to lose logins and some other info - but I'm not a DTS expert so I may be misspeaking. You should post a question in that forum if you are going to consider using DTS.

Books On Line (BOL) has a good description of how to go from one compatibility mode to another. Use the sp_dbcmptlevel stored procedure, either before or after the upgrade.

Paul

|||

one more question Paul if you don't mind.

Does the BOL Books on Line article show how to change from the existing collation? I apparently have a "conflict"

when I start the SQL Server Management Studio and right click on any of my databases, they show this error:

"Cannont Show requested dialog.

ADDITIONAL INFORMATION:

Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)

-> an exception occured while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.Express.ConnectionInfo)

--> Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Compatibility_52_409_30003" in the equal to operation. (Microsoft SQL Server, Error: 468)

I have no idea what this means, which is why I had the idea to transform the packages using DTS?

please let me know if you have an answer. If this is for another forum, please let me know that. Thanks for your help.

|||

I would post the question about collation in the tools forum. Are Management Studio and Express on the same box? I have no idea what would cause such an error.

Paul

|||

thanks this has helped tremendeously.

I still have problems with collation but it looks like the SQL Server default collation and the master/temp/model db are different and this is causing problems.

I need to find a tool to rebuild the master and temp and model db.

One came with SQL 2000 but I do not have one for SQL 2005.

I am still searching for rbuild.exe to use.

hopefully I'll find it!

thanks,

j