service-packsql server
I want to list all the SPs and CUs applied to a SQL Server 2008R2 instance.
Example:
How can I get the above list from SQL Server 2008R2 instance?
Yes, you can choose which instance(s) you wish to apply the service pack. They'll all be checked by default; as will the client tools / shared features (there is only one "instance" of those):
You can re-run the installer later to update any that you didn't update this time; the ones that are already at SP2 will not be selectable.
On this VM, I have 2008 R2 RTM (no patches whatsoever) and 2008 R2 with Service Pack 2 and CU9, installed and running side by side with no issues:
Now, why you want to have a mix of patched and unpatched instances on a single server might be a different question...
I am a big advocate of keeping up to the date with the most recent cumulative update, but only if your testing/QA cycle can ensure full and proper regression testing against it. Glenn Berry of SQLskills is also a proponent of this approach.
Microsoft's own recommendation is to only apply CUs that fix issues that affect you, though they have loosened that stance recently. The problem is that you may be affected by one or more of those issues and not know it, or you might be affected tomorrow even if it hasn't hit you yet. Are you going to go through and try to reproduce the issue behind every single fix in every single CU for your branch? Are you going to do this continuously to make sure you still aren't affected?
I'll be quite honest: I've never had an issue applying any CU to my instances. And in fact their CU release process has been a lot more reliable than the service pack release cycle, and in many cases (including most recently with SQL Server 2012 Service Pack 2), you don't want to apply the service pack until the first CU for that branch has been released anyway. In this case there is an interim hotfix to resolve the issue that wasn't fixed in time to make the Service Pack code, but that isn't always true.
Best Answer
Cumulative updates are exactly that, cumulative. CU2 for example contains all of the fixes included in CU1. As such, the only SP or CU that matters is the last one to be applied which you can determine with:
and Google or a reference list of SQL Server versions. SQLServerBuilds is the popular unofficial list, KB321185 is the official list.
I'm not aware of any way to obtain a complete list of all patches that have ever been applied to an instance. It's likely buried in the registry somewhere but I haven't fished for it.