SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
How to Upgrade SQL Server Instances Quickly and Quietly in Ten Simple Steps
Web Monkey About the Author

With security clearance and well over a decade of experience with SQL Server, Web Monkey is the resident DBA at Norb Technologies and a real database guru.

A regular contributor to SQL Server Club, his background includes software development, hardware, a smattering of essential networking knowledge and an unhealthy interest in web security. He has an obsession with automating repetitive tasks, but has a tendency to forget where he left his last open bag of fruit and nut...

Typepad Profile RSS Feed Web Monkey on Twitter
Send to a Friend Printer Friendly Version
Introduction
If you have more than one instance of SQL Server on a box then you'll need to apply a service pack to each and every instance. So, if you have two instances, you'll have to do this twice. But there is a quicker way. Follow this really simple guide to applying a service pack to multiple instances and really save yourself some time. Let's get started!
  1. Download the latest SQL Server service pack. The exact location varies from release to release, but a good place to start is www.microsoft.com/sql. For this example we're going to use SQL Server 2005 SP2a, which is 282MB, so enough time to make a coffee whilst it downloads.

  2. Once the download has finished, double-click on the executable, which has probably be named something like SQLServer2005SP2-KB921896-x86-ENU.exe.

  3. You'll see a dialog box which looks something like this:

    Extracting file to directory

    Make a note of the "To Directory" folder name, you'll need this shortly.

  4. You will also see the "Welcome" dialog pop up. Although it's tempting to close this, leave it open until we're finished, as whilst it's open it allows us to get direct access to the juicy setup files we're going to need to perform the multi-instance upgrade. As soon as you cancel out of this dialog, the files and folder they sit in are deleted from the disk.

    The SQL Server 2005 SP2a Welcome Screen

    You may notice that although we are installing SP2a in this case, Microsoft have not changed the Welcome screen from SP2...

  5. Open up the folder that the service pack has just been unzipped to, and copy the folder name out of the Address bar:

    Open the folder where the service pack files have been extracted to

  6. Open a DOS (or "Command") box by going to Start, Run and then type cmd. Change the directory to the folder that the setup files reside in by typing:

    cd C:\5e4c74508dd9aa22dfab6f685d2c60

    or whatever the name of the folder is where you extract the setup files to.

    Change the directory to be the same as the folder where your files are

  7. If you now type "hotfix /?" in the DOS box you will see the following dialog box:

    Usage options for running the upgrade on multiple SQL Servers

    Don't be put off by the word hotfix; this really is a Service Pack, it's just that some bright wag at Microsoft decided that the main executable would be called hotfix.exe instead of something more descriptive.
  8. At this point you can probably work the rest out. To upgrade all of the SQL Server instances on your current server, simply return to your DOS box and run something like:

    Hotfix.exe /allinstances

    To run the Service Pack upgrade without a GUI front end and more importantly, without having to interact with it as it does its job, run it in quiet mode:

    Hotfix.exe /quiet /allinstances

    This also has the added advantage that the time taken to apply the service pack is much quicker than if you run it normally, as it does not have to constantly update the progress bar on the front end. For some reason this adds minutes to the upgrade process.

    TIP: As a general rule I always log in to the server being upgraded using the account which the SQL Server database engine service runs under, rather than my own Local Admin account. Although a local admin account should work just fine, I have occasionally seen problems upgrading a server, but running it after logging in with the service account seems to get around this problem.

  9. Finished? Great! Copy those files across to your next server and run it again. Once the copy is finished, you can go back the Welcome dialog box and click on cancel, at which point all of the files and folder will be cleaned up on the local server, unless you’ve purposely made a copy of them.

  10. And of course if you're feeling particularly adventurous, you could be extra clever and do all of this from one central server, using something like psexec, but I'll leave you to figure out how to do that as it's a whole other article!
If you found this article useful, please visit our sponsors who keep us going...

SQL Enterprise Monitor 2009
This tool gives you unlimited server monitoring at a really great price!

Ensure critical services are running, compare enterprise-wide security and configuration settings, find troublesome queries and resource-intense database activities.
Download
Send to a Friend Printer Friendly Version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form