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 ««12

Database went into Restoring State for unknown reason Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 7:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:09 AM
Points: 7,383, Visits: 15,377
Grant Fritchey (11/7/2013)
GilaMonster (11/6/2013)
Restoring or recovering?

A DB will go restoring if someone runs BACKUP LOG ... WITH NORECOVERY ....


In what might be an epic role reversal, don't you mean RESTORE LOG etc.?


Gail is correct here, BACKUP LOG WITH NORECOVERY is used to take a tail log backup and is also used for role switching in a log shipping configuration.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1513876
Posted Thursday, May 14, 2015 8:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 20, 2015 1:30 PM
Points: 3, Visits: 10
GilaMonster (11/6/2013)
Can also happen if someone starts a restore and stops the restore part way through (perhaps realising they were restoring over the wrong DB).


Could you tell me what to do in this situation? Is there anyway to recover the victimized database? Did the restore not roll back when they hit cancel?
Post #1685716
Posted Thursday, May 14, 2015 11:47 PM


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 @ 3:38 AM
Points: 42,217, Visits: 38,957
afw (5/14/2015)
GilaMonster (11/6/2013)
Can also happen if someone starts a restore and stops the restore part way through (perhaps realising they were restoring over the wrong DB).


Could you tell me what to do in this situation? Is there anyway to recover the victimized database? Did the restore not roll back when they hit cancel?


Drop the partially restored DB and start the restore from scratch. The DB that got restored over is gone.
Restores can't be run in a transaction and the first thing they do is start overwriting the file on disk



Gail Shaw
Microsoft Certified Master: SQL Server, 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 #1685742
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse