How to restore the SQL Server model database?

sql-server sql-server-2005 database database-restore

9,576

Solution 1

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.

Solution 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.

Solution 3

Have you tried starting sql server from the command line?

9,576

Ben Fulton

Maintainer of the Math.NET project. Enjoying distributed algorithms and numbercrunching, be it in the cloud or on an ASIC. F#/C#/VHDL.

Updated on September 17, 2022

Comments

Ben Fulton over 1 year
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!
Ben Fulton over 14 years

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!
Ben Fulton over 14 years

Thanks for the idea, would have tried it but the other answer already did the trick.
Remus Rusanu over 14 years

I just wanted to clarify why model is critical during startup.