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
