SQL SERVER – How to Restore Corrupted Model Database from Backup?

Another email from one of my blog reader, which made me think about the incorrect information which is available on the internet.

Hi Pinal,

I have corruption in model database and due to that SQL Service is not able to start. Here are the messages in SQL ERRORLOG

2015-09-23 21:45:07.00 spid11s     Srting up database ‘model’.

2015-09-23 21:45:07.45 spid11s     Error: 5125, Severity: 24, State: 2.

2015-09-23 21:45:07.45 spid11s     File ‘E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\model.mdf’ appears to have been truncated by the operating system.  Expected size is 4288 KB but actual size is 4280 KB.

2015-09-23 21:45:07.49 spid11s     SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

What should I do? I have backup of the model database and want to restore it. I have spoken to many folks and I was getting different answers. Can you please provide me right direction?

Thanks,
<Name Hidden>

In this case problem is straight forward, restore backup of model when current model is corrupted and SQL is not starting due to that. If we search on internet, we get below incorrect hints:

  1. Start SQL with trace flag 3608. To do that Use this from command prompt: sqlservr.exe -T3608
  2. Start SQL Server via command prompt using sqlservr.exe -c –m

To test above, I renamed the database files of model database and then tried restore after started via trace flag 3608. Here is the message which I received during restore.

Shared Memory Provider: The pipe has been ended.
Communication link failure

SQL SERVER - How to Restore Corrupted Model Database from Backup? model-02

Restoring model is not a big deal, it doesn’t need any special switch or startup parameter. We can use T-SQL query or Management Studio UI to restore model.

Our problem above is that SQL is not getting started because model is in bad state. Below are the working solutions.

SQL SERVER - How to Restore Corrupted Model Database from Backup? model-01

Hopefully, this flow chart would help in getting exact steps needed.  Model can be restored directly via T-SQL or SSMS.

SQL SERVER - How to Restore Corrupted Model Database from Backup? model-03

Have you ever found incorrect information on the internet? Never trust information from untrusted sources.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Failed Rule “Valid DSN” and “Valid Database compatibility level and successful connection”
Next Post
Interview Question of the Week #038 – What is Left Semi Join Showplan Operator?

Related Posts

8 Comments. Leave new

  • twoknightsthenight
    September 28, 2015 5:19 am

    Gail Shaw has an extensive post on this.

    Reply
  • If your SQL Server 2014 can’t start and show error of “model.mdf” then do the following steps:
    1) Copy “model.mdf” and “modellog.ldf” files form “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\Binn”
    2) Paste\overwrite those copied file inside “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER2014\MSSQL\DATA”
    3) Restart SQL server 2014

    Reply
  • Dieter Brouckaert
    July 3, 2017 3:18 pm

    CMD command for rebuilding your system databases:
    Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

    Source:
    https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-2017

    Reply
  • sachin jawalkar
    February 13, 2018 1:04 pm

    Hi sir i have also faced same issue i resolved this by copy paste from

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

    Reply
  • Hi Pinal

    This post has saved me..

    I was getting below error while starting SQL SERVER services.

    2018-07-01 18:39:32.96 spid6s [INFO] HkHostDbCtxt::Initialize(): Database ID: [3] ‘model’. XTP Engine version is 0.0.
    2018-07-01 18:39:32.96 spid6s Starting up database ‘model’.
    2018-07-01 18:39:32.98 spid6s [INFO] HkHostDbCtxt::Initialize(): Database ID: [3] ‘model’. XTP Engine version is 0.0.
    2018-07-01 18:39:33.50 spid17s [INFO] HkHostDbCtxt::Initialize(): Database ID: [7] ‘SACC LIVE’. XTP Engine version is 0.0.
    2018-07-01 18:39:33.57 spid6s [INFO] HkHostDbCtxt::Initialize(): Database ID: [3] ‘model’. XTP Engine version is 0.0.
    2018-07-01 18:39:33.59 spid6s Error: 9003, Severity: 20, State: 1.
    2018-07-01 18:39:33.59 spid6s The log scan number (33:1888:1) passed to log scan in database ‘model’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
    2018-07-01 18:39:33.60 spid6s SQL Server shutdown has been initiated
    2018-07-01 18:39:33.60 spid6s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

    I replaced model LDF and MDF file from working server and was able to start service.

    Great Thanks.

    Reply

Leave a Reply

Menu