It's hard to believe, but it's service pack time already. Service Pack 1 (SP1) for SQL Server 2000 installs like earlier service packs; however, in addition to the service pack for the database engine and SQL Server 2000 Analysis Services, SP1 includes a service pack component just for Microsoft SQL Server Desktop Engine (MSDE) installations. As you do with all service packs, read the readme.txt file before you start. Here are seven essential steps that can make your SQL Server 2000 SP1 installation as smooth as possible.

1. Back Up SQL Server Databases


Always perform a full system backup before you install any system upgrade. Although installing SP1 doesn't change user databases, it does alter the master and msdb system databases. You'll need backups of these databases if you want to uninstall SP1 later.

2. Back Up Analysis Services Databases


You can back up Analysis Services databases by archiving the \Microsoft Analysis Services\data folder. Also, back up the registry entries at HKEY_LOCAL_MACHINE\SOFTWARE\OLAP Server.

3. Enable Autogrow for Master and Msdb


The master and msdb databases require a minimum of 500KB of free disk space. To make sure that you have the room, run Enterprise Manager, right-click the msdb and master databases, and select the Data Files option. Then, select Automatically grow file.

4. Stop SQL Server Services and Other Database Apps


Because SP1 updates the SQL Server executable files, make sure that active applications haven't locked these files and other database connectivity files (such as the ODBC drivers). Before applying the service pack, use the SQL Server Service Manager to stop the following services: MSSQLServer, MSSQLServerOLAPService, SQLServerAgent, and Microsoft Distributed Transaction Coordinator (MS DTC). Stop related services, and stop ODBC and OLE DB applications such as Active Server Pages (ASP) applications.

5. Install SQL Server 2000 SP1 Database Components


To install SP1 for SQL Server 2000, run the setup.bat file, which is in the service pack CD-ROM's root directory or in the root directory to which you expanded the service pack files from the sql2ksp1.exe archive. To run the installation, you must use an account that has sysadmin authority.

6. Install Analysis Services SP1 Components


You install SP1 for Analysis Services by running the setup.bat file, which is in the service pack CD-ROM's \msolap\install subdirectory or in the \msolap\install subdirectory to which you expanded the service pack files from the sql2kasp1.exe archive. Again, use an account that's included in the sysadmin role.

7. Restart SQL Server and Analysis Services


After applying SP1, use the Control Panel Services applet to restart MSSQLServer, MSSQLServerOLAPService, SQLServerAgent, MS DTC, and Microsoft Search. Then, restart your database applications.