Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-05-20 : 09:06:23
|
| Melvis Vaz writes "How to restore the database when the database is in suspect state." |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-20 : 12:28:59
|
| Change the status bit using sp_resetstatus. Then perform RESTORE.Tara |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-05-22 : 04:59:03
|
quote:
Originally posted by tduggan
Change the status bit using sp_resetstatus. Then perform RESTORE.
Would there be any reason NOT to add that into my own RESTORE routines? (I have an SP that does a restore given some suitable parameters - but I currently have no check for SUSPECT [nor OFFLINE/READONLY for that matter]  )Perhaps I should add a parameter "@blnForceRestore" or somesuch that would do this, and otherwise draw it to the operators attention.Just wondering if there is any reason to be MORE careful about restoring over a Suspect DB - my reckoning is that when the need arises there will be plenty going on and I'd be better off spending the time now to make my RESTORE SProc able to handle it, rather than when the client is jumping up and down!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-24 : 11:59:14
|
| You should not immediately restore a suspect database so your sproc should not be checking for it. What is the goal of your sproc? You shouldn't have one running like this in production. It sounds like this would be used for a test environment or a disaster recovery site but not production.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-05-25 : 04:29:49
|
| Its not really running "in production" as such; its an SProc that generates the syntax for a restore given a DB name etc. Given a folder name it will list available files etc. Given a DBName and File it will find out the Logcal names / restore path etc. and provide syntax for the restore, plus the code to fix-up logins and so on.So I only run it when I want to restore something, and I am thinking that restoring over a SUSPECT DB is a likely scenario, but I supose I could just have it output a warning that the target DB is SUSPECT and go ahead and generate the appropriate RESTORE syntax anyway. Same for READONLY I suppose - there is probably a good reason why a DB is readonly, and wanting to do a RESTORE seems an unlikely scenarion (and after a RESTORE the DB _might_ need putting back into READONLY mode etc. etc.), but it might only be READONLY because the disk got full!Kristen |
|
|
|
|
|
|
Database Suspect