If Model (SystemDB) database corrupt in SQL Server How to start SQL server.

Model database importance ,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

→ if Model will not start then tempdb can’t create if tempDB will not create then our SQL services can’t start .TempDB depend on Model database

→As per Microsoft statndars 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.

→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 SQL server version from some other server.

Process to recovery the Model database :

→ Rebuild master database it will create new System database but its complicated ,because 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.

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

Now to recover Model database we can restore backup of Model and we can start SQL Server without any trace Flag.

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

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

cmd -net start mssql /T3608 /T3609

Restrictions :

• The model database’s primary file group cannot be set to READ_ONLY status (but the database can)
• The model database does not allow to rename the primary file group;
• The owner of user, the primary file group and the primary data and primary log files of model database cannot be deleted;
• The default collation for the model database is the SQL Server instance collation and cannot be changed;
• The model database cannot be part of a database mirroring solution