July 28, 2011 at 12:06 am
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
July 28, 2011 at 1:57 am
Not sure about the cause. But this my bring it online.
restore database model with recovery
July 28, 2011 at 3:20 am
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 ourmodel 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!
July 28, 2011 at 3:57 am
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 ourmodel 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.
July 28, 2011 at 8:37 am
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.
July 28, 2011 at 8:41 am
It was a fresh install. Just noticed that the database was in stuck in restore state a day or two later.
July 28, 2011 at 8:47 am
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!
July 28, 2011 at 8:47 am
Thanks I will try this, is it ok to try this during production hours?
July 28, 2011 at 8:50 am
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).
July 28, 2011 at 8:53 am
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
July 28, 2011 at 8:57 am
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.
July 28, 2011 at 9:02 am
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!
July 28, 2011 at 9:04 am
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!
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply