As one of the most popular database servers, Microsoft SQL Server has been used for quite some time now. In 2005 Microsoft released an updated version of the SQL 2000 edition that was launched in 2001 called SQL Server 2005. Now you’re probably asking yourself, if SQL 2000 does the job, why should I upgrade to SQL 2005? Well, SQL 2005 offers many new features and improvement compared to its older brother SQL 2000. A few of the important features are as below:

Let us start off by making a backup of the existing database (just to make sure nothing goes wrong).

1. Open the MS SQL Enterprise Manager and connect to the server.
2. Expand the databases folder and right click the database you want to backup.
3. Select All tasks, then select backup database.
4. Provide a name for the backup in the Name text box. Leave the Database – complete radio button selected since we are performing a complete database backup.
5. Select the Overwrite existing media checkbox to initialize the destination file or device or select the Append to media checkbox to append the current backup to existing file or device.
6. To select a destination for the backup, click the Add button.
7. Select an existing file or enter a new file name.
8. Click the Options tab.
9. Select the Verify backup upon completion checkbox to verify the backup upon completion.
10. Once all the necessary options are selected, either click the OK button to start performing the backup or check the Schedule checkbox to schedule this operation for periodic execution.

Methods for upgrade

There are 2 methods to upgrade to SQL Server 2005:

- In-place upgrade: SQL Server 2005 takes over completely from SQL 2000. (Easier and faster/complex rollback and cannot upgrade partial systems)
- Side-by-side upgrade: SQL Server 2000 and SQL Server 2005 run side by side in completely separate instances. (more control and no downtime/more time, more intervention and more resources)

Things to do:

Before the upgrade:

- Run upgrade advisor and fix any issue/blocker for upgrade
- Take proper downtime before starting upgrade
- Once all issues / blockers have been resolved make a complete backup of all the databases (including master, model and msdb)
- In case you are planning to use Reporting Services in the future, install IIS
- If the operating system is server 2003, make sure that Windows service pack 1 is installed
- Install prerequisites in the database server (.net framework 2.0 and SQL Native Client
- Download the latest service packs and some cumulative hot fixes
- Make sure there is enough free HDD space
- Remove all fulltext indexes. And afterwords run the script below to remove all leftovers: