Migrate from sql server 2000 to 2008 r2 - how to

Question

I have a database working on SQL Server 2000. We are now migrating to a new server with SQL Server 2008 r2. Can anyone please point me to some resource or howto? I'm not really finding my way around SQL 2000.

Thank you!

Myosin

You don't understand SQL Server 2000? – Hawn

Answer 1

Basically, what you need to do is:

• backup your database in SQL Server 2000 to a .bak file
• move that *.bak file to your new server
• restore that database onto your new server

You're done! There's really nothing more to it..... just backup (on your old system) and restore (on your new system).

So where exactly is your problem ??

Update: as @Péter correctly mentions: this leaves your database in the SQL Server 2000 compatibility mode. This means: even though you've "migrated" to SQL Server 2008 R2, you can still only use the 2000 features.

In order to see what compatibility mode your database is in, check the sys.databases catalog view:

SELECT * FROM sys.databases WHERE name = 'YourDatabaseName'

One column is called compatibility_level and contains an INT; 80 = SQL Server 2000, 90 = SQL Server 2005, 100 = SQL Server 2008 / 2008 R2 and 110 = SQL Server 2012

In order to change your database to a different compatibility level, use this command:

ALTER DATABASE YourDatabaseNameHere
SET COMPATIBILITY_LEVEL = 100;

This will put your database into the "native" SQL Server 2008 (and 2008 R2) mode and now your migration is complete, you can use all the new SQL Server 2008 R2 features.

Apparent

There is no problem. I was just wondering what I should do, and thats just the answer I was looking for. Doing a backup on the old server and doing a restore on the new server. Thank you. – Myosin

I was reading all sorts of stuff like export or detach DB files, etc. – Myosin

not quite.. - if there are DB Login's then they will need to be recreated and linked - if you use any "=" or "=" joins that syntax is now deprecated and will cause those queries to fail, you'll need to replace with modern syntax - when you restore the DB you will be the owner, you should change that to a system account like SA – Polarization

@matao: true - one of the points that SQL Server 2011 "Denali" tries to address with its "contained" databases... – Apparent

This runs the database on compatibility mode, not really sql 2008r2 environment. So it's not really a migration. – Determination

@Péter: yes - sure - but it's still a migration, since the internal structures of the .mdf file have been upgraded to SQL Server 2008 R2 standards (adn you cannot go back and restore that file to a 2000 server...) – Apparent

But you can only use the SQL2000 feature set, and the server behavior still 2000 so not as good in optimization as 2008R2. – Determination

Answer 2

I would start by running the Upgrade Advisor against the 2000 server (during low utilization or off hours) to see what recommendations it makes and fully address each: http://msdn.microsoft.com/en-us/library/ms144256.aspx

Here too is a white paper from MS on the topic: http://download.microsoft.com/download/2/0/B/20B90384-F3FE-4331-AA12-FD58E6AB66C2/SQL%20Server%202000%20to%202008%20Upgrade%20White%20Paper.docx

A lot could go wrong...too much to cover in a forum setting. But then again nothing could go wrong...the best plan, test, and then test some more.

Clement

Good advice, for someone looking to upgrade. That was not what I was asking, but don't let me stop you. Answer any ol' question you like :) – Myosin

Sorry, maybe you should not have bothered mentioning the fact that you are migrating the database to 2008 R2 which implies upgrading. How about asking for what you really need, a tutorial for SQL 2000 ;-) – Clement

Answer 3

The others answers are correct from a technical perspective but not from a support point of view.

I don't think Microsoft support a direct upgrade from SQL Server 2000 to SQL Server 2008 R2. That doesn't mean it is hard, just that it is not supported. (Which may or may not be significant for your scenario)

You can upgrade your SQL Server 2000 instance to SQL Server 2008 R1 and then perform a subsequent upgrade to SQL Server 2008 R2. (Or even SQL Server 2012 if you are so inclined)

Atomy

I have had the same issue. We had to first go to a pre 2008 R2 instance before going to 2008R2. Its a pain but it works. – Bibbye

Answer 4

I am currently doing the same thing.

Creating your SQL 2008 database from a 2000 restore bak is a good first step. Most of the work for me was dealing with the user permissions, and making sure that the users were in sync with the database login, and that we didn't have a database schema generated by the backup tied to that user that would cause problems if we tried to recreate that database user.

What we ended up doing was:

1) Create a script. We had a script that would dynamically write a script to do the following: drop login, drop db user, drop schema, recreate login, recreate user, grant user permissions.

2) Restore database.

4) Run the generated script

Issie

Answer 5

Edited Apr 2012 because original link changed to latest version, SQL Server 2012

For an "in-situ" upgrade (MSDN links):

... to SQL Server 2008 R2

You can upgrade instances of SQL Server 2000, SQL Server 2005 or SQL Server 2008 to SQL Server 2008 R2.

... to SQL Server 1012

You can upgrade from SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2 to SQL Server 2012.

Hawn

This link has changed to SQL Server 2012. No longer answers the question. – Atomy

Answer 6

yet another option is to try to connect database (files) of sql2k to sql2k8 directly.

Malissamalissia

Answer 7

The simpliest way is to back up your database in SQL 2000 to a .bak file and move it. Do a restore and everything should be fine. Run a sp_Users_Loging to identify the users in the orphan server.

Trickster