Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Since a few days ago, when I try to start the SQL Server service, it fails with the following error logged to the NT Application Log:

An error occurred during recovery, preventing the database 'model' (database ID 3) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

How can I restore (or rebuild) the model database, when the master database is fine but I can't run sql commands against the database (or connect to it from the management studio) since SQL Server does not even start in this case?

I do have backups in a backup container/volume (all backups in a single file on a second HD). I believe the recovery model of the model database to be SIMPLE.

I didn't really find much info in the docs or the internet (like this), as in most articles either the service actually runs so you can run commands against it, or the master database is corrupt as well.

Some selected entries of the ERRORLOG:

  • Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) [..]
  • CHECKDB for database 'master' finished without errors [..]
  • Starting up database 'model'.
  • WARNING: did not see LOP_CKPT_END.
  • Error: 3414, Severity: 21, State: 2.
  • An error occurred during recovery, preventing the database 'model' (database ID 3) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
  • SQL Trace was stopped due to server shutdown. Trace ID = '1'. [..]

Microsoft SQL Server 2005 - 9.00.4053.00, on Windows NT 5.2 (Build 3790: Service Pack 2)

Any ideas? Thanks very much!

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

As it is "only" the model database, i recommend only to copy a model database from a working instance. Rebuild all system databases is not necessary in this case.

share|improve this answer
    
Wow, I'm stupid. Beside of the SQL Server internal backups I also had a full file system backup, so I simply restored the whole model.mdf from there and everything seems fine again. Thanks for the hint! –  Christoph Rüegg Dec 11 '09 at 21:35
add comment

Have you tried starting sql server from the command line?

share|improve this answer
    
Thanks for the idea, would have tried it but the other answer already did the trick. –  Christoph Rüegg Dec 11 '09 at 21:36
add comment

Model is needed at start up to create tempdb. Without tempdb, the engine cannot start. You can copy model from another instance which is at the same build 9.00.4053. If you modified model and what to preserve these modifications, you can try to copy mdf/ldf to another server and attach it as ordinary, user, database to try to repair it, then copy it back.

share|improve this answer
    
I just wanted to clarify why model is critical during startup. –  Remus Rusanu Dec 11 '09 at 21:40
    
Thanks for the explanation! –  Christoph Rüegg Dec 11 '09 at 21:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.