Article ID: 314823
Article Last Modified on 12/26/2003
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 64-bit Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q314823
SUMMARY
This step-by-step article describes how to remove a SQL Server service pack and expands on the information contained in the Readme.txt file for each service pack.
When you install a new service pack, the service pack makes changes to the system tables for maintenance reasons, and upgrades user and distribution databases that are members of a replication topology. Due to these changes, you cannot easily remove service packs. There is no automated way to remove a service pack, and the process of removing a service pack involves several manual steps and risks if not done correctly.
back to the top
Backup of System Databases
To remove the new service pack and revert to the build you were running before, you must have a backup of the master, msdb, and model databases from the earlier build to which you want to revert. For example, to revert to the SQL Server 2000 pre-Service Pack 2 (SP2) version of SQL Server 2000 components, you must have a backup of the master, msdb, and model databases prior to the SQL Server 2000 Service Pack 2 installation. If you do not have backups of your system databases on the service pack version to which you want to revert, you must perform the following steps to save your scheduled tasks, Data Transformation Services (DTS) packages, logins and full-text catalogs:
- Script all scheduled tasks (that is, jobs, alerts and operators).
- Save DTS packages to a file. You must save each package one by one into separate files.
- Script the logins and passwords.
- Back up the full-text catalog folders.
For additional information about how to script scheduled tasks and how to save DTS packages, click the article number below to view the article in the Microsoft Knowledge Base:
314546 HOW TO: Move Databases Between Computers that are Running SQL Server
For additional information about how to script the logins and password, click the following article number to view the article in the Microsoft Knowledge Base:
246133 HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
For additional information about backups of full-text catalogs, click the article number below to view the article in the Microsoft Knowledge Base:
240867 INF: How to Move, Copy, and Back Up Full-Text Catalog Folders and Files
NOTE: If you do not perform the preceding steps, you must manually re-create the scheduled tasks, DTS packages, logins and full-text catalogs.
back to the top
Use the following steps to remove the service pack and revert to a prior build:
- Detach all user databases. For more information, see the "Attaching and Detaching Databases" topic in Microsoft SQL Server 7.0 Books Online or the "How to attach and detach a database (Enterprise Manager)" topic in Microsoft SQL Server 2000 Books Online.
NOTE: If any of the databases are involved in replication, you must first disable publishing and distribution. For more information, see the "Disabling Publishing and Distribution" topic in SQL Server Books Online. - Stop all SQL Server services (that is, MSSQLServer, SQLServerAgent, Microsoft Distributed Transaction Coordinator [MS-DTC], Microsoft Search).
- As a safety factor, copy the Data folder to a safe location. If you have data and log files in a separate folder other than the default Data folder, also copy those files.
- Uninstall SQL Server by using the Add/Remove Programs applet in Control Panel. After you remove SQL Server from the computer, Microsoft recommends that you restart your computer to remove files that were in use during the uninstall process.
- Install SQL Server with same character set, sort order, collation and destination folder for program and data files as that of the original installation.
- Apply any service pack that you might have been running before you installed the new service pack. For example, if you want to remove SQL Server 2000 SP2 and you were running SQL Server 2000 SP1 before you installed SQL Server 2000 SP2, then install SQL Server 2000 SP1.
- Restore the master, msdb, and model databases from backup if you have backups that match the version of service pack to which you want to revert. This automatically attaches any user databases that were attached when you created the backup. Attach any user databases that were created after the last backup of the master database.
If you do not have backups of the master, msdb and model databases then:
- Run the scripts that you created to re-create the logins and scheduled tasks (that is, jobs, alerts and operators). If you did not create the scripts before you uninstalled SQL Server, then re-create the logins and scheduled tasks manually.
- Open the DTS packages you saved as files. Save the packages to SQL Server. For more information, see the "How to save a DTS package to SQL Server" topic in SQL Server 7.0 Books Online or the "Saving a DTS Package" topic in SQL Server 2000 Books Online.
- Re-create any changes you want in the model database.
- Attach all your user databases.
NOTE: After you re-create the logins and passwords, your users may not be able to access the database because the users in the database may not be linked to the corresponding login. This problem is referred to as "Orphaned Users." To correct orphaned users, see step 3 in the following Microsoft Knowledge Base article:
314546 HOW TO: Move Databases Between Computers that are Running SQL Server
- If you had full-text catalogs, restore the full-text catalogs and resynchronize the full-text catalogs. For more information, see the following article in the Microsoft Knowledge Base article:
240867 INF: How to Move, Copy, and Back Up Full-Text Catalog Folders and Files
- If you use replication you must reconfigure replication manually.
- If you use SQL Mail, reconfigure SQL Mail. For more information, see the following article in the Microsoft Knowledge Base:
263556 INF: How to Configure SQL Mail
REFERENCES
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
304692 INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP and RESTORE
240872 INF: How to Resolve Permission Issues When a Database is Moved Between SQL Servers
274188 PRB: Troubleshooting Orphaned Users Topic in BOL Incomplete
Additional query words: SP remove return undo revert rollback fallback
Keywords: kbhowtomaster KB314823
