MSDN Archive Home
Help and FAQs
All Resource Updates
Change History (all pages)
Upgrading Steps to SQL Server 2005
Methods for Upgrading to SQL Server 2005
, Oct 16, 2008
This article describes the normal methods of upgrading to SQL 2005 and it doesn't include the steps for Clustered environment. There are two types of upgrade methods to SQL Server 2005 from SQL Server 2000 or SQL Server 7 namely,
1. In-place upgrade
2. Side by Side upgrade
SQL Server 2005 gives us the ability to automatically upgrade an instance of SQL Server 2000 or 7.0 to SQL Server 2005. The reason it is called in-place upgrade is because a target instance of SQL Server 2000 or 7.0 is actually replaced with a SQL Server 2005 instance. We do not have to worry about coping data from the old instance to new instance as the old data files are automatically converted to new format. This upgrade method is the easiest way to upgrade the database to newer version. We have to run the SQL Server 2005 setup.exe and choose to upgrade and existing instance of SQL Server 2000 or SQL Server 7.
Performing Rollback In-place upgrade:
Because the legacy SQL Server instance is replaced by a new SQL Server 2005 instance, rolling back an in-place upgrade can be complex. The new data file structures for SQL Server 2005 are not compatible with legacy SQL Server instances. Therefore, to roll back, we will have to uninstall SQL Server 2005, removing the data files and other components, and then install the legacy instance of SQL Server and rebuild the original server. If we can make a backup or an image of the initial system, we might be able to shorten the time required to restore the original system on the server.
Side by Side upgrade:
There are three types of methods to upgrade in this method namely,
1. Detach and Attach the databases refer,
2. Backup and Restore the databases refer,
3. Copy Database wizard refer,
We cannot perform backup/restore or detach/attach the databases from one version to another i.e. in order to restore/attach the system databases we need to have the same build and same edition of SQL Server as shown in the KB,
The following are the steps to perform Side by Side upgrade from SQL Server 7 or SQL Server 2000 to SQL Server 2005.
1. Take full backup of all the databases in SQL 7 or SQL 2000 for precautionary purpose
2. Script all the logins using
in SQL 7 or SQL 2000
3. Script all the jobs in SQL 7 or SQL 2000 using the below link
. We can also script all the jobs into a single file by selecting all the jobs simultaneously and then right click and script them.
4. Install SQL Server 2005
5. Detach all the user databases in SQL 7 or SQL 2000 and copy the files
6. Attach all the user databases in SQL 2005
7. Run the script taken in Step 2 to recreate the logins
8. Run the script taken in Step 3 to recreate the jobs
9. In order to migrate DTS packages to SSIS we can refer this link,
10. Once everything is completed login and check the application functionality
The below White paper is the best resource for getting started with upgrading to SQL Server 2005,
Steps prior to and after upgrade:
1. We need to run the Upgrade Advisor,
against the existing SQL Server 2000 or SQL Server 7 databases in order to identify and fix any potential upgrade issues
2. Take full backup of all the databases and keep it in a safe location just for a precautionary purpose
3. Insert the SQL Server 2005 Setup.exe, run the setup files and upgrade the instance.
4. After upgrading we need to rebuild all the indexes and also run dbcc checkdb to check the database consistency
5. Finally we have to change the database compatibility level to 90 in order to avail the new features in SQL 2005 like performance dashboard. This can be done using sp
dbcmptlevel as shown below, sp
dbcmptlevel ‘dbname’, 90
Oct 18 2008 at 5:35 PM
, version 3
Sign in to add a comment
Related Resource Pages
Microsoft SQL Server Community & Samples
SQL Server Reporting Services
SQL Server Analysis Services
SQL Server Integration Services
SQL Server Learning Materials and Tutorials
More Tags ...
Visual Studio 2005
Visual Studio 2008
Visual Studio 2010
Manage Your Profile
MSDN Flash Newsletter
© 2008 Microsoft Corporation. All rights reserved.