2

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!

3 Answers 3

2

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.

1
  • 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! Dec 11, 2009 at 21:35
2

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.

1
  • I just wanted to clarify why model is critical during startup. Dec 11, 2009 at 21:40
1

Have you tried starting sql server from the command line?

1
  • Thanks for the idea, would have tried it but the other answer already did the trick. Dec 11, 2009 at 21:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .