How to Check SQL Configuration in a Database Backup for SQL Server 2016

Learn the steps to diagnose SQL Server 2016 issues by checking configurations in a master database backup.
---
This video is based on the question https://stackoverflow.com/q/64159819/ asked by the user 'James S.' ( https://stackoverflow.com/u/10701175/ ) and on the answer https://stackoverflow.com/a/64159996/ provided by the user 'SQLpro' ( https://stackoverflow.com/u/12659872/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Checking SQL Configuration in Backup

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Diagnosing SQL Server Configuration Issues in Backups

When working with SQL Server 2016, one may encounter issues after a software update that affects the server's configuration. If you suspect that changes to the server settings have led to a problem, such as the "Protocol error in TDS stream" often associated with network packet size, you may need to investigate the server's configurations. By comparing the live master database to a backup, you can identify any discrepancies that may have occurred. This post will guide you through the process of checking SQL configurations in a master database backup.

Understanding the Importance of SQL Server Configuration

SQL Server configurations play a critical role in the performance and stability of your database operations. For instance, settings like network packet size can significantly impact data transmission and, if not set correctly, can lead to various errors. It's crucial to ensure that your configurations remain consistent, especially after updates or changes to the system.

The Challenge: Checking Configurations in a Backup

To accurately diagnose configurations, many users turn to the system stored procedure sp_configure, which allows you to view the server settings. However, checking configuration settings directly from a backup of the master database requires a slightly different approach. Your live database reflects the current state, while the backup contains the state at the time it was created.

The Solution: Comparing Configurations

Here's a step-by-step method to compare the configurations between your live database and your master database backup:

Step 1: Copy System Views

Creating a Comparison Database:

Start by creating a new database that can hold the configuration data from your master database. This allows you to maintain a record of the configurations over time.

Extracting Configuration Data:

Regularly copy specific system views from the master database (which is, in fact, the mssqlsystemresources database) into your newly created comparison database. You can do this by executing SQL queries that capture details from useful system views such as sys.configurations.

Here's an example SQL query to copy the configurations:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Querying the Configurations

Reviewing the Current Settings:

After you have copied the configurations into your comparison database, you can query both the live database and your backup by using the following SQL commands:

[[See Video to Reveal this Text or Code Snippet]]

Step 3: Analyzing Differences

Comparing Results:

Utilize the results of these queries to compare the configurations. Look for discrepancies in critical settings, particularly those known to affect connection stability, such as network packet size.

Final Thoughts

By following these steps, you can effectively diagnose issues within SQL Server 2016 caused by configuration changes after software updates. Keeping a regular backup of the master database and keeping track of system configurations in a structured manner will help maintain performance and prevent unexpected errors.

If you're ever uncertain about the impact of certain configurations, don't hesitate to consult SQL Server documentation or communities for additional guidance. Staying informed and prepared will empower you to troubleshoot and resolve issues much more efficiently.