Find the version of the Master DB from a backup

Try researching recovering the master database online and you will see countless references to having to have SQL at the same version and builld level as the backup of the master database.  Since most folks probably don’t have a routine in place to record the version number each time SQL server is patched we need a way to be able to determine the version and build level of our instance.

If you take regular backups of the master database and have to restore to the same instance, you should be ok to not have to worry about the version.  However, if you are having to recover the system database from one instance to another server, then the build version becomes very important.

Imagine the scenario that you get a call from the NOC at 3:00 AM stating an important production server has crashed. You crawl out of bed, remote in and realize that all your SAN LUNS are missing.  You report back to the NOC, they call the SAN administrator and you learn that the LUNS are unrecoverable.  As luck would have it, you have a spare server you can migrate over to but you don’t script out all your user objects so you have to recover the system databases as well.  What version are you on?  10.0.4023 or 10.0.4062  You don’t know.  All you know is SQL 2008 Sp2.

As luck would have it, the boys and girls at Microsoft thought ahead.  This data is stored in the header and we have the ability to retrieve that information.

RESTORE HEADERONLY FROM DISK = ‘DRIVE:PATHDB_NAME.BAK’

This will return the SoftwareVersionMajor, SoftwareVersionMinor, and the SoftwareVersionBuild.  For example 10.0.4064.  Pretty neat huh?

Once you have SQL on the new instance installed and patched to the same level, you can start SQL in single user mode, connect to SQL using SQLCMD, and then restore the master database.  There are tons of blogs with step by steps on how to recover the master database.

I hope you never have to use this in production, but you should have plenty of experience practicing this.