What is the best way to migrate a database from one server to another server?

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
EXEC sp_detach_db
      @dbname = N'MyDatabase1';
EXEC sp_detach_db
      @dbname = N'MyDatabase2';
EXEC sp_detach_db
      @dbname = N'MyDatabase3';
EXEC sp_detach_db
      @dbname = N'MyDatabase4';
EXEC sp_detach_db
      @dbname = N'MyDatabase5';
EXEC sp_detach_db
      @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
      ON(NAME=’MyDatabase1_Data’,
            FILENAME='C:\DataFiles\MyDatabase1_Data.mdf')
      LOG ON(NAME='MyDatabase1_Log',
            FILENAME='C:\LogFiles\MyDatabase1_Log.ldf')
      FOR ATTACH
      WITH ENABLE_BROKER;
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.

Thanks.
Bits
data_bitsdbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tigin44Commented:

additionaly
step 0
you should also transfer the loging from the old server to the new server... check this link
   http://support.microsoft.com/kb/246133

step 5
 raise the compatibility level to sql server 2008



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
data_bitsdbaAuthor Commented:
The link talks about migrating SQL Server 7 and SQL Server 2000. Are those steps still valid with 2005 and 2008?

In Step 4, on the 2008 server, is the best thing to do?
CREATE DATABASE MyDatabase1
      ON(NAME=’MyDatabase1_Data’,
            FILENAME='C:\DataFiles\MyDatabase1_Data.mdf')
      LOG ON(NAME='MyDatabase1_Log',
            FILENAME='C:\LogFiles\MyDatabase1_Log.ldf')
      FOR ATTACH
      WITH ENABLE_BROKER;
0
TempDBACommented:
Apart from that following other things are to be kept in mind:-
Server level settings:-
1. set the configuration of the server properly(sp_configure).
2. transfer all the logins from the old server (as tigin44 said).
3. Move the server level triggers.
4. Move the jobs, SSIS packages, and any other utilities you are using.
5. Move the link servers.
6. Check for the msdtc setup and proper functioning.

Database level settings:-
1. Fix for the orphan users.
2. Move publications\subscriptions.
3. Check for any high availability solution applied and move that too.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SThayaTechnical MAnagerCommented:
Hi,

  you can achieve this with SSIS package . use the "Transfer SQL Server Objects Task" in the control flow for the same .

Note " all the logins should be in active status .. if any thing is disabled , delete first then start the package


0
VENKAT KOKULLASQL Server DBACommented:

The below link will surely suffice your requriement i guess..pls have a look

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

--Venkat
0
data_bitsdbaAuthor Commented:
If I detach the databases on the source server and copy the files over to the new server, can I reattach the databases back on the original server?

I want a fall-back plan in case things don't work on the new server.

0
VENKAT KOKULLASQL Server DBACommented:
Yeah why not You can do that...

--Venkat
0
data_bitsdbaAuthor Commented:
I used the restore from backup method to get the databases migrated and got the users migrated too.
I'm having trouble with the maintenance plans. I exported them to the filesystem and copied them to the new server.

When I try to import them into the new environment, it fails. I keep getting this error:

Failed to retrieve the data for this request…
The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information…..

Login timeout expired
A network related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if the instance name is correct and if SQL Server is configured to allow remote connections. For more information….

And so on.

It seems that SSIS can't find the default instance. There is only one instance of SQL Server here. I'd rather not recreate them from scratch since some of them are complicated.

TIA!

Bits
0
data_bitsdbaAuthor Commented:
wanted responses to the followup questions
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.