Model database (Restoring...)

[Former Member]

Hello,

I´m facing a weird problem. In SQL Management Studio I see that Model database is at restoring status, it tells that Model DB is in "Restoring" procedure! We can´t understand why this is happened!

How can I cancel this procedure, can I cancel this restoring? If so, how can I do it? It show me these information for a long time ago.

Can you help me please?

Kind regards,

samid raif

[Matt_Fraser · Accepted solution]

Hi Samid,

This condition can be caused a number of ways.  What probably happened is that you, or someone, attempted a restore on the model database without changing an important option in the SQL Management Studio.  By default (in SQL 2012, anyway), a restore that doesn't include transaction log backups leaves the database in the restoring state (i.e., uses the WITH NORECOVERY option).  Since most DBAs don't bother backing up transaction logs for model, a restore of model will generally fall into this category.

You can probably fix it without even doing another restore.  Open up a query window (not directly on the model database; open it generically on the server or against the master database), and execute the following:

RESTORE DATABASE model WITH RECOVERY

That will probably take care of it.  All it does is 'finish' the unfinished restore by rolling back uncommitted transactions, rolling forward committed ones, etc, and then changing the status of the database.  In model there shouldn't be any outstanding transactions, so it should be very quick.

Let me know if that works or not.  If not, there are other things we can try.

Regards,

Matt

[Former Member · Accepted solution]

Hello Matt,

That solved my problem!

I did exactly what you recommended, I performed the following SQL statement and now everything is fine

RESTORE DATABASE model WITH RECOVERY

Thank you very much for your help

Best regards,

samid raif