SQL DB migration

[Thalko]

Due to the increasing size of our Vault Database (MS SQL 2K) we have decided to migrate the database to a seperate partition (on a Win 2K3 Server). We are planning on backing up the database, dropping it and creating a new one on the new partition and naming it the same as the current Vault DB (sgvault), then restoring from this backup.

I see that db_owner access to the Vault DB is to 3 users:

1.) dbo
2.) <local_account>\ASPNET
3.) NT AUTHORITY\ NETWORK SERVICE


I assume the Vault Server executable uses these to communicate with the database, though can you confirm this?


So after doing the database restore on the newly created DB (with the same name), I would grant db_Owner access to these same accounts again in the DB. other than that is there anything i should do to enable the vault server executable is able to communicate with the database?


Also, are there any services i should stop to restrict access to people prior to doing the DB backup and ensure that nothing gets messed up when vault tries to connect to the DB during the window of when the DB is dropped up to the point that it has been recreated and restored on the new partition?

[lbauer]

One way to do this would be to restore the database, then upgrade to Vault 3.1.2, if you haven't already. The Vault installer will not only upgrade the database but take care of the necessary SQL Server login, db user, etc. Or you can uninstall Vault and re-install, pointing at the new DB location.

So after doing the database restore on the newly created DB (with the same name), I would grant db_Owner access to these same accounts again in the DB. other than that is there anything i should do to enable the vault server executable is able to communicate with the database?

You may actually need to delete and recreate the account used by Vault.
If you don't want to reinstall Vault after you move the database to a different SQL Server, then see details here.

http://support.sourcegear.com/viewtopic.php?t=924

I assume the Vault Server executable uses these to communicate with the database, though can you confirm this?

Yes, during Vault installation on Windows 2003 Server, if you choose Network Service for the IIS Process Model and Windows Authentication for the SQL Server Setup, Network Service will be the database user for Vault.

Also, are there any services i should stop to restrict access to people prior to doing the DB backup and ensure that nothing gets messed up when vault tries to connect to the DB during the window of when the DB is dropped up to the point that it has been recreated and restored on the new partition?

Here are some tips on "closing Vault":

http://support.sourcegear.com/viewtopic.php?t=4538