model database stuck in restore state

Question

Post reply

model database stuck in restore state

brown-415622

SSC Enthusiast

Points: 158
More actions
July 28, 2011 at 12:06 am

#390073

We just upgraded our Sql server to windows 2008R2 and Sql 2008 R2 about a week ago and noticed that our
model database is stuck in a restore state. Any ideas what could have caused this and what can I do to solve the problem.
Thanks

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

Not sure about the cause. But this my bring it online.

restore database model with recovery

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 2

brown-415622 (7/28/2011)
We just upgraded our Sql server to windows 2008R2 and Sql 2008 R2 about a week ago and noticed that our
model database is stuck in a restore state. Any ideas what could have caused this and what can I do to solve the problem.
Thanks

How did you do the upgrade? Did you restore the model database from the old SQL Server? Maybe if you did you selected restore with no recovery.

By the way Suresh tip should help you. Let us know how did it go. Good luck!

My blog

chandan_jha18 SSChampion Points: 11330 More actions · Answer 3

brown-415622 (7/28/2011)
We just upgraded our Sql server to windows 2008R2 and Sql 2008 R2 about a week ago and noticed that our
model database is stuck in a restore state. Any ideas what could have caused this and what can I do to solve the problem.
Thanks

If you upgraded the databases by using backup and restore, why did you follow the same procedure for model. Did you have any specific objects in model that you needed to retain.

brown-415622 SSC Enthusiast Points: 158 More actions · Answer 4

The upgrade was done from a fresh install of OS and Sql install. I just restored our database from a backup. The model database was fine just noticed the model database was stuck in a restore state a day later.

brown-415622 SSC Enthusiast Points: 158 More actions · Answer 5

It was a fresh install. Just noticed that the database was in stuck in restore state a day or two later.

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 6

brown-415622 (7/28/2011)
It was a fresh install. Just noticed that the database was in stuck in restore state a day or two later.

So, you did not restore the model database. hen it is very weird what happened to you. I have never heard of something like it. A database changing the state to restoring, all by itself!

My blog

brown-415622 SSC Enthusiast Points: 158 More actions · Answer 7

Thanks I will try this, is it ok to try this during production hours?

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 8

brown-415622 (7/28/2011)
Thanks I will try this, is it ok to try this during production hours?

Are you referring to the restore script for the model database?

My blog

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Ignacio A. Salom Rangel (7/28/2011)
brown-415622 (7/28/2011)
It was a fresh install. Just noticed that the database was in stuck in restore state a day or two later.
So, you did not restore the model database. hen it is very weird what happened to you. I have never heard of something like it. A database changing the state to restoring, all by itself!

It can happen if someone did a tail backup of model (putting it in NORECOVERY).

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

brown-415622 (7/28/2011)
Thanks I will try this, is it ok to try this during production hours?

Actually I wouldn't wait. In theory, you can't create a new DB untill this is fixed (also possibly not recreate tempdb at all... so DON'T restart the server untill this is fixed).

I have never seen / tested that scenario and I don't intend to try it on my prod server ;-).

You can also read the sql logs to see what happened...

CREATE TABLE #logs (LogDate DATETIME, ProcessInfo VARCHAR(50), Txt VARCHAR(MAX))

INSERT INTO #logs (LogDate, ProcessInfo, Txt)

EXEC master.sys.xp_readerrorlog 0,1

INSERT INTO #logs (LogDate, ProcessInfo, Txt)

EXEC master.sys.xp_readerrorlog 1,1

INSERT INTO #logs (LogDate, ProcessInfo, Txt)

EXEC master.sys.xp_readerrorlog 2,1

--as many as you need

SELECT * FROM #logs

--where Txt like '%model%'

ORDER BY LogDate DESC

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 11

Ninja's_RGR'us (7/28/2011)
Ignacio A. Salom Rangel (7/28/2011)
brown-415622 (7/28/2011)
It was a fresh install. Just noticed that the database was in stuck in restore state a day or two later.
So, you did not restore the model database. hen it is very weird what happened to you. I have never heard of something like it. A database changing the state to restoring, all by itself!
It can happen if someone did a tail backup of model (putting it in NORECOVERY).

Yes, but if you are the only DBA, how can it happend? I'll find it very weird if he did a tail backup and forgot about it, Don't you!

Thank you for pointing the Tail backup.

My blog

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 12

brown-415622 (7/28/2011)
Thanks I will try this, is it ok to try this during production hours?

If you are referring to the restore script from Suresh, the answer is yes, you can do that during production hours!

My blog

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 13

Ninja's_RGR'us (7/28/2011)
brown-415622 (7/28/2011)
Thanks I will try this, is it ok to try this during production hours?
I have never seen / tested that scenario and I don't intend to try it on my prod server ;-).

I tried this on my test environment before and it should not be a problem to run the restore script!

My blog

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Ignacio A. Salom Rangel (7/28/2011)
Ninja's_RGR'us (7/28/2011)
Ignacio A. Salom Rangel (7/28/2011)
brown-415622 (7/28/2011)
It was a fresh install. Just noticed that the database was in stuck in restore state a day or two later.
So, you did not restore the model database. hen it is very weird what happened to you. I have never heard of something like it. A database changing the state to restoring, all by itself!
It can happen if someone did a tail backup of model (putting it in NORECOVERY).
Yes, but if you are the only DBA, how can it happend? I'll find it very weird if he did a tail backup and forgot about it, Don't you!
Thank you for pointing the Tail backup.

I actually did that once. Used the GUI to take a backup and hit the norecovery by accident (and never saw it) untill I realized that the db wasn't available anymore. Of course I figured out what had happened somewhat easily!