October 15, 2012 at 11:46 am
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?
October 15, 2012 at 12:22 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2012 at 12:30 pm
I'm certain the engine wasn't stopped when the backup ran. Is that a show stopper?
October 15, 2012 at 12:37 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 15, 2012 at 12:46 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2012 at 6:54 am
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!!
October 18, 2012 at 7:27 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2012 at 7:50 am
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?
October 18, 2012 at 7:54 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2012 at 8:05 am
Thanks Gila Monster!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply