SQL Server Service Pack or Hotfix patch or Cumulative Update: do not attempt Windows System Restore as a backout plan?
To cut the long story short, during a recent implementation of Cumulative Update 6 and 7 on a particular 'application domain' server we have had major application issues due to a mix up of application code & these SQL cumulative update fixes. Actually this server was not under our support structure that would definetly go through thorough testing cycles for any hotfix or service pack patch application, due to the road blocks and issues we were involved to provide recovery tasks.
I have highlighted and would like to stress that at any point of time it is always recommend that you test hotfixes before you deploy them in a production environment. Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Due to the application campaign previously the cumulative update package 6 wasn't applied to this SQL Server database, as per the list of hotfixes that are included in CU7 for SQL Server on the KBA 949095 information the application team were able to obtain the hotfix from CSS, that is another story. But the main problem occurred as they haven't tested the reporting part of the code that has caused this downtime to the application.
Being the reason of shared platform database server the meeting has included technical teams from Application, Database & Server based people and the discussion has brought up the suggestion of performing the 'Windows System Restore' on this Windows 2003 server. I would like to run through the basics behind 'system restore' concepts that were introduced from Windows XP time. Based on the home based work & tasks using Windows XP (with VIsta too) has that time machine facility such as when some awful thing gives your computer the staggers, it's easy to go back to the day before. Or the day before that. Or last week or last month. Few things to note for the users:
- System Restore creates points in time — called "restore points" — in which it takes a snapshot of Windows. It stores them on your hard drive. At any given time, you might have restore points going back a few weeks, or a few months.
-
System Restore only takes Windows and some programs backwards. This is important to know: It does not reverse any changes made in your documents.
-
The System Restore monitors the files that are important to the system's performance. These have such extensions as EXE, DLL and INI. When you select a date in the System Restore wizard, it restores these files to whatever they contained on that date. If you installed a program after that restore date, you might have to reinstall it.
-
Windows creates restore points under several circumstances. For instance, when you install something from a CD or floppy, it usually creates a restore point. It makes points every 24 hours. Or, if you don't use your computer that often, it creates restore points when you boot up.
-
Never forget the fact that such system restore chekpoints and tasks you need free space in hard drive (atleast 12%) of total disk size.
Coming back to the subject topic that suggestion of performing the 'Windows System Restore' on this Windows 2003 server is not possible directly unless you perform few tweaks to enable on Windows 2003 and I have totally opposed to this suggestion by System Administrators. As per the behaviour of System Restore which will not remove binaries from locations on your disk that Windows isn't deemed to "own" that is applicable to SQL Server binaries too such as shared applications folder. This will have major issue on applied SQL instance of causing unstable state or even corruption to system database where you will have major downtime to recover the complete server platform.
I have recommend(ed) and strongly propose to follow the process of using the CU uninstaller utility in Add/Remove Programs instead of trying to "roll back" method. Since the CU6 implementation and availability anyhow you have to approach CSS to obtain the hotfix, they will provide the steps to rollback/uninstall the Cumulative Update hotfix to your SQL instance. TO close this topic if you need to know the SQL Server 2005 builds that were released after SP2 for SQL Server 2005 refer to this KBA 937137 link.