Move database from SQL Server 2000 to 2008

Hi all.

I have 3 databases that I want to move from SQL Server 2000 to another box which has SQL Server 2008. What is the best way to do this?

I was thinking making a backup of each database on SQL Server 2000 and then going to SQL Server 2008 and restoring those files. Is that possible?

Thank you in advance!
printmediaAsked:
Who is Participating?
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.

BembiCEOCommented:
Yes, this is one path...
You should run the Upgrade advisor tool first to see, if there are any issues. ..
Take care of user accounts.....

See  step by step details on TechNet.
http://blogs.technet.com/b/mdegre/archive/2009/07/21/migration-sql-server-2000-to-sql-server-2008.aspx
KCTechNetCommented:
It is always good to have a backup of each database before you try anything new.  If they are straight tables from SQL 2000, you should be able to restore them to SQL Server 2008 or higher.  If you have views, functions, etc a straight restore may not transfer all data.  As Bembi says, the migration tool will verify if any issues will occur before importing.

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
Scott PletcherSenior DBACommented:
After the restore, reset the compatibility level to SQL 2008, if you can.  Then, either way, update all stats on all tables.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

printmediaAuthor Commented:
Thank you all. For now, I will only be transferring tables. The views will still be on SQL Server 2000 until I move them all to SQL Server 2008.

That being said, since the views will still be on SQL Server 2000, will these views be able to look at the tables on SQL Server 2008? What would I need to change in the views to be able to look at the tables on SQL Server 2008?
KCTechNetCommented:
The quickest way would be to create a new view of the existing view on SQL Server 2008 and point to the same tables on SQL Server 2008.  (Just open the old view (Design) and copy all the text and copy that over to the new view).
Jose TorresCertified Database AdministratorCommented:
printmedia,

You need to review what features have been discontinued in 2005, 2008 and 2008R2 and while you are at it you might as well look at 2012.

Here are some links to help you on your way
SQL Server 2005 Database Engine Backward Compatibility (2005)
SQL Server Database Engine Backward Compatibility (2008)
SQL Server Database Engine Backward Compatibility (2008R2)

When copying the tables over you may want to review your tables and see if you are using the text datatype and consider making the change now to varchar(max)

Once you copy the table(s) over that are the underlying data for a view, then you create your view.
You start out by doing a straight create view from your 2000 db and one of two things are going to happen... The create is going to work or fail.  If it works then the code for the view is compatible, if it doesn't then the code is not compatible and you will have to review the error generated and re-engineer to accomplish the same results.
KCTechNetCommented:
One other thing to add to Jose Torres' comment:
If your existing tables have a text datatype, you may receive errors querying against the newly created table.
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 2008

From novice to tech pro — start learning today.