Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Hands-ups, I have no idea what I'm doing!

We have an old SQL 2000 Server with 2 databases. I have a new server running SQL Server 2008 R2. Can someone outline the basic steps of migrating the databases from the old to the new and highlight any potential pitfalls or things to bear in mind.

Any advice is much appreciated.

share|improve this question

First step is download and run the SQL 2008 Upgrade Advisor:

For an OS you are looking at a minimum of Windows 2003/2008 Std.

share|improve this answer
I have the new hardware up and running, I'm at the actual data migration stage. For example how do I transfer logins details, do I simply just restore a backup from the old SQL Server onto the new one? – Symon Jul 14 '11 at 18:34 – jl. Jul 14 '11 at 18:39
The sp_help_revlogin script will generate logins (SQL Server level not database level). Unless all of your logins use Integrated security or your SQL logins all specify master as the default database, I would restore my SQL 2000 databases to the SQL 2008 environment. They will upgrade during the restore. After the restore, run your login script from above, then run sp_change_users_login in the restored databases for the SQL logins to map database users to respective logins. Change your compatibilty mode, update your statistics. – jl. Jul 14 '11 at 22:06
jl, thank you. So will this transfer user logins and passwords from my SQL 2000 setup to my new 2008 R2 server? I have read many posts/articles that say a straight jump from 2000 to 2008 is not possible because of certain changes made, for example passwords get reset to something random. – Symon Jul 14 '11 at 22:31
Also, do I run the scripts against the specific databases that I want to transfer the logins for? The scripts state 'USE master', but the master DB does not hold the logins I want to transfer. Do I change this to the name of the database I need to transfer the logins for? Sorry for my ignorance. – Symon Jul 14 '11 at 22:36

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.