• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

How to move a SQL database

I have two database instances running on a SQL 2000 SP3 Server. I need to move these database instances to a another server running SQL Server 2008 R2. How/what is the best way to achieve this move?
0
dowhatyoudo22
Asked:
dowhatyoudo22
  • 2
2 Solutions
 
PadawanDBAOperational DBACommented:
I would encourage you to take a look at this question that I answered for someone else (under a different account).  I tried to be pretty thorough in explaining the options, but if I missed anything or you had any other questions, let me know: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28121746.html#a39148501
0
 
Jeff PerryWindows AdministratorCommented:
Microsoft Knowledgebase #314546

I personally would use the database export on the old server and then import the databases on the new server, though using the wizard in option 3 sounds interesting.
0
 
dowhatyoudo22Author Commented:
documentation seems to be focused on SQL 2005 -> SQL 2008. I'm wondering if the same rules apply to SQL 2000 SP3 -> SQL 2008 R2?
0
 
Jeff PerryWindows AdministratorCommented:
The move will work from 2000 to 2008 but documentation says that it will be in 2000 compatibility mode once you bring it online.

To restore native 2008 compatibility on the database after the move you can run the following command.
ALTER DATABASE YourDatabaseNameHere
SET COMPATIBILITY_LEVEL = 100;

Open in new window


Keep your backup handy until you verify the compatibility mode change operates correctly.
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now