Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

model database stuck on Restore Expand / Collapse
Author
Message
Posted Tuesday, July 24, 2012 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 2, 2012 9:16 AM
Points: 9, Visits: 11
Last night my backups failed on my maintenance plan and looks like my Model database was corrupted. It was restoring the database and now it stuck on Restore. If I select Model database and go to Properties in the status: Restoring. The server is running SQL 2008 R2, just a couple of days old, I tried to restore from previous date but it won't allow me to do it because its in the status of restoring.

I have tried this:

use model
restore database model
with norecovery

output:
Msg 927, Level 14, State 2, Line 1
Database 'model' cannot be opened. It is in the middle of a restore.
Post #1334531
Posted Tuesday, July 24, 2012 8:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
What exactly happened? (maintenance plans can't restore model)
What is the exact state of model?
What is the exact state of the sQL instance?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1334533
Posted Tuesday, July 24, 2012 8:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 2, 2012 9:16 AM
Points: 9, Visits: 11
I thought so, but I no expert in SQL.

So my users are able to insert records into another database in SQL, but when I perform the maintenance plan to run it, it errors out:

Executing the query "USE [model]
" failed with the following error: "Database 'model' cannot be opened. It is in the middle of a restore.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


I not able to restore back to previous date or stop the restore process.
Post #1334540
Posted Tuesday, July 24, 2012 8:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, May 3, 2014 5:22 AM
Points: 92, Visits: 321
Please check the SQL error log for more information. you should in all probability find some clues there.




Post #1334545
Posted Tuesday, July 24, 2012 8:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
DO NOT restart SQL!

Look in the SQL error log. Sounds like someone's been fiddling with things they don't understand. Look for any and all messages relating to model.

p.s. A maintenance plan cannot restore databases. It can back up.
Has anyone changed that maint plan recently? Check and see if it's trying to do log backups on model.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1334548
Posted Tuesday, July 24, 2012 9:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 2, 2012 9:16 AM
Points: 9, Visits: 11
Okay I haven't restarted the server.

It's only me that been touch this server.

So not sure what to look for now.
Post #1334568
Posted Tuesday, July 24, 2012 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 2, 2012 9:16 AM
Points: 9, Visits: 11
Here is something from my log files. I do run BackupExec from Symantec to do my nightly backups to tapes.

Log Name: Application
Source: Backup Exec
Date: 7/23/2012 11:08:44 PM
Event ID: 57859
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: VENSQL01.com
Description:
An error occurred while executing the following query: "DBCC CHECKDB([model]) WITH PHYSICAL_ONLY".
On server: "VENSQL01".
SQL error number: "039F".
SQL error message: "Database 'model' cannot be opened. It is in the middle of a restore.
".

For more information, click the following link:
http://eventlookup.veritas.com/eventlookup/EventLookup.jhtml
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Backup Exec" />
<EventID Qualifiers="8192">57859</EventID>
<Level>2</Level>
<Task>0</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2012-07-24T03:08:44.000000000Z" />
<EventRecordID>6071</EventRecordID>
<Channel>Application</Channel>
<Computer>VENSQL01.com</Computer>
<Security />
</System>
<EventData>
<Data>DBCC CHECKDB([model]) WITH PHYSICAL_ONLY</Data>
<Data>VENSQL01</Data>
<Data>039F</Data>
<Data>Database 'model' cannot be opened. It is in the middle of a restore.
</Data>
<Binary>9F030000000000000000000000000000</Binary>
</EventData>
</Event>
Post #1334577
Posted Tuesday, July 24, 2012 9:27 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:23 PM
Points: 277, Visits: 1,691
What happens if you issue

RESTORE DATABASE model WITH RECOVERY

from another process?
Post #1334578
Posted Tuesday, July 24, 2012 9:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 8:53 PM
Points: 33,204, Visits: 15,353
What is in the maintenance plan? Is there a T-SQL script that is there to restore something? There are no tasks in maintenance plans to do restores, as Gail mentioned.

If you are the only one that touches the server, then you started the restore. There's no way for a restore to start by itself. Did you attempt any restore?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1334581
Posted Tuesday, July 24, 2012 9:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 2, 2012 9:16 AM
Points: 9, Visits: 11
clayman (7/24/2012)
What happens if you issue

RESTORE DATABASE model WITH RECOVERY

from another process?


Results:

RESTORE DATABASE successfully processed 0 pages in 0.109 seconds (0.000 MB/sec).

Then run the Maintenace plan again,

It failed, and starting to Restore process the Model database again.

Ran restore database model with recovery again then check database results:

DBCC results for 'model'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
DBCC results for 'sys.sysrscols'.
There are 632 rows in 7 pages for object "sys.sysrscols".
DBCC results for 'sys.sysrowsets'.
There are 91 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sys.sysallocunits'.
There are 103 rows in 2 pages for object "sys.sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syspriorities'.
There are 0 rows in 0 pages for object "sys.syspriorities".
DBCC results for 'sys.sysfgfrag'.
There are 2 rows in 1 pages for object "sys.sysfgfrag".
DBCC results for 'sys.sysphfg'.
There are 1 rows in 1 pages for object "sys.sysphfg".
DBCC results for 'sys.sysprufiles'.
There are 2 rows in 1 pages for object "sys.sysprufiles".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 130 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 53 rows in 1 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 483 rows in 8 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 34 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 134 rows in 2 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 286 rows in 2 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysaudacts'.
There are 0 rows in 0 pages for object "sys.sysaudacts".
DBCC results for 'sys.sysobjvalues'.
There are 136 rows in 17 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 16 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 146 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 106 rows in 1 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.syscompfragments'.
There are 0 rows in 0 pages for object "sys.syscompfragments".
DBCC results for 'sys.sysftstops'.
There are 0 rows in 0 pages for object "sys.sysftstops".
DBCC results for 'sys.sysqnames'.
There are 97 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 99 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 112 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 18 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 3 rows in 1 pages for object "sys.sysbinsubobjs".
DBCC results for 'sys.syssoftobjrefs'.
There are 0 rows in 0 pages for object "sys.syssoftobjrefs".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'sys.filestream_tombstone_2073058421'.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'model'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator
Post #1334590
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse