May 13, 2009 at 9:06 am
This is posted to warn others and - hopefully - to see if anyone knows a better solution than the one I finally used.
I keep backups of system databases using Red-Gate Backup.
However, my MSDB was marked as suspect after moving the virtual machine.
I was then in a situation where I could not re-set the msdb ststus to be online, and I couldn't restore a red-gate backup as the restore failed.
Alter Database failed - couldn't set msdb status (or set it into emergency mode).
On the restore - I attempted to use Red Gates >SQLBACKUPC -SQL "Restore Database .....". This failed because the database was suspect.
started getting worried.
to fix:
1 - I restored the Red-Gate msdb database backup to a temp database (tempmsdb). This was achieved using Red-Gate command-line statements. There were some error messages because the restore could not be recorded in msdb but the database was present and OK.
2 - I generated a native sql backup of this database (C:\tempmsdb).
3 - I then restored c:\tempmsdb over the suspect master.
SO - Has anyone seen this sort of problem and - if so - how did you fix it?
May 13, 2009 at 11:06 am
I'm not sure, but I pinged RG support here.
I would suggest you look through the error logs to determine why it was marked as suspect.
May 14, 2009 at 12:18 am
hi
Thanks for the reply
This is on a virtual machine. We moved the filestore whilst the database was on-line, which seems to have caused major issues - but only on MSDB.
Looks like this is pretty specialised. I'll pass to RG myself today...
May 14, 2009 at 12:47 am
There's two ways I've got around similar issues in the past.
One way is to detach the suspect database then do the restore, but it sounds like that may not work in this case as the RG software expects to be able to access msdb.
The other way I've got around this in the past is to:
- Restore the database as a different name and/or on a different server
- If it's on a different server, detach the files and copy them to the real server. On the correct drive but can be a different directory.
- Stop SQL Server service
- Rename or move the files from the dud database
- Rename or move the files from the restored database to those of the files you renamed/moved in the previous step
- Start SQL Server service
You may need to change the status of the database after this. It does require an outage doing it this way but it should only be a minute or two tops.
May 14, 2009 at 12:53 am
Glenn,
thanks for your solution.
Looks pretty close to what I did.
thing is, I shouldn't have had to do this - I already had Red Gate backups of msdb. Should have been able to apply these.
I have reaised a help call with Red Gate and I'm going to replicate the problem - if I can find a better work around I'll post here.
regards
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply