November 22, 2011 at 10:46 pm
we have a sql2000 db with suspect status. However the server also has hardware issues.
There is no backup for last 10 days for the db, and nothing brings the suspect db out of suspect status. The server team is taking sql offline and imaging of the server and I also heard replacing the parts. I think they might restore the image on a VM.
So in this case, once they restore the image, do I rebuild the master database using rebuilm.exe? and then restore system db's, user db's? with steps below-
From the Command prompt stop the services by running NET STOP MSSQLServer.
From the Command prompt navigate to the directory where the SQL Server executable and start the server in single user mode by executing sqlservr -m.
Open query analyzer and restore master using the following script:
RESTORE DATABASE master FROM disk = 'local path of the master backup file' --- or rebuildm.exe? (was not sure if rebuildm.exe was required here)
Restore msdb and model, Stop SQL Agent before restore of msdb.
Restore each of the user databases.
Please let me know if that looks right
November 23, 2011 at 1:24 am
No backup of master?
There's a procedure for rebuilding master in Books Online. I suggest you find and follow 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
November 23, 2011 at 8:29 am
Hi Gail,
when I mentioned rebuild of master, I thought I saw it somewhere, I was not sure it was required and I had mentioned that too. I was wrong, master is not corrupt and doesn't need to be rebuilt
we were able to get a backup of master. Its only the suspect user db that doesn't have a recent backup. We took backups of other db's including system db's
So do I restore master and still follow the steps I had mentioned above?
November 23, 2011 at 8:53 am
No, after they apply the image (which I assume contains the system DBs) then restore master only if necessary.
As for the suspect DB, depends how badly it's damaged what options there are for recovery.
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
November 23, 2011 at 9:12 am
Master has not been damaged. Sorry, I misinterpreted something. It was only the user db that was suspect and didn't have backups for few days.
And the server image they were going to take was after taking SQL offline, so when they restore the image, I assume all the db's be available, including suspect db.
"No, after they apply the image (which I assume contains the system DBs) then restore master only if necessary"
- Does that mean I would not have to restore master also?
If so, I can go ahead, get the most recent valid backup of suspect db and restore the db. (Well, all options have been used, and none worked to bring it out of suspect mode.)
November 23, 2011 at 9:21 am
Step 1 : Don't panic !
Step 2: keep your head cool !(if you don't, who will ?)
...
What where the results your the dbcc you ran on the suspect db.
Especially Gail has a ton of experience interpreting that stuff or we may even be able to call in some extra lines 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 23, 2011 at 9:56 am
frankie11 (11/23/2011)
"No, after they apply the image (which I assume contains the system DBs) then restore master only if necessary"- Does that mean I would not have to restore master also?
Maybe, maybe not. Depends if master is up to date and correct after the restore of the image
If so, I can go ahead, get the most recent valid backup of suspect db and restore the db. (Well, all options have been used, and none worked to bring it out of suspect mode.)
That is usually the preferred recovery from suspect.
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
November 23, 2011 at 9:58 am
ALZDBA (11/23/2011)
What where the results your the dbcc you ran on the suspect db.
CheckDB can't be run on a suspect database, it has to be set to emergency mode first (which is not trivial and not documented in SQL 2000)
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
November 23, 2011 at 11:39 am
GilaMonster (11/23/2011)
ALZDBA (11/23/2011)
What where the results your the dbcc you ran on the suspect db.CheckDB can't be run on a suspect database, it has to be set to emergency mode first (which is not trivial and not documented in SQL 2000)
Hence my request.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 23, 2011 at 12:36 pm
ALZDBA (11/23/2011)
Step 1 : Don't panic !Step 2: keep your head cool !(if you don't, who will ?)
...
Thank you ALZDBA. I'm trying.. 🙂
Maybe, maybe not. Depends if master is up to date and correct after the restore of the image
Gail, Pardon me here, how do I know if master is up to date and correct after the restore of image?
Is there anything that I can check to ascertain?
(Guess I need to state that this is a server, dbs we have never managed till date. when things went bad, we got called. So if there is anything as to comparing after restoring the image with what was on the server originally, I would have no idea). Thank you.
November 23, 2011 at 1:01 pm
run dbcc checkdb() prior and after the restore and compare the result.
November 23, 2011 at 1:38 pm
frankie11 (11/23/2011)
Gail, Pardon me here, how do I know if master is up to date and correct after the restore of image?Is there anything that I can check to ascertain?
That all the user DBs are listed, all the logins that are supposed to be there are, that the server settings are correct. If you have no idea what's supposed to be there, then I guess you can either restore or wait for someone to complain.
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
November 23, 2011 at 1:39 pm
viiki.seth (11/23/2011)
run dbcc checkdb() prior and after the restore and compare the result.
Other than a rather slow way of getting the row counts of all tables, I can't see how CheckDB would help tell whether a master (or any other DB) is up to date.
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
November 24, 2011 at 12:26 am
I am clueless. The server team feels they don't need us now because they were restoring the image. I mentioned that suspect db will still be suspect and I'd need to restore from recent backup. They didn't get back to me.. I'm still awaiting their response. Thank you for your help Gail.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply