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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Brian CroweDatabase AdministratorCommented:
0
David ToddSenior Database AdministratorCommented:
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 Database AdministratorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.