Upgrading SQL Server 2005

Posted: September 11, 2009 in SKUUPGRADE=1, SQL Server 2005, SQL Upgrade

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 creted an extra dirctory 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.

Advertisements
Comments
  1. […] This link will take you to my previous post on upgrading SQL Server 2005 Share this:ShareLinkedInTwitterFacebookPinterestLike this:LikeBe the first to like this. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s