Recovering Master Database with Master and Data log file

Hi ,

There are times when you will need to fix a corrupt database server and you will need to restore your master database from a good old backup.  This situation is fairly documented and does not need further elaboration with so many articles floating around from God's of SQL Server to help us. 

But I found another situation which was new and different from the other situations. 

I will start with giving you the configuration path so it will help to imagine the pain and progress. 

OS Installation - C:\ Drive 

SQL Installation - D:\ Drive 

SQL User and Data Log file - E:\ Drive (I know please do not shout it's our dev and I did not build it)

SQL Backup - J:\ Drive

SQL Server version - SQL Server 2016 upgraded from SQL Server 2012. 

Ok now that we have base line set let me show you the scenario. 

The OS crashed aka C drive and the backup crashed aka J:\ Drive. 

So we had to get the OS rebuilt to get something up and running. 

SQL was definitely not up because the OS had no clue there was any installation. 

So we had a discussion with the Infra Team that they will install SQL 2012 and then upgrade 2016 and bring it up to the same patch level as before the disaster and then we will try and re-attach the old system database files to bring up the system.

But as I have mentioned before things do not get fixed so easily once they are messed up in DB World. Specially when some one is blogging about it. 

So when I got back the system I saw that the super smart Infra Guy had installed 2016 with the latest patch directly rather than the upgrade path and he improvised on choosing the new path for the system databases to be E:\ drive rather than the default D:\ Drive and he was very proud that he had fixed a glitch with the original install plan. 

Only if he could understand my pain but let me help you understand by elaborating a little on this. 

1. The old system database files were in D Drive when the disaster happened so if we put them in the new path and start the server it will not come up with the missing address link from old (in master database ) to new path . 

2. If we put the old system databases in the old path and try to start the SQL Server then the resource database will happen address issues. 

So I devised an interim plan to first modify all the existing system databases to old database file path in D:\ Drive so the resource database will be updated. Second we will shutdown the database server and attach the old database files to bring up the system. 

Well after attaching the old database files and restarting the SQL Server the System resumed and all the databases and other objects came online. 

This is how we potentially avoided the need to redo all the login , packages and jobs from prod to dev and saved weeks of development effort. 

Thanks,

Tushar Kanti