0

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.

Symon
  • 11
  • 1

1 Answers1

0

First step is download and run the SQL 2008 Upgrade Advisor: http://msdn.microsoft.com/en-us/library/ms144256.aspx

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

jl.
  • 1,076
  • 8
  • 10
  • 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
  • http://support.microsoft.com/kb/246133 – 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
  • The output of sp_help_revlogin is executed under under the context of master, as logins get added at the SQL Server level. Users are added at the database level. After the logins are created, the specific database permissions will need to be added. – jl. Jul 15 '11 at 02:53