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 through SQL Server 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.
While, the Backup function the Vault Admin Web Client allows you to backup and restore the entire set of Vault/Vault Pro databases, it is a simple interface which can sometimes lead to unexpected behavior. Since Vault/Vault Pro uses SQL Server’s backup command as merely an front end to SQL Server to create backups of the Vault databases. You can also initiate manual backups or create SQL Server backup tasks using tools like SQL Server Management Studio or a batch file in addition to using the SQL Server's sqlcmd utility.
Also note, it's considered a best practice to have an automated backup plan which you can create using your own scripts or by using a SQL Server maintenance plan.
STORE BACKUPS ON A DIFFERENT MACHINE
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.
TEST YOUR BACKUPS
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 these Microsoft articles:
http://msdn.microsoft.com/en-us/library/ms191239.aspx
https://docs.microsoft.com/en-us/sql/re ... rver-ver15
For more specific information see these links for each of the versions of SQL Server:
SQL 2000 with Enterprise Manager
http://technet.microsoft.com/en-us/libr ... 66495.aspx
http://msdn.microsoft.com/en-us/library ... L.80).aspx
SQL Server 2005 Management Studio Backup and restore:
http://msdn.microsoft.com/en-us/library ... L.90).aspx
SQL Server 2008 Backup and Restore and Maintenance Plan Topics:
http://msdn.microsoft.com/en-us/library/ms187048.aspx
http://msdn.microsoft.com/en-us/library/ms189621.aspx
http://msdn.microsoft.com/en-us/library/ms189953.aspx
SQL Server 2017 Back and Restore and Maintenance Plan Topics:
https://docs.microsoft.com/en-us/sql/re ... erver-2017
https://docs.microsoft.com/en-us/sql/re ... erver-2017
If you cannot or do not want to use SQL Server's built-in backup functionality within SQL Server Management Studio, you can also backup the Vault databases via running a SQL script using a SQL Server command line utility (like the sqlcmd or osql utilities).
For example, the following SQL commands could be saved to a file, and run using a SQL Server command line utility. In this example, the SQL queries used to backup the Vault databases will be sent to C:\Backups\ directory on disk:
Code: Select all
--
-- This is a sample 'vault-bak.sql' file which can be used to run a FULL backup of the
-- Vault related databases to a known directory 'C:\Backups\' that was created on
-- the Vault Server's file system.
BACKUP DATABASE sgmaster TO DISK = 'C:\Backups\sgmaster.BAK' WITH INIT, NOSKIP
GO
BACKUP DATABASE sgvault TO DISK = 'C:\Backups\sgvault.BAK' WITH INIT, NOSKIP
GO
BACKUP DATABASE sgnotify TO DISK = 'C:\Backups\sgnotify.BAK' WITH INIT, NOSKIP
GO
BACKUP DATABASE sgvaultindex TO DISK = 'C:\Backups\sgvaultindex.BAK' WITH INIT, NOSKIP
GO
-- this last database is for Vault Professional ONLY
BACKUP DATABASE sgdragnet TO DISK = 'C:\Backups\sgdragnet.BAK' WITH INIT, NOSKIP
GO
For the sqlcmd or osql utilities, run a search for "sqlcmd" or "osql" for your particular version of SQL Server.
Note, the sample above does not include backing up the system databases for your SQL Server installation. Backups for SQL Server itself will require backing up the master, mode, and msdb SQL Server databases. Additional information can be found at : https://technet.microsoft.com/en-us/lib ... 05%29.aspx
Automating the backups
If you wish to automate the backup script outlined above, you can either configure a task within SQL Server Management studio to backup the Vault related databases (sgmaster, sgvault, sgdragnet (Vault Pro only), and possibly sgvaultindex and sgnotify database) OR save the T-SQL commands from above to a text file, and then schedule a batch/shell command script to automate the entire backup process using a SQL Server command line utility (such as sqlcmd or osql). 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 /apppool.name:VaultAppPool
%windir%\SysWow64\inetsrv\appcmd.exe stop apppool /apppool.name:SgDavAppPool
rem @echo Suspending main Vault Web services (32-bit Windows)
rem %windir%\System32\inetsrv\appcmd.exe stop apppool /apppool.name:VaultAppPool
rem %windir%\System32\inetsrv\appcmd.exe stop apppool /apppool.name:SgDavAppPool
@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 /apppool.name:SgDavAppPool
rem %windir%\System32\inetsrv\appcmd.exe start apppool /apppool.name:VaultAppPool
@echo Restarting Vault Web services (64-bit Windows)
%windir%\SysWow64\inetsrv\appcmd.exe start apppool /apppool.name:SgDavAppPool
%windir%\SysWow64\inetsrv\appcmd.exe start apppool /apppool.name:VaultAppPool
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 https://msdn.microsoft.com/en-us/library/ms188714.aspx for additional information.