Move database from SQL Server 2000 to 2008

printmedia
printmedia used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
After the restore, reset the compatibility level to SQL 2008, if you can.  Then, either way, update all stats on all tables.
Ensure you’re charging the right price for your IT

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

Author

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?
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 Administrator
Commented:
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.
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial