Q
Manage Learn to apply best practices and optimize your operations.

Upgrade databases to SQL Server 2005 after server upgrade

One upgrade method is to uninstall SQL Server 2000 and then install SQL Server 2005 and attach databases. Learn how to upgrade those databases.

I need to upgrade a SQL Server 2000 SP4 to SQL Server 2005. As all my other 2005 servers are "clean installs," i.e. not upgrades, I'd like to take the same approach with this one. So, the plan is to back up the databases, uninstall SQL Server 2000, install SQL Server 2005 SP2 and attach databases. Now, obviously these will be 2000 databases. How do I upgrade the databases to SQL Server 2005, and scripts/stored procedures etc? Can you enclose the necessary links or stored procedures?
The SQL Server 2000 databases will be upgraded to SQL Server 2005 as soon as they are attached to the SQL Server 2005 instance. You will need to change the compatibility level from 80 (SQL 2000) to 90 (SQL 2005) in order to get the full benefit of SQL Server 2005. After you attach the databases to the SQL Server 2005 instance and change the compatibility level, it's recommended that you rebuild your indexes and update your statistics.
This was last published in December 2007

Dig Deeper on SQL Server Migration Strategies and Planning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close