SQL SERVER – FIX – The database ‘model’ is marked RESTORING and is in a state that does not allow recovery to be run

SQL SERVER - FIX - The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run help-mac The best way one can learn SQL Server is by trying out things on their own and I am no different. I constantly am trying to explore the various options one can use when working with SQL Server. In the same context, when I was playing around with backup restore commands, I made a mistake and unfortunately restarted SQL Server. After that I was unable to start SQL Service. If I start the service, it doesn’t give any error but gets stop automatically.

Whenever I have any weird problems with SQL, I always look at ERRORLOG files for that instance. If you don’t know the location of Errorlog, you should refer Balmukund’s blog (Help : Where is SQL Server ErrorLog?)

This is what I found in ERROLROG just before the stop.

2014-10-28 002039.02 spid9s      Starting up database 'model'.
2014-10-28 002040.01 spid9s      The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run.
2014-10-28 002040.04 spid9s      Error 927, Severity 14, State 2.
2014-10-28 002040.04 spid9s      Database 'model' cannot be opened. It is in the middle of a restore.

The error and behavior which I am seeing makes sense because to start SQL Server, we need master, model and tempdb database. You might think that MSDB is also a system database and would be needed for SQL Engine? Well, you might have been tricked. MSDB is needed for SQL Server Agent Service, not SQL Server Service. So, my master is fine, model has some problem. Every new database is created using model, including TempDB so SQL Service is refusing to start. Since the model database is not recovered successfully, SQL Server cannot create the tempdb database, and the instance of SQL Server does not start understandably.

So I called up Balmukund – these are the perks of having a good friend to rely. He never says “no” but he also doesn’t give complete solution to the problem. He gives hint and asks me to research further. This time also magical words were – “use trace flag 3608 and restore model with recovery”.

I followed his advice and performed below steps.

1. Start SQL Server with trace flag 3608 using net start command

Net Start MSSQL$SQL2014 /T3608

In my case SQL2014 is the name of the instance. If you have default instance then service name would be MSSQLServer. For named instance, it is MSSQL$InstanceNameHere

SQL SERVER - FIX - The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run model-restoring-02

2. After starting with trace flag 3608, I verified the same from Errorlog as well.

SQL SERVER - FIX - The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run model-restoring-01

Further, I also found below message in ERRORLOG.

Recovering only master database because traceflag 3608 was specified. This is an informational message only. No user action is required.

3. Connected to SQL Instance using SQLCMD by below command.

SQLCMD -S .\SQL2014 -E

You can read parameter of SQLCMD at Books online here

“1>” means we are connected to SQL Instance and then Executed below command (hit enter at end of each line)

RESTORE DATABASE Model WITH RECOVERY
GO


SQL SERVER - FIX - The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run model-restoring-03

4. Once the command is executed successfully, we will come back to “1>” again. We can type exit to come out of SQLCMD

5. Now stop SQL Service

Net Stop MSSQL$SQL2014

6. And start again without trace flag.

Net Start MSSQL$SQL2014

SQL SERVER - FIX - The database 'model' is marked RESTORING and is in a state that does not allow recovery to be run model-restoring-04

Now my SQL instance came up happily and I was unblocked. After sometime I got call from Balmukund asking if SQL is started and I told that my next blog is ready on the same topic. He finally asked, how did that happen? And my answer was – I ran wrong command. My command was

BACKUP DATABASE model TO DISK = 'Full.bak'
GO
BACKUP LOG model TO DISK = 'Log.trn' WITH NORECOVERY
GO

My request to my reader is that please DONOT run the above command in your SQL instance and restart SQL else you need to follow the steps in production server. Learning never stops when working with SQL Server.

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

, ,
Previous Post
SQL SERVER – SSMS Trick – Generating CSV file using Management Studio
Next Post
SQL SERVER – Maintenance Plan – Maintenance Cleanup Task not Deleting Files

Related Posts

14 Comments. Leave new

  • another awesome post. a must save article in my recovery docs

    Reply
  • thanks!!!!

    Reply
  • Hi sir ,
    how to recover database if master data file is corrupted.

    Reply
  • Awesome! Much simpler than the majority of posts on this problem which recommend installing another server with the same server system file locations, and copying over and attaching model.mdf.

    Reply
  • hi sir i have face same problem pls can u explain in details my server is sql server2008 enterprice edition.

    Reply
  • Thanks its very help full for default

    CD C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\
    C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>net start MSSQLSERVER /T3608
    The SQL Server (MSSQLSERVER) service is starting.
    The SQL Server (MSSQLSERVER) service was started successfully.
    C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>SQLCMD
    1> RESTORE DATABASE Model WITH RECOVERY
    2> GO
    RESTORE DATABASE successfully processed 0 pages in 0.722 seconds (0.000 MB/sec).
    —Good Luck

    Reply
  • CD C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\
    C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>net start MSSQLSERVER /T3608
    The SQL Server (MSSQLSERVER) service is starting.
    The SQL Server (MSSQLSERVER) service was started successfully.
    C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn>SQLCMD
    1> RESTORE DATABASE Model WITH RECOVERY
    2> GO
    RESTORE DATABASE successfully processed 0 pages in 0.722 seconds (0.000 MB/sec).
    —Good Luck

    Reply
  • I just had this happen this morning. Its actually easy to do in the GUI when creating a Tlog backup with the wizard. Right above the “compress” option dropdown, is a box that lets you back up a tlog, but leave the DB in a state of recovery. I must have unintentionally checked the box. and when the job runs (for tlogs it runs every 20 minutes), I managed to crash SQL server. And for me, the fix you mentioned above did not work. All you would have to do is recover each of the DB’s that have full recovery mode on, and if you caught it quickly, you would be OK. I let it sit all night and woke up to having 4 databases showing as “restoring” I thought I had gone crazy!!! But no, it was my tired brain (worked until 2am last night) that caused the issue. DOH!

    I’m thinking of The Emperors new Groove quote of the day: “Why do we even have that lever?”

    I cant think of a situation, where you would want to back up a transaction log, and “Back up the tail of the log” and thus put the model database into restoring mode. What are the downsides to putting the model db into simple recovery mode so you cannot destroy your server? over 20 years, Ive been working with SQL server this was a first. By the way we’re running SQL 2008 sp4 on Windows 2012 R2.

    Reply
  • What is the wrong with your command that caused the mishap in the first place?

    Reply
  • Got it. Didn’t see the command properly. You were backing up the log with no recovery.

    Reply
  • Satish Patnaik
    January 7, 2020 3:15 pm

    Can I have the steps to configure SQL Server Mirroring on SQL Server 2017

    Reply

Leave a Reply

Menu