how to upgrade msde to sql 2005 on different server

[darwin101]

hi all
is it possiable to upgrade an msde db on one pc to sql 2005
on a different server? or is the only path to do an inplace upgrade.

much thanks

 

[gmmastros]

If you detach a database from MSDE and attach it to SQL 2005, it will automatically upgrade the database for you. Before attaching it though, I strongly advise you to make a backup copy though. Once a database is upgraded to 2005, it is EXTREMELY difficult to 'put it back' to 2000.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[darwin101]

Thanks George.
Will the new attached datbase have a size limit or is it now fully a 2005 database when using the attach method?

If that works, I would guess that using a backup of an msde db to do a restore to sql2005 might fully upgrade the db also.?

 

[gmmastros]

It will fully be a 2005 database.

You may want to check the properties of the database and make sure that it is set to unrestricted file growth.

I've always detached and attached, so I can't say for sure if a restore will work for you. I suspect it will. Also, I'm reasonably confident that others will respond to this thread if anything I have posted isn't right.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

A backup and restore will work fine for this upgrade.

Either way make sure to change the compatility mode and the file growth.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[darwin101]

Thanks to the both of you