Rolling back a previously applied SQL Server patch package

You can roll back a Microsoft SQL Server patch package that has been applied to a node, and return the node to the previous patch level.

A node might host:

• Both primary and secondary instances of multiple clustered instances
• Both primary and secondary replicas of multiple availability groups
• A combination of the preceding types

BDA does not reboot such nodes during patching. The following reason appears in the Middle Tier:

<yyyy/mm/dd> HH:MM:SS [WARN] GA.MiddleTier.Process.RunMssqlPatch (pid: <processID> - line: <lineNumber> - file: <fileName>) These nodes will not be considered for reboot before or after patching or rollback activity: <nodeName1> <nodeName2> <nodeName3> ...
The reason being one of the below:
1. These nodes host both primary and secondary instance of multiple clustered instances
2. These nodes host both primary and secondary replicas of multiple availability groups
3. Combination of 1 and 2

Before you begin

• The Can Rollback option is selected in the attributes for the package.
• The host or database is running the latest hotfix.

To roll back an SQL Server patch package that has already been applied to a node

1. From the Management Console main page, select Patching > Patch Repository in the Context Frame.

2. In the Patch Repository page, select the patch package you want to uninstall, and select Rollback from the list box for that row. In the MS SQL Server Patch Rollback Candidates page, populate the following fields, and click Next.

   Patch Candidate field	Description
   Remote Account Information	Type a valid domain administrator account domain, user name, and password in the Remote Account Domain, Remote Account Username, and Remote Account Password fields. Note This field is enabled only if you select a SQL Server Instance on a WSFC node. If you are patching stand-alone SQL Server Instances on non-clustered nodes, BDA will not prompt you for credentials to a domain administrator account.
   Remove Pre-Staged Patch (BDA version 8.9.02)	Select this option if you want to remove the pre-staged patch media after installation of the patch irrespective of whether the installation was successful or not.
   Reboot Node If Required (before rollback) (2020 Release 01)	Select this box to reboot the nodes before rollback. Notes This field is enabled only if you have reboot node permission. This field is disabled if you select Use /ALLINSTANCES.
   Reboot Node (after successful rollback) (2020 Release 01)	Select this box to reboot the nodes after successful rollback. Notes This field is enabled only if you have reboot node permission. This field is disabled if you select Use /ALLINSTANCES.
   Template	Displays the default BDA template selected from the Default Template field in the Edit Existing Package wizard. Optionally, you can select a template for this operation. See Managing templates for SQL Server. If you select a template, you can click Skip Ahead to advance directly to the next step that requires input. An information message appears in the step, in green type, listing the fields that require entries before advancing to the next step.
   Use /ALLINSTANCES (Applicable for version 8.9.01 and later)	Select this check box to use the ALLINSTANCES parameter when running the SQL Server patch installer to rollback the patch to all applicable instances of SQL Server on a given node rather than rollback a patch to each instance individually. For more information about this parameter, see https://msdn.microsoft.com/en-us/library/dd638066.aspx. Notes It is required to have more than one candidate instance for a given node and you must select all the candidate instances on that node for BDA to use ALLINSTANCES to rollback a patch to the instances on a node. BDA will not proceed unless this option is disabled. The pre and post patching scripts in this patch package still run individually for each instance at the appropriate time. This option is available only to users having the "SQL Server Patch with /ALLINSTANCES" capability, see Capabilities tables. During the process of rollback a Clustered SQL Server Instance using ALLINSTANCES parameter, active instances are failed over in the middle and at the end of rollback. After the roolback has been done, BDA restores these instances that were active when rollback started. (2020 Release 01) When you select this field, the following fields are disabled: Reboot Node If Required (before rollback) Reboot Node (after successful rollback)
   Instances	Select the instances for which you want to roll back this patch package. The candidates for this patch package are determined by the patch version number defined when you created patch package. Note The candidates list does not get refreshed automatically. Click the Reload Candidates button to refresh the list.

3. You can view a list of the targets that were excluded from the roll back candidates list and why specific databases did not meet the criteria for rollback by clicking Excluded Candidates. 

4. On the Custom Fields page, populate the custom fields you have defined in the template, and click Next.

   Note

   This page appears only when in the BMC Database Automation XML template, either custom fields are defined used for this activity or NEW IN 8.9.03any custom fields are defined for the scheduling options (present on the Scheduling page in this wizard). If you have defined custom fields, value of those options on the Scheduling page will be derived from these custom fields. For more information, see Adding custom fields to a template.

5. In the Job Options page, specify any of the following Job options, and click Next.

   Type	Option	Description
   Notification Emails	Email List	(Optional) Click Add Email to add email addresses to the notification list.
   Job Administration	Cleanup Agent Logs	(Optional) Select to automatically remove logs that are generated by BMC Database Automation on the Agent after the job is complete.

6. In the Scheduling page, select when to run this job in the Job Execution field, and click Next.
   NEW IN 8.9.03If you choose to run the job now or at a future time, optionally, you can use the Set Maintenance Window option to specify the Maintenance Window Start Time and the Maintenance Window End Time during which the job should be scheduled and running.

7. If the Change Control page displays, populate the following fields, and click Next.

   Section	Field	Description
   NA	Bypass Change Control	(Optional) Select to bypass using BMC Remedy ITSM to control the change process. Selecting this option removes the rest of the fields from this page.
   NA	Change Control Options	Select to determine whether to open a new BMC Remedy ITSM change ticket, or to use an existing BMC Remedy ITSM change ticket to control the change process. Selecting Use Existing Change Ticket displays the fields in the Use Existing Change Ticket section on this page. Selecting Create New Change Ticket displays the fields in the Create New Change Ticket section on this page.
   Use Existing Change Ticket	ITSM Change ID	Specify the BMC Remedy ITSM change ticket number to associate with this change process.
   NA	ITSM Task ID	Specify the BMC Remedy ITSM task ID number to associate with this change process.
   Create New Change Ticket	Change Type	Assign the type of change for the new BMC Remedy ITSM change ticket.
   NA	Change Impact	Assign the impact level of this change process for the new BMC Remedy ITSM change ticket.
   NA	Change Urgency	Assign the urgency level for the new BMC Remedy ITSM change ticket.
   NA	Change Risk-Level	Assign the risk level of this change process for the new BMC Remedy ITSM change ticket.
   NA	Change Class	Assign the change class for the new BMC Remedy ITSM change ticket.
   NA	Change Summary	Type a summary of the change process.

   Note

   The Change Control page appears only when change control is configured for your environment. See Configuring change control.

8. In the Summary page, review the information.
   To make changes, do the following:
   1. Click Go to to return to the provisioning step that you want to edit.
   2. Make your changes.
   3. Click Save and Review to return to the Summary page.
9. Specify your verification preferences.
   • To continue with database creation after the pre-verification tests have been successfully run without manual intervention, select Automatically Continue If All Tests Succeed.

   • To create the database without having first run the pre-verification steps, select Skip Pre-Verification Tests.

     Note

     The Skip Pre-Verification Tests option should only be used when you are certain all tests can succeed. The option skips verification and advances directly to the actual provisioning activity.

10. Click Execute Job or Schedule Job (depending on your selection in the Scheduling page).