<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
Data Corruption (SS2K8 / SS2K8 R2)
»
restore a db by using OS level database files...
restore a db by using OS level database files
Rate Topic
Display Mode
Topic Options
Author
Message
underwoodmf
underwoodmf
Posted Monday, October 15, 2012 11:46 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 18, 2012 8:32 AM
Points: 5,
Visits: 6
I have '08 instance that has two dbs in it. No SQL Server backups, only OS level. One of the dbs started getting the 605 logical fetch error a few days ago. It's now hung in "in recovery" state. Can I restore this db simply by shutting the instance down and then restoring the database file(at the OS level) from b4 the errors started? Then just start the service?
Post #1372858
GilaMonster
GilaMonster
Posted Monday, October 15, 2012 12:22 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744,
Visits: 30,025
Maybe. As long as you have both files (log and data) and those copies were taken with the database engine stopped then you should just be able to attach them (take backups of the current files before you do that, with SQL stopped)
Are you sure it's hung, not just taking ages to recover (check the error log)?
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 #1372878
underwoodmf
underwoodmf
Posted Monday, October 15, 2012 12:30 PM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 18, 2012 8:32 AM
Points: 5,
Visits: 6
I'm certain the engine wasn't stopped when the backup ran. Is that a show stopper?
Post #1372885
Sean Lange
Sean Lange
Posted Monday, October 15, 2012 12:37 PM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 1:17 PM
Points: 8,641,
Visits: 8,273
And when you get it back up and running create a maintenance plan to backup your databases so you can avoid this type of thing in the future.
_______________________________________________________________
Need help? Help us help you.
Read the article at
http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1372892
GilaMonster
GilaMonster
Posted Monday, October 15, 2012 12:46 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744,
Visits: 30,025
underwoodmf (10/15/2012)
I'm certain the engine wasn't stopped when the backup ran. Is that a show stopper?
Quite likely.
SQL locks the database files open while it's running. That means that most backup tools will skip the files (they're locked). If you have one of those backup tools that can read locked files then it's a gamble whether those files are usable or not and that's because of the way that SQL writes to the files, they're never guaranteed to be consistent unless the DB is closed, offline, detached or SQL is stopped.
Maybe they'll work, maybe they won't.
File system backups are not a backup strategy.
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 #1372899
underwoodmf
underwoodmf
Posted Thursday, October 18, 2012 6:54 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 18, 2012 8:32 AM
Points: 5,
Visits: 6
Since my db has been hung "in recovery" for 3 days, would it be wise to try the Dedicated Access Connection and run DBCC to fix corruption? If so, do I just open DAC from the copmmand line while "in recovery" or do I try to get the database(not the instance) down to single user and then use DAC? Thanks!!
Post #1374308
GilaMonster
GilaMonster
Posted Thursday, October 18, 2012 7:27 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744,
Visits: 30,025
Nope, you won't be able to access the DB while it's in recovery, at all, DAC or no DAC. How do you know there's corruption? Got the error messages? Or are you just guessing there is some?
If you restart SQL, the recovery starts over from the beginning.
Wait it out (check the error log for progress reports) or restore from backup.
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 #1374346
underwoodmf
underwoodmf
Posted Thursday, October 18, 2012 7:50 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 18, 2012 8:32 AM
Points: 5,
Visits: 6
There were some "logical fetch" errors (605) showing up in the log before the before the scheduled shutdown. It wasn't able to come up after the recycle. It's been over 3 days "in recovery". Is it smart to continue waiting? They're not going to let me stretch it out much longer. If I can't use DAC, will I be able to use sqlcmd to take it to single user and run DBCC?
Post #1374372
GilaMonster
GilaMonster
Posted Thursday, October 18, 2012 7:54 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744,
Visits: 30,025
No, you will not be able to use single user mode. You cannot access a database in any way via any means until recovery is complete (or fails).
Once more with feeling... check the SQL error log, see how far the recovery is. If it hit the corruption the DB would not be 'in recovery', it would be 'suspect'.
You can wait, or you can restore backups. That's pretty much that.
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 #1374377
underwoodmf
underwoodmf
Posted Thursday, October 18, 2012 8:05 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, October 18, 2012 8:32 AM
Points: 5,
Visits: 6
Thanks Gila Monster!!
Post #1374389
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
