How to backup OVIS Reporter DB on regular basis
In the standard configuration HP OpenView Internet Services (OVIS) on Windows is using MSDE as database to store and manage system configuration and to collect measurement reuslts (metrics). If this configuration is being used for production purposes, the need to back up the MSDE base OVIS Reporter database arises. This document describes how this backup can be created on a regular bases.
No additional tools are necessary except a running HP OpenView Internet Services Management Server system on Windows 2000, Windows XP or Windows Server 2003. Only a small batch job needs to be installed.
Concept
In the OVIS standard configuration data is stored in a MSDE instance on a Windows based system. This instance is named "Reporter". This document describes a small batch script to be exceuted by the Windows scheduler on a regular daily basis to backup the reporter database into a backup file on the file system. In case of data loss this file can be used to restore the reporter database as described in the OVIS users guide.
The backup script itself is shutting down OVIS, uses the MSDE command line tool osql to bacckup the reporter database and starts OVIS again. Th name of each backup includes information on the time the backup was performed. Afterwards the batch job deletes existing old backup files from the file system so that only a limited number of backup files remain.
The backup script itself will not harm the collection process of metrics. The only possible consequence might be that mesaurement results are inserted into the database some minutes later.
The batch job for backup
The batch job "backup-ovis-db.bat" is found below:
:: Author: ADVENAGE GmbH
::
:: File: backup-ovis-db.bat
:: Oct 04 2007
::
:: (c) 2007 ADVENAGE GmbH
:: Hannover, Germany
:: www.advenage.com
::
:: Permission to use, copy, modify, distribute, and sell this software
:: and its documentation for any purpose requires specific, prior
:: written permission of ADVENAGE GmbH.
::
:: THE SOFTWARE IS PROVIDED "AS-IS" AND WITHOUT WARRANTY OF ANY KIND,
:: EXPRESS, IMPLIED OR OTHERWISE, INCLUDING WITHOUT LIMITATION, ANY
:: WARRANTY OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE.
::
:: IN NO EVENT SHALL THE ADVENAGE GMBH BE LIABLE FOR ANY
:: SPECIAL, INCIDENTAL, INDIRECT OR CONSEQUENTIAL DAMAGES OF ANY KIND,
:: OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS,
:: WHETHER OR NOT ADVISED OF THE POSSIBILITY OF DAMAGE, AND ON ANY THEORY
:: OF LIABILITY, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
:: PERFORMANCE OF THIS SOFTWARE.
::
:: Description
:: The purpose of this batch job is to backup the current version of the OVIS Reporter database with MSDE.
:: to a file and to keep a configureable number of versions of backups. Older versions will be deleted.
:: Batch job was approved on Windows 2000, Windows XP, Windows Server 2003
::
:: Options to be configured below as variables:
:: BACKUP_PATH - Where to store the backups (excluding trailin backslash)
:: KEEP_VERSION - Number of versions to be kept
:: REPORTER_DB_PASSWORD - password for the account sa of the reporter datebase
::
:: Remarks:
:: BACKUP_PATH shall exist
:: Check the language dependent date settings defined below. Examples given for German and US English
::
SET BACKUP_PATH=C:\OVIS-Backup
SET KEEP_VERSIONS=5
SET REPORTER_DB_PASSWORD=
:: Date and time settings for German Windows
::set year=%date:~-4%
::set month=%date:~-7,2%
::set day=%date:~-10,2%
::set mtimeh=%time:~0,2%
::set mtimem=%time:~3,2%
:: Date and time settings for US English Windows
set year=%date:~-4%
set month=%date:~-10,2%
set day=%date:~-7,2%
set mtimeh=%time:~0,2%
set mtimem=%time:~3,2%
ECHO USE Reporter > backup-ovis-db.ip
ECHO BACKUP LOG Reporter WITH TRUNCATE_ONLY >> backup-ovis-db.ip
ECHO EXEC sp_addumpdevice 'DISK', 'Reporter_BKUP', 'C:\OVIS-Backup\Reporter_%year%%month%%day%_%mtimeh%%mtimem%.bak' >> backup-ovis-db.ip
ECHO BACKUP DATABASE Reporter TO Reporter_BKUP WITH INIT, STATS >> backup-ovis-db.ip
ECHO EXEC sp_dropdevice 'Reporter_BKUP' >> backup-ovis-db.ip
ECHO go >> backup-ovis-db.ip
ECHO exit >> backup-ovis-db.ip
@ECHO on
ovc -stop
osql -S.\OVOPS -Usa -P -i "backup-ovis-db.ip"
ovc -start
@ECHO OFF
ECHO Deleting old Reporter database versions
FOR /F "usebackq skip=%KEEP_VERSIONS%" %%f IN (`DIR /O-N /B %BACKUP_PATH%\Reporter_*.bak`) DO (
ECHO %BACKUP_PATH%\%%f
ERASE %BACKUP_PATH%\%%f
)
How it works:
At the start of the batch file some variables are defined:
- BACKUP_PATH - Where to store the backups (excluding trailin backslash)
- KEEP_VERSION - Number of versions to be kept
- REPORTER_DB_PASSWORD - password for the account sa of the reporter datebase
Please adapt these settings to your particular requiremens. In the next section the date and time variables get initialized. Examples are given for the German and the US English version of Windows. Depending on your languages setting this section might require to be adapted. The version of the batch file found above uses US English settings.
The next section writes the backup commands for MSDE Reporter database into an imput file being used later on. Local settings are resolved using variables defined before.
Now the backup file is created. As a first step the OVIS services are stopped. Afterwards "osql" is called with the necessary arguments to craete the backup. Finally OVIS is started again.
In the last step of the batch file old backup files in the filesystem are deleted using the "FOR" command. Please note that the extended command set is used - which is turned on by default on WIndows 2K, Windows XP and Windows 2003 Server.
Setting up the the backup
Setting up backups with the batch file described above is straight forward:
- Save the bacth file to disk, e.g. in the home directory of Administrator.
- Adapt the batch file to local requriements.
- Create the directory for the backup files.
- Execute the batch file from the command prompt to test it.
- Confiure the Windows scheduler to execute it each day:
Choose: Start/Settings/Control Panel
Start "Scheduled Tasks"
Select "Add Scheduled Task" and specify the batch file and time settings