Move DB to a new server

[MasterRacker]

I need to migrate a DB from SQL Express 2005 to SQL Standard 2005. On the Express box the mdf and ldf files reside together in C:\Program Files\Microsoft SQL Server\blahblahblah...

On the Standard box we have mdfs and ldfs separated onto separate arrays.

Whats the best way to move the DB. Can I simply detach on the Express box, copy the files to the proper locations and attach on the Standard box?

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MasterRacker]

Another option: If I create a new blank DB on the Standard box so the files are in the right place then restore a backup from the Express box into it would that work? Are there any differences between the two approaches?

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[intelwizrd]

You should be able to detach, move the files to the new server, and when you attach the database you will have to tell it where the log file is located.

You can restore from a backup as well without having to create the database first. Part of the restore process is to tell it where to put the data and log files.

All fairly straight forward in SSMS.

more info and commands if you want them here

http://support.microsoft.com/kb/224071

 

[mrdenny]

The detach and attach will work just fine for you.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog