model database stuck on Restore

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » model database stuck on Restore

27 posts, Page 1 of 3

1 2 3 »»»

model database stuck on Restore

Rate Topic

Display Mode

Topic Options

Author

Message

gkuiper

Posted Tuesday, July 24, 2012 8:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 02, 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

GilaMonster

Posted Tuesday, July 24, 2012 8:34 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671, Visits: 29,925

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

gkuiper

Posted Tuesday, July 24, 2012 8:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 02, 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

Vikrant S Patil

Posted Tuesday, July 24, 2012 8:43 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:53 PM
Points: 92, Visits: 281

Please check the SQL error log for more information. you should in all probability find some clues there.

Post #1334545

GilaMonster

Posted Tuesday, July 24, 2012 8:46 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671, Visits: 29,925

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

gkuiper

Posted Tuesday, July 24, 2012 9:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 02, 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

gkuiper

Posted Tuesday, July 24, 2012 9:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 02, 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

clayman

Posted Tuesday, July 24, 2012 9:27 AM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, May 18, 2013 8:47 PM
Points: 237, Visits: 1,215

What happens if you issue

RESTORE DATABASE model WITH RECOVERY

from another process?

Post #1334578

Steve Jones - SSC Editor

Posted Tuesday, July 24, 2012 9:29 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 2:54 PM
Points: 31,410, Visits: 13,726

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

gkuiper

Posted Tuesday, July 24, 2012 9:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, August 02, 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 »

27 posts, Page 1 of 3

1 2 3 »»»

Permissions