SQL 2008 std R2 - Copy Data folder?

Question

I have Windows server 2003 std/SQL server 2000 std server (old server).  I also have a "go between Work PC".  So the "Work" PC will be identical to the old server (Win2003\SQL2000).  I will copy the "Data" folder over from the old server to the "Work" PC, run the SQL 2008 Update in Place on it.   (So if something does not work, I can always plug the old server back in and then try to figure out what went wrong.  So I don't plan on clobbering the old server.)  I have new server I will setting up as Windows server 2008 std R2 / SQL server 2008 std R2 (32 bit SQL- same as before on SQL 2000).

With the SQL server software "stopped" on both servers (Work and New), can I hook a crossover cable between the 2 servers and copy the entire contents from Program Files\Microsoft SQL Server\MSSQL\Data folder from the Windows 2003\SQL 2008 (upgraded) machine to the "Data" folder on the new Windows 2008\SQL 2008 and everything be fine with SQL server?

This is how I've always done it on SQL 2000 to go from an old server to a new server and it has always worked.  Just wondering if that still applies in SQL 2008?

thanks

---

Murf

Answer 1

Hello,

Passwords are case sensitive in SQL Server 2008.

System databases should not be copied to SQL Server 2008, it is not supported.

You may find orphan users. You will find scripts to transfer logins from the old SQL Server 2000 instance to the new SQL Server 2008 instance.

http://www.sqlservercentral.com/Forums/Topic856985-391-1.aspx

Please read the following resources:

http://download.microsoft.com/download/2/0/B/20B90384-F3FE-4331-AA12-FD58E6AB66C2/SQL%20Server%202000%20to%202008%20Upgrade%20White%20Paper.docx

http://www.microsoft.com/en-us/download/details.aspx?id=11455

Hope this help.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

Hmmm.   I'm not copying from 2000 to 2008.  I'm "Updating in place" a 2000 sever.  THEN when it is in 2008 format, I'm copying the 2008 format files over to another 2008 server.  I've read others that say they have done this successully.  Why would this not work - when I start SQL 2008, how will it know that the new "empty" install files are gone and a complete database is in place.  Are there other files that are needed other than those in the "data" folder (.ldf, .mdf, ...)?

thanks,

---

Murf

Answer 3

Hello,

I would recommend you to follow the procedure explained on the whitepaper I posted above.

I would say it makes more sense to install the new SQL Server 2008 instance, and restore or attach de SQL Server 2000 databases.

Try to use SQL Server Upgrade Advisor before upgrading.  

Hope this help.

Regards,
Alberto Morillo
SQLCoffee.com

Answer 4

I have been using the SQL Upgrade Advisor and "fixing" the few issues it finds.  We don't do "Complex" things with our server.  Also, Server Admin is my "other hat".  I'm primarily a developer (and do the server admin too because we have no one to do that).  So I'm not an expert by any means when it comes to SQL Server.  I need the simplest most straight forward method.  I looked at the white papers (again).  And was reminded of why I doing "Update in place"  (on a temp "work" server in case it all goes awry I can still set my old server back in place).  "Update in place" says it does a lot of things automatic where The side-by-side says:

Objects requiring other transfer methods include:

• Data files
• Database objects
• SSAS cubes
• Configuration settings
• Security settings
• SQL Server Agent jobs
• SSIS packages

I don't have the time or knowledge to figure all that out!

So are you saying that instead of doing the "Data" folder file copy of my update 2008 files to my new empty server, I should backup the 2008 DBs and then restore them to my new 2008 server?  Can I also do that with the Master, MSDB, model, and tempdb system folders?  (If not, I'm going to be hosed with all the settings that are in there.)

thanks

---

Murf

Answer 5

Hello,

Yes, an side-by-side upgrade is what I recommend you. Doing a backup of user databases on the actual instance and restoring them into a new SQL Server 2008 instance, give the option to keep the original SQL 2000 instance intact, in case you need to rollback.

Do not try to restore system databases like the master database, because is not supported between instances of different versions, You can script your jobs/logins and recreate them.


Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com