[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to move a SQL database

Posted on 2014-01-24
4
Medium Priority
?
225 Views
Last Modified: 2014-01-30
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
Comment
Question by:dowhatyoudo22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
PadawanDBA earned 1600 total points
ID: 39807089
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
 
LVL 8

Expert Comment

by:Jeff Perry
ID: 39807097
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
 

Author Comment

by:dowhatyoudo22
ID: 39807414
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
 
LVL 8

Assisted Solution

by:Jeff Perry
Jeff Perry earned 400 total points
ID: 39807423
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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