How to start SQL Server if Model database corrupt in SQL Server ?

Today I did some workaround on Model database importance so here we will discuss what will happen if our Model database corrupt or any .mdf or .ldf missing .Model database is also most important database to start SQL Server Services because tempDb need to take some property from Model database and if Model will not start then tempdb can’t create if tempDB  will not  create then our SQL services can’t start .So our TempDB depend on Model database partially .

Microsoft created some trace flag files to resolve this issue we can skip the creation of  tempdb file cause this trace T3609 will not clear tempdb file so it start it from existing file and start SQL Server Services.

Once our SQL Server start with the help of trace files T3608 and T3609 we can restore the Model database if we have backup or we can replace the .mdf and .ldf files with same build version from some other server.

In My case SQL Server not starting casue .MDF and .ldf file missed from the drive whenever I am trying to start SQL Server it’s not starting .When I checked my Windows event log than found

SQL Error log:-

SQL Server Data file location:-

When we opened the path of SQL Server DATA files and found there is no .mdf and .ldf  files exist.

So what next ?

There are some way to recover Model database in SQL Server.

1) Rebuild master database it will create new System database but its complicated technique because you have to very care full if you are going to rebuild master database .When we rebuild the master database it will create all the system Db and remove all the user database from instance so we have to take all the database backup before rebuild Master DB.

2)   Second I have already written a blog on this use Templates that exist in binn folder of SQL Server.

3)    You can copy and paste Model database .MDF and .ldf files from other server but make sure the build should be the same version and currently running on source Server.

4)    This one is interesting and on that I am writing this blog that is restore database backup with the help of trace files –T3608 and –T3609 .

We can start SQL Server by trace flag –T3608 and –T3609.

So, let start with Trace Flag

If you try to use Model database functionality like create database or use SP_HELPDB ‘MODEL’ then you will get below error :-

You can see the status of Model database in below Screen shot:-

So through these trace we can start SQL Server without Model database and Trace 3609 prevent to  clear  and create tempdb files from starting and it will start from existing files.

Now to recover Model database we can restore backup of Model and we can start SQL Server normally without any trace Flag but make sure you have good backup means a restorable backup :-

Run Below command to restore Model database you can do it by three   ways:-

1)  T-SQL

USE [master]

RESTORE DATABASE [model] FROM  DISK = N'E:\model050115new.bak' WITH   REPLACE,  STATS = 5

2)      CMD

3)      SSMS Wizard GUI

Now Model database has been restored successfully! And you can see the .mdf or .ldf file in below folder. Now you can start MSSQLServer normally without any issue.

You can See below when we start SQL Server normally without trace it will clear tempdb first then start .

Below SQL Server start with trace flag and it will not clear Tempdb just start so this is the main use of Trace flag here.

So finally you can see database now in on-line state:-

Its Done Thanks ! J