[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-18
9
Medium Priority
?
377 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:data_bits
9 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 750 total points
ID: 36988694

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
 

Author Comment

by:data_bits
ID: 36988753
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
 
LVL 25

Assisted Solution

by:TempDBA
TempDBA earned 750 total points
ID: 36988811
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 11

Expert Comment

by:SThaya
ID: 36991615
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
 
LVL 5

Expert Comment

by:VENKAT KOKULLA
ID: 36998193

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

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

--Venkat
0
 

Author Comment

by:data_bits
ID: 37031583
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
 
LVL 5

Expert Comment

by:VENKAT KOKULLA
ID: 37031667
Yeah why not You can do that...

--Venkat
0
 

Author Comment

by:data_bits
ID: 37079352
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
 

Author Closing Comment

by:data_bits
ID: 37178182
wanted responses to the followup questions
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question