Use “Template” folder data files rather than rebuilding system databases in SQL Server 2012 during Master database corruption or other system DB corruption

I found one interesting thing to recover master database

There are multiple way to recover master databases but I found one interesting way that is also recommended way by MS.

As everyone know master database is really critical database to start SQL Server instance if master database got corrupt then your SQL Server Instance will not start.

There are some way through we can recover our Master database or start SQL Server instance:-

1)           We can restore backup of Master database if we are able to restart SQL Server in single user mode like if .ldf file missing or corrupt or any page corruption.

2)           We can copy and replace the .mdf and .ldf file from another server where the same SQL Server build running and it will work in case you don’t have backup or you are enable to restart SQL Server in single user mode.

3)           Most complicated and dangerous technique from my side that is Rebuild system database dangerous because whenever we rebuild the Master database it rebuild all the system database as well so before rebuild the master database it mandatory to take all the system and user database backup or copy paste all the .mdf and .ldf file in safe location if you didn’t take the backup then you will lose the all data and database from instance.So we need to take some per-step and post steps before rebuild master databases.

4)           It really interesting solution from my side and its provided by Microsoft as a system template.Have you ever heard about system database templates in SQL Server that exist in :-

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\Templates

So, what next how we can recover master database by provided system database template.

Step 1>Copy Master .mdf and .ldf file from template folder and replace with old master file in the following path.

Now if you try to restart SQL Server it will not start because master database contain the all other database information as well so still we can’t start SQL Server because to start SQL Server we need other system database as well means master database contain all system database info ,so here we copy the master .mdf and .ldf file from template folder and we need to modify the other system database path as well that will help to start SQL Server.

If you try to start SQL server you will get below error:-

System error 1067 has occurred.

The process terminated unexpectedly.

If you check the windows event log you will see error like:-

Error states SQL Server enable to find path of Model database due to SQL Server unable to start.

So, now question is how will be achieve this then .Here is the main twist you have to modify the path of all system database and once it’s done it will work as it is.

So start how to achieve next steps:-

Start SQL Server with single user mode with below command by CMD:-

C:\Windows\system32>NET START MSSQLSERVER /m /t3608

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service was started successfully.

Once it start with Single user mode run below command to modify the path of all system database.

C:\Windows\system32>sqlcmd

1> ALTER DATABASE model  MODIFY FILE  ( NAME = modeldev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\model.mdf')

2> go

The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.

1> ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\modellog.ldf');

2> go

The file "modellog" has been modified in the system catalog. The new path will be used the next time the database is started.

Now you can check the path for all database where they exist to before modify with following command.

1> select * from sys.master_files

2> go

You can see below tempdb path in strange location

TempDB database Path

Tempdev

   e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\tempdb.mdf

Templog

 e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\templog.ldf

Model Database

For model we have already modify the path with above command so it’s showing in right drives

modeldev

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\model.mdf

modellog

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\modellog.ldf

MSDB database

Msdb still not modified so it’s showing in different location

MSDBData

e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBData.mdf

MSDBLog

e:\sql11_main_t.obj.x86release\sql\mkmastr\databases\objfre\i386\MSDBLog.ldf

so same way we need to modify all database tempdb and MSDB as well.

If we ty to start SQL Server again without modify the path of tempdb SQl Server will not start and you can see below error in event viewer:-

C:\Windows\system32>NET START MSSQLSERVER

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service could not be started.

A service specific error occurred: 1814.

More help is available by typing NET HELPMSG 3547.

C:\Windows\system32>sqlcmd

1> ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb.mdf');

2> go

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.

1> ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\templog1.ldf');

2> go

The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

1> ALTER DATABASE msdb MODIFY FILE ( NAME = msdbdata, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\msdbdata.mdf');

2> go

The file "msdbdata" has been modified in the system catalog. The new path will be used the next time the database is started.

Same way modify MSDB as well and start Sql Server it will work and please restore master database backup if you have else you will loss all the login and users even your own access as well.

If you don’t have Master database backup then you will lose your own access as well from instance .So other way to create new login in SQL Server by run SQL Server single user mode.

Run SQL server Single user mode create login and provide admin access

Please check if your error log also working as expected most of time error log lose the permission and we get error like:-

SQL Server error: 

Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found

So it might be issue with your domain account so please provide "SYSADMIN"rights to account it will work.