We currently have a SQL 2008 datawarehouse server with about 15 different databases.
We were given a new Server (faster,more memory,etc..) with a fresh copy of SQL 2008 R2 installed.
I will need to move all of the databases from the old SQL server to the new SQL server. The issue that i have is that on the current SQL server, we have a C: and D: drive. Some of the databases and logs are on C: and others on D:. On the new SQL server we have C:, D:, and E: drives.
What is the best way to move the databases from the old SQL server to the new SQL server and have the files reside on a different drive? For example, If i have database called ABC and the data and log files are stored on C:\DataFiles. I want to move them to the new SQL server and have the data file on D:\DataFiles and the log file on E:\LogFiles.
I will also be moving the system databases as well. Those all reside on C: and will be on C: on the new server. I imagine i will have to move those first.
Since this is a datawarehouse, i don't have any issues with stopping the service if I need to.
Would backup and restore allow me to move the data and log files to new locations? I've been reading about attach/detach databases and was wondering if that was another option. I would also need that option to have the ability to move the data files to a different location