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

restore a db by using OS level database files Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 15, 2012 12:22 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: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
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
Posted Monday, October 15, 2012 12:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 15, 2012 12:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 13,139, Visits: 11,980
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1372892
Posted Monday, October 15, 2012 12:46 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: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
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
Posted Thursday, October 18, 2012 6:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, October 18, 2012 7:27 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: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
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
Posted Thursday, October 18, 2012 7:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, October 18, 2012 7:54 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: Yesterday @ 3:21 PM
Points: 42,495, Visits: 35,566
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
Posted Thursday, October 18, 2012 8:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse