July 1, 2011 at 3:53 am
Hello Everybody,
I had a problem with an SQL 2005 server because the model database was corrupt. Due to that the database couldn't start anymore because it could create the tempdb without the model database. I also couldn't find back in the SQL server log files when this happenend because SCOM had been trying for several times to restart the sql server and so the older log files were already overwritten, so I couldn't find what caused this problem.
Here's the content of the log file:
2011-07-01 09:50:10.25 spid9s Error: 17207, Severity: 16, State: 1.
2011-07-01 09:50:10.25 spid9s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.
2011-07-01 09:50:10.25 spid9s File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf" may be incorrect.
2011-07-01 09:50:10.25 spid9s Error: 945, Severity: 14, State: 2.
2011-07-01 09:50:10.25 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2011-07-01 09:50:10.25 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
As a good DBA, I make a daily full backup of all my system databases, but didn't find any proper solution to restore the model database because the sql server wouldn't start (I even tried to start the sqlserver with the -c -f -m options, but without success).
When I googled on this kind of problem, it always came with the solution
copy the files over from a working database
But to be able to copy the files over, you need to stop the other database.
(For anyone with the same problem)
I finally copied over the files from a 64 bit database over to this 32 bit server and could restart the SQL server without problems.
But I am still wondering if there exists a "proper" way to be able to restore the model database from the backups I made from them.
Greetings,
Peter
July 1, 2011 at 5:15 am
After copying the model data files from a working instance and get your instance started, you could just restore from the backups you made previously.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
July 5, 2011 at 3:02 am
So no proper way of restoring the model database without having to copy over a clean one from another existing instance. I better start making a filebackup of all model database files for every version of SQL server and store them in a safe place, just to be sure. 😉
July 5, 2011 at 3:10 am
There is. I can't recall off the top of my head, but there's a traceflag that, when enabled, makes SQL only recover the master database, not model or tempDB. With that you can then restore.
I'll have to check details.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2011 at 3:07 am
what about restoring the model backup on a different server with a different name ? make sure the mdf / ldf file stay the same (just a different folder). you can easily deattach afterwards. then you can continue with the mentioned "just copy the model files from a different server" method.
if you dont have a backup, make one on a different server and restore that one with a different name etc and follow the previous alinea
- keep in mind the collation though
- fixing the corruption issue doesn't prevent it from happening again though (easy to say, hard to fix) unless you know exactly what caused it.
July 30, 2011 at 11:35 am
Hi,
copy & paste the mdf & ldf files of model DB from other server which has same OS and SQL server's configuration..as i face out this problem in production environment and the solution was suggested by Microsoft's Technocrats
Thanks
Chandan Gupta
July 30, 2011 at 3:02 pm
peter Vramby (7/5/2011)
So no proper way of restoring the model database without having to copy over a clean one from another existing instance. I better start making a filebackup of all model database files for every version of SQL server and store them in a safe place, just to be sure. 😉
I highly recommend doing that. Do it for all your system databases, including (especially) the resource database.
BTW the official recovery method for the situation you were in is to run rebuildmaster, then restore your backups, but copying over flat file copies of the model database is quicker and preferable.
---------------------------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply