How to Upgrade SQL Server Instances Quickly and Quietly in Ten Simple Steps

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:

   

   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.

   

   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:

   

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.

   

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

   

   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!

End of Technical Article

For further essential guides, technical articles, blogs, newsgroups, forums, links and more, please visit the SQL Server Club site at www.sqlserverclub.com