Best practices for installing service packs, cumulative updates and hotfixes for SQL Server

"Test on a test/dev SQL Server first and only after you have confirmed that all applications are working as expected then install it on a production SQL Server.

Review the Readme for Service Pack/cumulative update/hotfix. Any concerns/recommendations will be found in the readme.

Run DBCC CHECKDB on ALL databases (user and system databases) and ensure that there were no errors reported.

Backup ALL databases (user and system databases) and full-text catalogs (if applicable). This is NOT required but highly recommended.

Stop Monitoring and Anti-virus services

Make sure you have the proper permissions to install (administrative privilege on server/cluster node)

The below points are for clustered SQL Server instance:

Make sure ALL SQL resources come online on ALL cluster nodes

Make sure that ALL disk resources (even the ones that not being used by SQL Server) are online and not in failed state.

Verify that there are no dependencies other than those created by the SQL Server setup on any SQL Server cluster resources.

Run MPSRPT_SQL.exe on all cluster nodes (not required but recommended)

Make sure all remote desktop connections are closed. You can connect to the node (you are running the setup from) using remote desktop connections but you should disconnect any remote connections to other cluster nodes."

More info: http://adf.ly/A7Uw6

"Cannot perform a differential backup for database... ...Perform a full database backup..."

After installing SP2 on MSSQL 2005 you may get the folowing error:

"Cannot perform a differential backup for database "abc",
 because a current database backup does not exist.
 Perform a full database backup by reissuing BACKUP DATABASE,
 omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)
 BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)."

More info: http://adf.ly/A76Se

Just set the SQL VSS Writer service to manual and restart SQL agent service:

Installing SQL SERVER 2012

"Features Added in SQL Server 2012 Installation

Operating System Requirement – SQL Server 2012 is supported only on Windows 7 , Windows Server 2008 R2 & above. SP1 is an mandatory requirement for OS else you won’t be able to proceed with installation.
Business Intelligence edition – This new edition supports BI related stuffs so it’s one power pack for your BI needs. For more details refer KB article http://technet.microsoft.com/en-us/library/ms144275%28v=sql.110%29.aspx
Enterprise Edition – You can now differentiate Enterprise edition based on licensing. CAL & Core based licensing are available for Ent Edition. If you noted I have mentioned it as Core based. Yes!! from 2012 the licensing model changes from processor to core.
Product Update – When you proceed with the installation you have the new feature to download updates for SQL Server during installation itself. This feature is like slipstream.
Server Core Installation – Now you can install SQL Server 2012 in Windows Server 2008 R2 Server Core (SP1 is required)
Multi-subnet clustering – Yep!! you are right now you can have your cluster nodes in different subnet masks, it’s supported in SQL Server 2012 clustering
SMB file share – System & User database can be placed in SMB file share. Incase of disk space issues, you can move your db files to file share!
Tempdb placement in Cluster – You are now allowed to place tempdb data files to a local drive in SQL Server cluster it’s not necessary to keep it in SAN disks.
Managed Service Account – Individual service account for all SQL Server related service to enhance security and isolate each service from one another."

Hardware and Software Requirements for Installing SQL Server 2012: http://adf.ly/A7U73

More info: http://adf.ly/A7UAD