How can I restore a master database from its bak file?

Question

During a penetration test I found multiple .bak-files for an MSSQL database. I want to restore them all. In a Windows VM I installed SQL Server 2017. For restoring the master database, I put the server in single-user mode and tried restoring the database as follows (also tried the same via SSMS):

RESTORE DATABASE master
FROM DISK = 'C:\Foo\<path>\master.bak'
WITH MOVE 'master' TO 'C:\Foo\master.mdf',
MOVE 'master_log' TO 'C:\Foo\master_log.ldf',
REPLACE
GO

This gave me an error like:

.. can't be restored because it was created by a different version of the server (13.00.4435) than this server (14...).

I then downloaded MSSQL 2016 with service pack 1 by subscribing to "Visual Studio Dev Essentials" on my.visualstudio.com. Now I got the error:

.. can't be restored because it was created by a different version of the server (13.00.4435) than this server (13.00.4001).

So now I needed to update. I downloaded MSSQL 2016 service pack 2, which gave me the error:

.. can't be restored because it was created by a different version of the server (13.00.4435) than this server (13.00.5026).

So I really seem to require the exact patch. However, I can't find a list of patches with corresponding server versions. Does anyone have an idea on how to find the right patch?

If I could just extract the .bak file to its .mdf- and .ldf files that would be enough. I found a bunch of tools for restoring MSSQL .bak files, but want they all want to connect to a database (to do a simple restore). I'd be very happy if I could either get the right SQL Server version or extract the .bak file.

Answer 1

The master database is special, different than other databases. It's a system database where SQL Server stores internal objects. The only time you'd normally restore it is if you're bringing back a server from the dead - you wouldn't usually want to restore master from one database to another.

In your scenario, when you're just curious about the contents of the database, restore master with a different database name, like this:

RESTORE DATABASE master_copy
FROM DISK = 'C:\Foo\<path>\master.bak'
WITH MOVE 'master' TO 'C:\Foo\master_copy.mdf',
MOVE 'master_log' TO 'C:\Foo\master_copy_log.ldf',
GO

Then, after the restore finishes, you can query objects in the master_copy database.

Answer 2

As I can't answer as comment, here it is as a normal one:

For your problem with the SQL-Server versions, have a look at https://support.microsoft.com/en-us/help/3177312/sql-server-2016-build-versions

There you can see, that your backup file is from SQL Server 2016 SP1 CU3. Microsoft maintains these lists for all versions of MSSQL down to SQL 7.0 i think.