Category Archives: Tips

Determine database version from a bak file

Have you ever imagine you can determine the version of SQL Server from a bak or a MDF file? I have been in situations where I received a bak or MDF and LDF files from a vendor to restore \ create a database for the implementation of a new application, most of the times I receive complete detail of the supported versions of SQL Server by the product but what happens if I don’t get that information?

SQL Server offers a way to get the actual version where the files were created, let’s review the process to using a database restore.

RESTORE HEADERONLY
This command does not restores the backup as the name suggest, it just returns metadata (information) about the backup file you are examining.

Syntax:

 RESTORE HEADERONLY FROM DISK = 'physical_backup_device_name'

Here is an example of the RESTORE command and its output with two different files:

The RESTORE command returns a large set of columns (+50) with greater details of the backup file that could be very handy for other kind of scenarios as well. But for now, let’s focus on the DatabaseVersion column which according to Microsoft Docs its just an INT value that represents the database version:

Taking the values from the two RESTORE examples above, the 706 and 782 values doesn’t say too much. After doing a research about the DatabaseVersion column, I didn’t find an official source from Microsoft to explain what these values are. To my surprise I found the information I was looking for from a source that I particularly like and use very much to check SQL Server build numbers, patches and CUs; a website called SQL Server builds

If we look at the values of the Internal Database Version column from the image above, we can easily match the backup file with the SQL Server version where the database was created. In this case the backups I used for my examples were created on SQL Server 2012 (706) and 2014 (782) and this is correct indeed because I used the Adventure Works sample database from Microsoft GitHub repository.

Thanks for reading!

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.

Starting SSMS with a specific connection and script file

Have you ever wanted SSMS to start and automatically and establish a connection to your DBA \ hub server and have your daily routine queries already open ready to run?

Good news, that is totally possible. SSMS actually uses the SSMS.exe in the background which has multiple options such as connecting to a specific SQL Server instance and opening a file. All these options are accessible through a few very simple command line parameters, if you are familiar with SQLCmd these parameters will like be really similar to you.

Let’s explore the different options available through command line, open PowerShell or CMD and running the following command:

 ssms -help 

As you may noticed a pop-up window showed up displaying the multiple options as server name, database name, user password, file name, log name, etc..

Now that we know the parameters, let’s have fun with these parameters!! Do you remember what I just mentioned at the beginning of this post? Let’s do a test, let’s call SMS from PowerShell and ask it to open a connection to a specific server (MASTER01 in this case) loading a SQL script file which contains a daily script:

Click for fullscreen

Ok, but you know what let’s make this more interesting, because we know already SSMS.exe it’s an executable and we have all these cool parameters to play with let’s modify the existing SSMS shortcut this time we will make SSMS to load a solution containing multiple SQL script files:

Click for fullscreen

As you may noticed from animation, I just added a set of specific parameters to the existing text, to be specific this is what I just added:

 F:\DailyChecks\DailyChecks\DailyChecks.ssmssqlproj 
-S master01 
-d master 
-nosplash

This modification will make SSMS to start not showing the traditional splash screen (because yes I want to save a second of my time), it also pre-loads a SQL Server solution which contains a collection of scripts I intents to run from my SQL Server central instance (master01). In case I choose the “master” database as default database to connect to but as you can figure out at this point all these options can be changed 🙂

So this is how you can set a specific configuration for SSMS to use every time you start this application, I consider this tip a very handy trick you can use to save yourself some time.

Stay tuned for more DBA mastery tips.

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.

MAX DOP calculator

Every time I conduct a SQL Server health check, it’s pretty common for me to find wrong configurations at instance level. But one of the most common mistakes I see on the wild, is SQL instances having the “max degree of parallelism” (MAXDOP) set to “1” (single core) , probably this behavior is related to a misconception of how parallelism works or because some DBAs thinks this parameter reduces the “CXPACKET” wait types, who knows …

In order configure MAXDOP properly, DBA’s should read and understand what Microsoft’s has as best practice guideline in the KB# 2806535. This article has a table with the explanation on how to calculate the right value for this configuration:

If you are not familiar with NUMA nodes, logical CPUs or CPUs at all this can be a little bit confusing …

NUMA nodes, CPU specs, logical CPU’s, wait …. WHAT???

I understand, we live in a very busy world where we need to do things quick and make everyone’s work easier. Just imagine having to make this calculation every time you want to change the MAXDOP configuration, you truly need something to make your life easier… enter MAXDOP calculator.

The concept of a MAXDOP calculator was created by a Microsoft field engineer long time ago, the article helps you to set the right value for the MAXDOP configuration, the problem I noticed with this tool is that requires a lot of manual intervention. Anytime you would need some help calculating the correct MAXDOP value you will have to visit the website and provide the input from a couple of queries, there is also an Excel version but I do see the same problem. Don’t get me wrong I think this is a wonderful idea but I do personally don’t like manual stuff.

Based on this need, I have created the same calculator but this time in T-SQL. The same concept but in runs in a SQL environment directly, returning the correct MAXDOP value without using any input as the original approach.

Here is an example of the output:

MAXDOP recommendation at Instance level for also T-SQL statement suggestion to re-configure value

At you can see, it provides you the current and recommended MAXDOP configuration also including the syntax to change it in case it is wrong. For SQL Server 2016 or major, it also returns the MAXDOP value configured at database level.

MAXDOP recommendation at DB and Instance level for SQL Server 2016 (and major) also T-SQL statement suggestion to re-configure MAXDOP

If you want to give try it, you can download the T-SQL script from my GitHub repository.

The requirement to execute this T-SQL script is to have SQLCMD mode enabled in SSMS, you can take a look at my previous post to learn how to enable it.

Please feel free to provide me feedback about this tool, stay tuned for more DBA mastery tips!

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.