Migrating Vault from one SQL Server 2000 instance to another

[ross613]

Am just about @ my wit's end with this one, so I got ambitous. I had trouble with one SQL instance - somehow the database account set as owner for the database got screwed up so that any attempt to reset the password or change the ownership to another user wouldn't work. (Won't go into detail on this - but suffice to say it's completely screwed up, I tried everything, and for my purposes let's assume a new database has to be created.)

So...I need to transfer the data from the old SQL instance to a new sgvault database (with a different user as owner). I have the new database setup, no problem...it's ready to go. The question is how could I transfer data from the old database (which I can still query) to the new one? Is there a process for transferring data from one database to another that's relatively issue-free?

Really appreciate anyone's help with this. This problem's been plaguing me for a while...

Gratefully,

[Beth]

You should be able to perform a detach/attach with your previous database or a back-up/restore. Once the database is in the new server, you'll need to re-configure SQL Server security accounts on the newly restored/attached database.

[ross613]

Yes, but that approach didn't seem to work either. Again, something about this database is screwed so that attempting to use the old database account is causing the install to fail either when adding a new ownership user to that database or deleting the old ownership account. I need a solution to actually copy the data from the old database to a new database instance I think...but I could be wrong.

Maybe there's something else here I've missed, but I haven't a clue what.

[Beth]

DTS won't work with the Vault Database. Some other options for you are:

---If you are currently on Vault 3.5 and it's working, you can perform an export/import; or
---You can send your database to us, and we could take a look at it. If it's small, email will be fine, or I can email you a location to ftp it to.

[ross613]

DTS won't work with the Vault Database. Some other options for you are:

{Part(s) omitted...}

---You can send your database to us, and we could take a look at it. If it's small, email will be fine, or I can email you a location to ftp it to.

I appreciate that, Beth - and might well take you up on that. However, before going to that final step - what do you folks normally do when you transfer say the contents of one project to another SourceGear Vault insance? One which already hosts a number of other projects, repositories, etc. Is such a function possible at this point?

[ian_sg]

Ross,

You may also want to try re-installing the server (making sure you keep the database when prompted). The installer can fix a number of SQL user/auth issues. You didn't really go into detail about what's broken, so I can't be sure, but it's definitely worth a shot.

Answering your other question, we don't move projects among Vault instances. We haven't had a reason to. The only way it's really possible, as Beth mentioned, is using the import/export utility included with Vault 3.5. Short of that, if you don't care about history of the "source" project, the only option would be the brute force method: get from one instance and check in to another.

[ross613]

Ian - thanks for the reply. I'm please to report, I just solved my trouble. For some reason, moving the database to another SQL Server 2000 instance allowed me to remove the corrupted SQL user account. Copying it back to the original host server again somehow fixed the trouble....and the reinstallation process worked fine!

Thanks to all who offered assistance on this!

Incidentally, I was using Vault 3.0.7...I realize I should upgrade, but I'd wanted to get this problem ironed out first. I'll consider upgrading to 3.5 in the very near future. Are there any issues upgrading with the stated configuration I should know about?

I tried to be as thorough as necessary in reporting this issue....what details did I miss / should I include that would've been helpful? (Just so I know for next time...) I'd thought the important things were:

• SQL Server 2000
• Vault (later reported as 3.0.7)
• corrupted user account that could be neither deleted, copied over, etc.
• adding new SQL user accounts still resulted in error message being displayed
• web service hosted on different hardware than SQL Server machine
• windows network is not domain-controlled (SQL Server 2000 Enterprise Ed. was running on Windows 2000 AS SAS)

Again, appreciate the help/feedback!

[ian_sg]

Glad you got it. Actually with your recap, it looks like everything important was included, I just neglected some of it. I wasn't sure what the "user corruption" you described was, but now I have a better idea.

When you say it this way,

# corrupted user account that could be neither deleted, copied over, etc.
# adding new SQL user accounts still resulted in error message being displayed

it sounds like very familiar and doesn't indicate any kind of "corruption." When you move a database (any database, not just Vault's) from one SQL Server to another, that database contains user information that's related by ID to a login on the original server. Upon restore on the new machine, these users no longer have their associated SQL login, so they show up sort of there, and sort of not, depending on how you look for them. (They show up in the database, but if you try to log in using them, you'll get a "no such user" error.) You can't delete them from the database using the Enterprise Manager GUI, but a sp_dropuser or sp_revokedbaccess command from Query Analyzer will do the trick. You can then re-add the correct machine-appropriate user on the new server.

I'm almost positive this is something the Vault Server installation corrects, but just in case, the info's here now.