DEV Community

nisargupadhyay87
nisargupadhyay87

Posted on

How to Patch SQL Server Safely and Handle Unexpected Database Issues Post-Patching?

Microsoft regularly releases security packs, cumulative, and other updates for MS SQL Server to add new features, fix performance issues, and bugs in the server. Before SQL Server 2016, the company used to release service packs and cumulative updates regularly. However, it changed its servicing model with SQL Server 2017. It now releases only cumulate updates (CUs) and General Distribution Releases (GDRs) every 2 months.

Unpatched SQL servers are prone to SQL injection attacks, cyberattacks, compliance issues, and more. So, it is recommended to install service packs, cumulative packs, and general distribution releases as and when available. Installing such updates timely ensure a secure, stable, and high-performance SQL Server environment. In this article, we will discuss the stepwise process to patch the SQL Server safely. We will also discuss some common database issues that you may encounter after patching the server and explain how to handle them.

Stepwise Process to Patch the SQL Server Safely

Before patching or installing the updates, consider the following:

-Check the SQL Server version you are updating. To do this, you can run the below query:

SELECT @@VERSION version
Enter fullscreen mode Exit fullscreen mode
  • Ensure that the version of SQL server that you are updating is compatible with the available service packs or cumulate updates.
  • Back up your SQL databases before installing the updates. This will help you restore the databases in case something goes wrong.
  • Running agent tasks can disrupt the patching process. So, temporarily disable the SQL agent tasks till the patching is complete.
  • Stop the SQL Server (MSSQLSERVER) service. To do this, open the SQL Server configuration Manager, click SQL Server Services, right-click on SQL Server (MSSQLSERVER), and click Stop.

  • Always perform the process in a test environment first.
    You can now download and install the latest updates according to your SQL Server version. For this,

  • Go to the Microsoft page to know the latest updates and version history for SQL Server.

  • According to your SQL Server version, download the patches/updates. For example, download only the cumulative updates, if you’re using SQL Server 2022. Download both service packs and cumulative packs if you’re using SQL Server 2016 and earlier versions.

  • Read and accept the License terms.

  • Select the SQL Server instance you want to update. Confirm the installation path.

  • Check and confirm the installation options.

  • Start the installation process.

  • Wait till the installation process is complete. Then, restart the SQL Server (MSSQLSERVER) service.

  • Verify the update by executing the SELECT @@version query.

Common Issues You can Face After Patching the MS SQL Server

After updating the SQL Server, you may encounter various issues and errors, like error codes 3417 and 917. These errors are associated with database script update failure, corruption, and inconsistences in SQL database files.
After installing the updates if the upgrade script fails, you can check the error log for the related error messages and then troubleshoot the upgrade script failure. You can also use the trace flag 902 to start the MS SQL Server.
If you see the SQL Server error 3417 after patching the SQL Server, this indicates that there is corruption in the database files. In such a case, you can restore the master database from backup or try rebuilding the master database file. To rebuild the master database, you can use the following CMD command:
Note: Make sure to read the limitations and prerequisites of rebuilding the SQL master database.

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName ]
Enter fullscreen mode Exit fullscreen mode

If the errors have occurred due to corruption in MDF files, then you can run the DBCC CHECKDB command to repair the files. Here’s the syntax:

DBCC CHECKDB    
    [ ( db_name | db_id | 0   
        [ , NOINDEX    
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]   
    ) ]   
    [ WITH    
        {   
            [ ALL_ERRORMSGS ]   
            [ , EXTENDED_LOGICAL_CHECKS ]    
            [ , NO_INFOMSGS ]   
            [ , TABLOCK ]   
            [ , ESTIMATEONLY ]   
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]   
            [ , MAXDOP  = number_of_processors ]   
        }   
    ]   
]
Enter fullscreen mode Exit fullscreen mode

Repairing the MDF files using the DBCC CHECKDB command may cause data loss. Alternatively, you can use a professional SQL repair tool, like Stellar Repair for MS SQL, to prevent data loss and quickly repair the SQL database. Its advanced algorithms can repair the MDF/NDF files easily and recover all the objects without any data loss. It can help resolve database corruption errors that may occur after updating the server. The tool supports all versions of SQL Server, including MS SQL Server 2022, MS SQL Server 2019, and MS SQL Server 2017.

Conclusion

You must always install the latest service packs or cumulative updates (when available) to protect the SQL Server from threats and malicious attacks. Above, we have discussed the stepwise procedure to patch the MS SQL Server. We have also mentioned some common issues that you may face after patching or installing the updates. However, if the database gets corrupted after patching the SQL Server, then you can restore the database from backup. If the backup isn't available, use Stellar Repair for MS SQL to repair the corrupted MDF/NDF files and recover all the data.

Top comments (0)