Upgrading SQL Server 2005

Upgrading SQL Server Standard to Developer Edition on Dev and Test servers – Stand alone servers. I have also used this to upgrade back again - Developer to Standard Edition.

Make sure you have backups of your System and user databases before you begin. Take a backup of your system databases before you apply the service pack. Take another backup of them when you have finished the hot fix install.

You should probably reboot the server before you begin to make sure you have a clean slate to work with.

Create a folder called sqlAdmin on the C drive.
Create a sub folder called SQL2005
Create two sub folders called Servers and Tools.
Copy the contents of CD1 into the Servers directory and CD2 into the Tools Directory.

You will also need to copy the latest service pack and hotfixes on to the server ready for installing after the upgrade: an upgrade takes the product level down to RTM.

In the sqlAdmin directory create a text file called upgrade.cmd and add this text to it:

@echo off

Set UPGRADE = SQL_Engine, SQL_Data_Files, SQL_Tools90, SQL_Replication

Set INSTANCENAME=MSSQLSERVER

Start /wait c:\sqlAdmin\SQL2005\servers\setup.exe /qb UPGRADE=%UPGRADE% SKUUPGRADE=1 INSTANCENAME=%INSTANCENAME%
:: END OF CMD TEXT FILE

This assumes no Reporting Services or Analysis Serivces. If you have them you may need to add RS_Server, RS_Web_Interface, Analysis_Server, AnalysisDataFiles to the list in Set UPGRADE=

After the upgrade you need to install the service pack and hot fixes.
Reboot the server once you have finished.

Problems

I had a problem with the upgrade failing when Reporting Serivces was on the server “sql bpa command line has encountered a problem and needs to close”. To fix this I created an extra directory under c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\ called BPAClient and in it I copied the file BPAClient.dll from the c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\BPA\bin\ directory.

BPA failed: http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/2a5f460b-8684-4510-8fcb-1e9d9786baff
 
I have not yet managed to upgrade SSAS - SSRS was fine but SSAS maintains the same version - If I ever work this I'll I will update this post. What I do instead is backup the databases then uninstall the old version and install the new edition.