Uninstalling SQL Server Service Pack or Hotfix

Prior to SQL Server 2008, if you had to rollback a Service pack or hotfix installation, it was a long and hectic process as it involves rebuilding the SQL Server and restoring the user and system databases and setting up jobs, logins,etc... And in the meantime if you have missed backing up something then you are gone!
Starting from SQL Server 2008, Service Pack 1, microsoft has introduced an option to uninstall the service pack or an hotfix. It is as simple as uninstalling an application from your computer.
To uninstall an SQL Server service pack or hotfix,
Go to "Control panel" --> "Uninstall a Program" --> Click on "view installed updates" on left hand side
Choose the update you want to uninstall and click "Uninstall" option.

Un-Installing Service Pack for SQL Server

Unlike any other software, SQL Server also allows uninstalling of the Service packs. This is a feature that was introduced from SQL Server 2008 SP1 and above and here is how you do it.
Before you do the activity, it is recommended to take a full instance backup just in case to recover if anything goes wrong.

Check the Current Service pack level of the SQL Server Instance.

Go to "Programs and Features" options in Control Panel and click on "view installed updates"

Highlight the Service Pack you want to uninstall and click "Uninstall"

The uninstall Service Pack for SQL Server wizard will now come up and click "Next" here once the update rules have been verified.

Select the features for which you need to remove service pack and click "Next"

Once the file check is completed, Click "Next"

Verify the Summary and click "Remove"

Wait for the process to complete

Once this is completed, Restart the computer to finish the SQL Server Setup.

Now verify the changes

A failure was detected for a previous installation, patch, or repair during configuration for features...

You receive the following error when you try to apply an update or service pack for a SQL Server Instance.

TITLE: SQL Server Setup failure. ------------------------------ SQL Server Setup has encountered the following error: A failure was detected for a previous installation, patch, or repair during configuration for features [SQL_Engine_Core_Shared,SQL_PowerShell_Engine_CNS,CommonFiles,CommonFiles_Loc,]. In order to apply this patch package (KB2528583), you must resolve any issues with the previous operation that failed. View the summary.txt log to determine why the previous operation failed. Error code 0x84B20001. ------------------------------ BUTTONS: OK ------------------------------

This happens when you try to install an update or service pack or do an edition upgrade and if that action does not complete successfully.
In my case, I recently tried to do an upgrade of SQL Server 2008 R2 Evaluation version to SQL Server 2012 Evaluation edition and since this upgrade path was not supported, the installation gave an exception which forced me to quit the installer.
After this I tried to install SP1 for SQL Server 2008 R2 and I got this error.

To Fix this issue, you have to "Repair" the existing SQL Server Instance. This option is available in "Add Or Remove Programs" feature of Control Panel.

Once the Repair is completed, you can restart the installation of Service Pack.

Microsoft SQL Server 2008 R2 Service Pack 2 is now available for download

SQL Server 2008 R2 Service Pack 2 (SP2) is now available for download. This Service Pack contains Cumulative Update 1 to 5 from SQL Server 2008 R2 SP1. The package can be used to upgrade the following SQL Server 2008 R2 editions: 

• SQL Server 2008 R2 Parallel Computing Edition
• SQL Server 2008 R2 Datacenter Edition
• SQL Server 2008 R2 Enterprise Edition and Developer Edition
• SQL Server 2008 R2 Standard Edition
• SQL Server 2008 R2 Web Edition
• SQL Server 2008 R2 Workgroup Edition
• SQL Server 2008 R2 Express Edition

And it can be downloaded from the here.

Order of Installing Service Pack on systems hosting Database Mirroring

If you have servers which hosts database mirroring and you want to install the service pack on those systems and you are trying to find out the order in which you need to do it then, here is the order in which you need to install service pack or a hotfix on the systems which hosts Database Mirroring.

1. Backup the principal
2. If you are using a witness, remove it from mirroring
3. Upgrade the mirror
4. Failover to mirror
5. Upgrade original principal/current mirror
6. If you wish to fail back to original principal continue on; otherwise, proceed to step 8
7. Failover to original principal
8. If you are using a witness, add it back into mirroring

For more information refer to the following article to know the entire process for upgrading both the principal and mirror servers.
http://technet.microsoft.com/en-us/library/bb497962(SQL.90).aspx

SQL Server 2008 Service fails to start after Service Pack Installation

Some times you may find that the SQL Server Service is not starting after applying a service pack and when you check in the Event Viewer you find the below message.

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

SQL Server Setup creates a database with the data file name temp_MS_AgentSigningCertificate_Database.mdf during the installation process and if the SQL Server setup is not able to create that database in the default data path then the above error is returned as it is not able to find the path.

To fix this issue.

1. Go to Registry editor, To open this, go to "Run" and type "regedit" and click "ok"
2. First go to this path and make sure that the path in the key SQLDataRoot exists. If not then give a valid path to this key.
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<instance name>\Setup
3. Then go to this path and make sure that the path in the keys "BackupDirectory", "DefaultData" and "DefaultLog" exists. If not then give a valid path to these keys.
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<instance name>\MSSQLServer
4. Now you should be able to start the SQL Server Service without any issues.

Once the Service is started, verify the SQL Server, databases and others to make sure everything is fine.

Also verify if the Service pack or the CU is installed correctly.