Do you have a backup of your Vault Databases?

A collection of information about Vault, including solutions to common problems.

Moderator: SourceGear

Posts: 9736
Joined: Tue Dec 16, 2003 1:25 pm
Location: SourceGear
PostPosted: Wed Apr 07, 2010 3:34 pm
Do you have a recent backup of your Vault, Vault Pro or Fortress databases?

What if you experience a server failure, hard drive failure or SQL Server catastrophic failure? These types of hardware/system failures can damage your Vault Pro and Vault databases. (Note: Fortress is now called Vault Pro.)

If a database is damaged, it may not be recoverable. If it is recovered with data loss, your database may still be unusable for source control or item tracking operations in Vault or Vault Pro.

You may think you have a recent backup. Are you sure? Maybe the backup job ran out of disk space or perhaps something on the network has changed and the backup isn’t happening.

These are all real issues that customers have reported to us in the past year.

It's extremely important to back up your sgvault, sgmaster and (if you’ re using Vault Pro) sgdragnet databases on a regular basis to prevent downtime for your production team and possible loss of your source code and item tracking information. It is not as critical to backup the sgvaultindex or sgnotify databases, but doing so could reduce the time necessary to recreate them and prevent missing any notifications.

Backing up your SQL Server databases.

The Backup function the Vault Admin Web Client allows you to backup and restore the entire set of Vault/Vault Pro databases. Since Vault/Vault Pro uses SQL Server’s backup tool, this is merely an interface to manually create backups of the Vault database. You can also do manual backups using SQL Server tools.

However, it's best to have an automated backup plan which you can create using your own scripts or by using a SQL Server maintenance plan.

You should store your backups on a different machine/drive than your live data. If your machine or disk crashes, your could lose your backups as well as the live data.

Once you've established a backup routine, it's crucial that you test your backups.

Every couple of months, restore your most-recent backup to a separate SQL Server - which is the situation you'll be in if you really need the backup. Make sure the backups are intact and accessible, and that you know the steps to restore a working SQL Server database.

For a good overview of SQL Server backup, see this Microsoft article:

For more specific information see these links for each of the versions of SQL Server:

SQL 2000 with Enterprise Manager

SQL Server 2005 Management Studio Backup and restore:

SQL Server 2008 Backup and Restore and Maintenance Plan Topics:

If you cannot or do not want to use SQL Server's built-in backup functionality, you can also backup the Vault databases through a SQL script. For example, the following SQL commands could be used to backup the Vault databases to a C:\Backups\ directory on disk:

Code: Select all
BACKUP DATABASE sgmaster TO DISK = 'C:\Backups\sgmaster.BAK' WITH INIT, NOSKIP
BACKUP DATABASE sgnotify TO DISK = 'C:\Backups\sgnotify.BAK' WITH INIT, NOSKIP
BACKUP DATABASE sgvaultindex TO DISK = 'C:\Backups\sgvaultindex.BAK' WITH INIT, NOSKIP
-- this last database is for Vault Professional ONLY
BACKUP DATABASE sgdragnet TO DISK = 'C:\Backups\sgdragnet.BAK' WITH INIT, NOSKIP
For more information on the syntax of Transact SQL backups see:

Note, the sample above does not include backing up the system databases for your SQL Server installation. Backups for SQL Server will require backing up the master, mode, and msdb SQL Server databases. Additional information can be found at : ... 05%29.aspx

Automating the backups

If you wish to automate the backup script outlined above, you can save the T-SQL commands above to a file, and schedule a batch/shell command script to automate the entire backup process. A sample script can be tailor-made for your own needs.

The following is a simple script which runs the 'vault-bak.sql' (from the commands above) to a file from an Administrative command line:

Code: Select all
@echo Suspending main Vault Web services (64-bit Windows)
%windir%\SysWow64\inetsrv\appcmd.exe stop apppool /
%windir%\SysWow64\inetsrv\appcmd.exe stop apppool /

rem @echo Suspending main Vault Web services (32-bit Windows)
rem %windir%\System32\inetsrv\appcmd.exe stop apppool /
rem %windir%\System32\inetsrv\appcmd.exe stop apppool /

@echo Backing up Vault databases for T-SQL BACKUP commands in vault.bak.sql using SQL Authentication
sqlcmd.exe -S "(local)\SQLEXPRESS" -U sa -P "sa_pwd" -i "C:\Backups\vault-bak.sql" -o "C:\Backups\vault-bak.log"

rem @echo Restarting Vault Web services (32-bit Windows)
rem %windir%\System32\inetsrv\appcmd.exe start apppool /
rem %windir%\System32\inetsrv\appcmd.exe start apppool /

@echo Restarting Vault Web services (64-bit Windows)
%windir%\SysWow64\inetsrv\appcmd.exe start apppool /
%windir%\SysWow64\inetsrv\appcmd.exe start apppool /

This sample is provided as a template in order to create a .bat or .cmd file which may be modified and scheduled to fit the needs of your Vault Server backup strategy.

Also note, you can also modify the vault-bak.sql and the batch file to use variables passed between the two in order to have more control over backup name and / or location. See for additional information.
Linda Bauer
Technical Support Manager

Return to Knowledge Base (Vault)

Who is online

Users browsing this forum: No registered users and 4 guests