What is the best way to migrate a database from one server to another server?
Posted on 2011-10-18
I need to migrate six SQL Server 2005 databases to a new server, which has SQL Server 2008 installed on it. The databases are outgrowing the old 2005 server. I want to move everything to a new server with more memory and disk space and upgrade to SQL Server 2008 in the process.
I’ve been reading about the use of attach and detach stored procedures and am wondering if I can use them to accomplish both tasks, migrate to new hardware and upgrade the environment at the same time. I've read that sp_attach_db is depricated in 2008, so what's the best way to do this?
Here is what I plan on doing and I’d like verification that this will work.
Step 1: Make the databases offline to ensure nothing is accessing them on the old 2005 Server.
ALTER DATABASE MyDatabase1 SET OFFLINE;
ALTER DATABASE MyDatabase2 SET OFFLINE;
ALTER DATABASE MyDatabase3 SET OFFLINE;
ALTER DATABASE MyDatabase4 SET OFFLINE;
ALTER DATABASE MyDatabase5 SET OFFLINE;
ALTER DATABASE MyDatabase6 SET OFFLINE;
Step 2: detach the databases
@dbname = N'MyDatabase1';
@dbname = N'MyDatabase2';
@dbname = N'MyDatabase3';
@dbname = N'MyDatabase4';
@dbname = N'MyDatabase5';
@dbname = N'MyDatabase6';
Step 3: Copy the mdf and ldf files to the new server
scp C:\MyDatabase1.mdf D:\MyDatabase1.mdf
scp E:\MyDatabase1_Log.ldf L:\MyDatabase1_Log.ldf
scp C:\MyDatabase2.mdf D:\MyDatabase2.mdf
scp E:\MyDatabase2_Log.ldf L:\MyDatabase2_Log.ldf
scp C:\MyDatabase3.mdf D:\MyDatabase3.mdf
scp E:\MyDatabase3_Log.ldf L:\MyDatabase3_Log.ldf
scp C:\MyDatabase4.mdf D:\MyDatabase4.mdf
scp E:\MyDatabase4_Log.ldf L:\MyDatabase4_Log.ldf
scp C:\MyDatabase5.mdf D:\MyDatabase5.mdf
scp E:\MyDatabase5_Log.ldf L:\MyDatabase5_Log.ldf
scp C:\MyDatabase6.mdf D:\MyDatabase6.mdf
scp E:\MyDatabase6_Log.ldf L:\MyDatabase6_Log.ldf
Step 4: This step would be done on the SQL Server 2008 system. I’ve read that sp_attach_db has been deprecated. Can I still use it or do I need to use another command that I’ve read about, ALTER DATABASE?
Or this procedure on each of my databases?
CREATE DATABASE MyDatabase1
Am I I missing something here? I don't have much SQL server experience so I'm unsure on how to do something like this.