Lost sgmaster database

richtebb · Post by **richtebb** » Sun Jan 20, 2008 12:36 pm

Hi

I'm trying to reinstall Vault after a HDD crash, and of course it's at this point I discover about the sgmaster database that was added during the 4.0 upgrade. Of course, it was never being backed up and so I've no way of recovering the information.

The upgrade seemed to go without problems and detected my old sgvault database. It evidently also created a new sgmaster database at the same time. Now I'm unable to log into Vault either via Web or client.

Is there any way that I can recreate the information in sgmaster? There are only 2 users, admin + myself (and we're both the same person anyway

). I'd dearly like to be able to access all my SCC history ...

TIA Rich

Post by **jclausius** » Mon Jan 21, 2008 8:57 am

We're in unchartered waters here, so it may take a couple of correspondences to get this right.

1) Get the results from this query:

SELECT DISTINCT(userid) FROM sgvault.dbo.tbluserrepositories

2) Make a known good backup of the sgvault database. THIS IS IMPORTANT!

3) Delete any sgvault and sgmaster databases.

4) Install Vault to the EXACT same version you were using.

5) Create the SAME exact users you had before. Here is where things get tricky. The userid from step 1 has to match any new users. So you must create user's in the exact same ORDER as you had before.

Run SELECT userid FROM sgmaster.dbo.users. It should match the list from step 1.

6) DELETE the sgvault database, for completeness sake.

7) Restore the sgvault database from STEP 2.

8) Finally, uninstall Vault Server (keeping the databases when prompted), and re-install Vault Server (keeping the databases when prompted).

At this point, the users should match up, and I think you are on the road to recovery. Once complete, make sure your SQL backup plan includes both sgmaster as well as sgvault databases. (And for you Fortress users out there, make sure to get the sgdragnet database as well.)

richtebb · Post by **richtebb** » Mon Jan 21, 2008 9:20 am

Jeff, thanks for your reply - prompt as ever. I love how you guys treat support requests, even from someone like me who has the free 1-person licence (although I have persuaded clients to use Vault, so it's not all for nothing!)

Actually, I've just managed to recover the data off the hard drive (it was the RAID controller not the HDD itself that died) so I've been able to restore the original sgmaster. It had been created in the default location (C:\Program Files etc) rather than on my main data drive - luckily, for historic reasons the OS drive was also RAID1 but many people don't run that configuration.

Hopefully, this post will stand as a reminder to anyone else who hadn't realised that version 4 added a new database. It might be worth adding a warning to the release notes or something too, in case there are other folks out there who don't have full-time DBAs to notice this kind of thing. I'm definitely going to alert my clients who use Vault to make sure that they've changed their backup routine!

Post by **jclausius** » Mon Jan 21, 2008 9:32 am

Rich:

Yes. There is info that needs to be better highlighted. The fact that Vault added another database for instance is important enough to warrant its own bullet item.

Glad you're up and running.