Migrate database from SQL Server 2000 to SQL Server 2008 R2

Hi all.

I want to migrate a specific database from SQL Server 2000 to SQL Server 2008 R2, I've done some reading and this is the steps I plan to take:

(1) Run the SQL Server 2008 Upgrade Advisor to see if there are any potential problems when I move this database to 2008 R2.
(2) Backup the database and restore the .bak file in SQL Server 2008
(3) Run DBCC CHECKDB

My first question is:

(1) Is my plan the correct way to to a migration from 2000 to 2008 R2?

(2) How do I bring over all of the logins and permissions over to 2008 R2?

Thank you in advance!
Sim1980Asked:
Who is Participating?
 
Brian CroweDatabase AdministratorCommented:
Your plan appears sound.  The logins and permissions are in the msdb system database.  You can either try and script out all of those objects or try to migrate that database as well.
0
 
Sim1980Author Commented:
Can you give me some more information on "script out all of those objects"? I don't know what that is, maybe an example or website where I can learn what to do?
0
 
Sim1980Author Commented:
We have about 100 users with permissions on the database.
0
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.

 
Brian CroweDatabase AdministratorCommented:
0
 
David ToddSenior DBACommented:
Hi,

There is a free MS script hat will script out the logins sp_help_revlogins or something like that. With 100 users it will be a lot easier and less stressful to do this. The output of the above procedure can then be taken an examined for 'dead' users, and then executed on the destination server - you now have the logins that the database will need ...

Also, a few other things after restoring the database on SQL 2008
1. Check the compatibility level. Unless you really do know you need otherwise, it should be on the highest setting possible.
2. Update the statistics - sp_updatestats
3. dbcc updateusage
4. Generally check the database settings the page verify should now be Checksum rather than Torn Page

HTH
  David
0
 
Sim1980Author Commented:
I restored the database on SQL Server 2008 using the backup file from SQL Server 2000 and I see all of the logins and it brought in all of the correct roles.

I went to the database --> Security --> Users

Does this mean I don't have to do the scripts to bring in the users/logins?
0
 
Brian CroweDatabase AdministratorCommented:
Verify that the instance-level logins are there as well ... Server -> Security -> Logins
0
 
Scott PletcherSenior DBACommented:
In SQL Server, you have to be careful with terminology between "login" and "user".

In the db, you see the *users*, not the logins.  The logins are in the master db (not msdb).  You can script them out from there or write some code to pull them out of master.  

You also need to check the configurations in the new master db.  Make sure they are what you currently need, including the "cost threshold for parallelism", which should be raised from the default value.

You should check the msdb db for any jobs and job schedules, which can also be scripted to move to the new server.  Also check for packages to move, but those you can't really script out, they may need converted to be moved.

After the user db is restored, you need to change the compatibility level to at least SQL 2005 and then update all statistics, like so:
USE [restored_db_name]
ALTER DATABASE [restored_db_name] SET COMPATIBILITY_LEVEL = 80 --90
EXEC sp_updatestats
0
 
David ToddSenior DBACommented:
Hi,

In your nearly restored database, run this:
execute sp_change_users_login 'report'

Then you will see all the orphaned users - those without a matching login.

Its not enough to do this visually, you really do need to run the above and then sort out the results. Why? Because the two logins on the two servers may be the same to us, but have different SIDS on the server.

HTH
  David
0
 
Sim1980Author Commented:
Got it. I will do it when I get to the office. I'll close this question and split the points. Thanks!
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.

All Courses

From novice to tech pro — start learning today.