One of the important responsibility of the SQL Server Database Administrators(DBAs) is to plan and install SQL Server and its patches like Service Packs, Cumulative Updates, Hotfixes, etc. Although how much ever planning and testing was done prior to installation of the SQL Server patches, some times there could be unexpected issues or problems which will affect the applications or certain functionality and there comes a need to rollback the installation changes or to revert back to the before state. Older versions of SQL Server did not had much flexibility in rolling back the service pack changes by uninstallation, but the latest versions of SQL Server provides feature to uninstall SQL Server patches which may be service packs, CUs or Hotfixes. Below are some of the important steps to follow to uninstall SQL Server service packs or other patches starting with SQL Server 2008 R2.
Steps to Uninstall SQL Server Patches on Clustered instance. This is also applicable for standalone instances.
– On Passive node, open Programs and Features” options in Control Panel and click on “View installed updates”
– Highlight the SQL Server 20xx Service Pack x or related patches and click “Uninstall”
– Uninstall Service Pack wizard will start.
– Click “Next” which will run update rules then select the features for which you need to remove the Service Pack and click “Next”
– Go through the wizard and then verify the Summary and click “Remove”
– Monitor for the uninstallation to finish successfully.
– Reboot the node
– Failover SQL Server instance on to the node where the patch has been uninstalled.
– Test the application to make sure it works fine.
– Remove the patches from other passive nodes as well and reboot the nodes.
For older versions of SQL Server 2005 or before, you cannot uninstall using the above method. If the server is a VM, then you may restore the snapshot of VM before the patch install, but this needs to be tested before hand. The only supported and reliable method of rolling back SQL Server patches on SQL Server 2005 or prior is to follow the below steps
– Backup all the system and user databases. Make note of instance level configuration settings, jobs, logins, etc.
– Detach all user databases and copy to another location.
– Uninstall SQL Server 2005 instance
– Reboot the server
– Install new SQL Server 2005 instance with same name
– Apply the service pack or CU to same level to the same when the SQL Server instance was working fine.
– Restore Master and MSDB databases and restore Model database as well if required.
– Restore all user databases or attach the user databases using mdf and ldf files which were copied to another location before.
– Verify logins, jobs, instance level configuration, etc.
– Test the application to make sure everything works fine.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.