How to configure and backup Enterprise Vault (EV) SQL Databases using SQL Stored Procedures

Article:TECH87494  |  Created: 2009-01-02  |  Updated: 2014-08-05  |  Article URL http://www.symantec.com/docs/TECH87494
Article Type
Technical Solution


Issue



The following instructions are for Enterprise Vault environments where there is not a 3rd party backup software that will utilize a SQL Agent to perform a logical backup of the SQL Databases.  This process will allow SQL backups, or dumps, to occur of the databases outside of any other backup schedules

For additional assistance in configuring backups in SQL, please see the SQL Administration documentation regarding proper maintenance.
 


Solution



Configuring Enterprise Vault Database Backup Stored Procedures and Views
 
Allow SQL Ad Hoc Distributed Queries by performing the following in SQL Management Studio. 
 
1. Open SQL Management Studio.
 
2. Choose New Query.

3. Copy the following into the query window and choose Execute.

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'Ad Hoc Distributed Queries', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO
 
4. Close the New Query window. 


For Microsoft SQL Server 2005/2008/2012:
1. Download and extract SQL scripts from TECH68618 (referenced within the Related Articles section below).

2. Open SQL Management Studio.

3. Choose New Query.

4. From the list of Databases choose EnterpriseVaultDirectory.
 
NOTE:  If using EV 9 or higher skip to step 7. 

5. Copy and paste the contents of the view_VaultStoreBackup.sql into the query window and choose Execute.

This will create the view 'dbo.view_VaultStoreBackup'.  The VaultStoreBackup view will query the EnterpriseVaultDirectory database for the SQL Server location and database name for the EV Vault Store, Vault Store Group, Monitoring, and FSA databases.

6. Delete the contents of the query window.

7. Copy and paste the contents of the aspa_BackupEVDatabases.sql into the query window and choose Execute.

This will create the stored procedure 'aspa_BackupEVDatabases'.  The aspa_BackupEVDatabases stored procedure will backup the EnterpriseVaultDirectory database, databases listed in the VaultStoreBackup view, and truncate the associated database transaction log.  



Manually Performing Backups of Enterprise Vault SQL Databases Using Configured Stored Procedures

For Microsoft SQL Server 2005/2008/2012:
1. Create a backup location for the Enterprise Vault databases.

2. Open SQL Management Studio.

3. Choose New Query.

4. From the list of Databases choose EnterpriseVaultDirectory.

5. Copy and paste the following query into the Query window, using values for EVDirectorySQLServer, BackupFolder, DirectoryUser, and Credential specific to the Enterprise Vault environment:

 
exec aspa_BackupEVDatabases
 
@EVDirectorySQLServer='EVDirectoryDatabaseSQLServer' ,
 
@BackupFolder='BackupLocation',
 
@DirectoryUser='Enterprise Vault Service Account',
 
@Credential='Enterprise Vault Service Account Password'
 

 
Example:

 
exec aspa_BackupEVDatabases 
 
@EVDirectorySQLServer='EVSQL' ,
 
@BackupFolder='C:\Backups\',
 
@DirectoryUser='EVDomain\VaultAdmin',
 
@Credential='Password'  
 
 
NOTE:   For EV versions 9.0.5 and 10.0.4 and higher only the EVDirectorySQLServer and BackupFolder variables are required.  For these versions the query would look like the following:
 
Example:
 
 
exec aspa_BackupEVDatabases 
 
@EVDirectorySQLServer='EVSQL' ,
 
@BackupFolder='C:\Backups\'  
 
 
6.   Click Execute.

7.   Backups will be created in the backup location specified in Step 1.  

8.   For each database, a separate backup file (*.BAK) file will be created.



Automatically Performing Backups of Enterprise Vault SQL Databases Using Configured Stored Procedures

NOTE:
This process assumes that the SQL Server Agent is started and operational


For Microsoft SQL Server 2005/2008/2012:
1.   Create a backup location for the Enterprise Vault databases (ex. C:\Backups).

2.   Open the SQL Management Studio utility.

3.   Browse to SQL Server Agent\Jobs.

4.   Right click on the Jobs folder and choose New Job.  The New Job Window is displayed
 

5.   In the Name: field, enter a name for the backup job (Ex. EVBackupJob)

6.   In the Description: field enter a brief description for the backup job

7.   Under Select a page, choose Steps.  The Steps screen is displayed
 

8.   Click New.  The New Job Step window comes up
 

9.    For Step name:, type in a step name, (ex. StartEVBackup).

10.  For Type:, choose Transact-SQL script (T-SQL) from the list of available options.

11.  For Database:, choose EnterpriseVaultDirectory from the list of available options.

12.  In the Command: box, copy and paste the following query using values for EVDirectorySQLServer, BackupFolder, DirectoryUser, and Credential specific to the Enterprise Vault environment:

 
exec aspa_BackupEVDatabases
 
@EVDirectorySQLServer='EVDirectoryDatabaseSQLServer' ,
 
@BackupFolder='BackupLocation',
 
@DirectoryUser='Enterprise Vault Service Account',
 
@Credential='Enterprise Vault Service Account Password'  

 
Example:

 
exec aspa_BackupEVDatabases
 
@EVDirectorySQLServer='EVSQL' ,
 
@BackupFolder='C:\Backups\' 
 
 
NOTE:   For EV versions 9.0.5 and 10.0.4 and higher only the EVDirectorySQLServer and BackupFolder variables are required.  For these versions the query would look like the following:
 

Example:
 

exec aspa_BackupEVDatabases 
 
@EVDirectorySQLServer='EVSQL' ,
 
@BackupFolder='C:\Backups\',
 
@DirectoryUser='EVDomain\VaultAdmin',
 
@Credential='Password'  

 
13.  Under Select a page, choose Advanced.  The Advanced screen is displayed
 

14.   For On success action:, choose Quit the job reporting success.

15.   For On failure action:, choose Quit the job reporting failure.

16.   Click Ok to return to the New Job window.

17.   Under Select a page, choose Schedules.

18.   Click New and the New Job Schedule Window is displayed.
 

19.   For Name:, type in a name for the Job Schedule (ex. EVBackupSchedule).

20.   For Schedule type:, choose Recurring from the list of available options.

21.   For Occurs:, choose a frequency (Daily, Weekly, Monthly) equivalent to the frequency of EV backups.

22.   For Recurs every:, choose the available options depending on the frequency of the backup.

23.   For Daily frequency:, choose the time the database backup should begin.  
 
NOTE: This time should be in conjunction with the EV Vault Stores being placed in backup-mode

24.   For Duration:, choose a start date to begin the backup.

25.   Click Ok twice.

26.   When the scheduled SQL job executes, backups will be created in the location specified in Step 12.

27.   For each database, a separate backup file (*.BAK) file will be created.


Additional Information
SQL Server 2005 Books Online (November 2008) Backing Up and Restoring Databases in SQL Server -  http://technet.microsoft.com/en-us/library/ms187048(SQL.90).aspx

SQL Server 2008 Books Online (July 2009) Backing Up and Restoring Databases in SQL Server -  http://technet.microsoft.com/en-us/library/ms187048.aspx
 

 




Legacy ID



322715


Article URL http://www.symantec.com/docs/TECH87494


Terms of use for this information are found in Legal Notices