Sql-server – How to restore a master database from its bak file

master-system-databaserestoresql server

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.

Best Answer

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.

Related Solutions

Sql-server – SQL Recover from .bak file with NOINIT

That's because by not specifying the FILE parameter of RESTORE DATABASE, it is defaulting to 1 which will be the 1st backup set on that media. See the section on this page of Specifying a Backup Set.

Instead of:

restore database YourDb
from disk = 'C:\yourpath\backupfile.bak';
go

You will want to do:

restore database YourDb
from disk = 'C:\yourpath\backupfile.bak'
with file = <n>;
go

Where N is the backup set number in the media. N can be obtained from running RESTORE HEADERONLY on the media. The column you will be concerned with is Position to use with the FILE option.

Sql-server – Restore .Bak file to different Server

When you take a backup all information including the file path is included as part of the backup. As you found out if you just run restore database it will try to restore to the exact same location.

when you include with replace, the backup overwrites the existing .mdf and .ldf files. I normally also have the data and log path directives to specify where to put the files (on my phone will edit to include the exact command).

this is normal behavior since if I am restoring a database on the same server you are most likely recovering from something and would want the db files to be the same.

EDIT: Here is my normal restore command, whether on the same server or different. I like to specify exactly where things are going.

RESTORE DATABASE dbName
   FROM DISK = N'Path\To\Backup.bak'
   WITH MOVE 'dbName_DataFileLogicalName' TO 'Path\To\Data.mdf',
        MOVE 'dbName_LogFileLogicalName' TO 'Path\To\Log.ldf',
       REPLACE,
       STATS = 10

By always specifying the files I am writing to it cuts out some uncertainty and when some one comes and looks it is very clear what is going on.

Best Answer

Related Solutions

Sql-server – SQL Recover from .bak file with NOINIT

Sql-server – Restore .Bak file to different Server

Related Question