Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Backing Up the CMS and Audit Databases Using SQL Anywhere Maintenance Plans

jose_at_sap
Advisor
Advisor
‎11-05-2013 7:45 PM

Contents


Introduction
Prerequisites
Types of Backups
Backup Strategy
Maintenance Plans
Creating a Maintenance Plan for the Full Database Backup
Creating a Maintenance Plan for the Incremental Database Backup
Testing and Executing the Maintenance Plan
Recovering the Database From Backup
Summary and Additional Resources




Introduction


If you are using SAP Business Objects (BOBJ) 3.1 (SP5 or higher) or BOBJ 4.1, then your Central Management Server (CMS) and Audit databases are likely to be SAP SQL Anywhere (SQLA) 12.0.1.  An extremely important task for any BOBJ administrator is to design and implement a proper backup and recovery strategy in case disaster hits and your production system is damaged beyond repair, leaving you with no other choice than restoring from a recent backup.

The backup and recovery strategy for the BOBJ server is well-documented in the Administrator's Guide, but it doesn't describe in detail the steps required to backup the SQL Anywhere CMS and Audit databases.  This document explains how to accomplish this task and automatically schedule the execution of backups using maintenance plans.

It is worth mentioning that SQL Anywhere databases perform automatic recovery when the server crashes, so there is no need to recover the database in those situations (e.g.: power outages, accidental power off, server restart after hanging).  However, if the CMS or Audit database files become corrupt, then you will need to perform recovery.  Corrupt database files are typically a sign of file system (disk) corruption.

The original location of this document is http://scn.sap.com/docs/DOC-48608.


Prerequisites


You will need Sybase Central, the SQLA database administration tool.  It is included in the SQL Anywhere Client installation - click here for information on how to obtain it.  Please install that component in your BOBJ server.

The procedure described in this document was tested on SAP Business Objects 3.1 SP5 running under Windows x64, but is also applicable to other BOBJ versions that use SQL Anywhere for its CMS and Audit databases, and other operating systems.


Types of Backup


There are two types of database backups you can perform: offline (cold) and online (hot).

Performing an offline backup is very easy: you completely shut down the BOBJ server, including the database service, and then copy the SQLA database and transaction log files to your backup location.  By default, these files are:

  • Business Objects 3.1

    • Location is C:\Program Files (x86)\Business Objects\SQLAnyWhere12\bin.

    • File names: BOE120.db, BOE120.log, BOE120_AUDIT.db, and BOE120_AUDIT.log.



  • Business Objects 4.1

    • Location is C:\Program Files (x86)\SAP Business Objects\sqlanywhere\database.

    • File names: BI4_CMS.db, BI4_CMS.log, BI4_Audit.db, and BI4_Audit.log.




An online backup executes when the database is running, so it can be done while your BOBJ system is in operation. The remainder of this document deals with this situation.


The Backup Strategy


Always make sure to have a backup and recovery strategy before testing and implementing your plan.

Our Strategy

  • Perform a full online backup of the CMS and Audit databases once a week, on Sundays at 5:00 AM.

  • Perform an incremental online backup each day at 6:00 AM.


A full backup is a complete backup of both the database and transaction log files.  An incremental backup is a backup of just the transaction log file.

The frequency of the backups depends on your deployment.  The more often your perform backups, the less data you'll lose in case of disaster.  In our case, a weekly full backup and daily incremental backups are sufficient.

SQL Anywhere has the concept of "database validation", a process where the database file is checked for corruption.  Since file corruptions will not be detected until the database server tries to access the affected part of the database, it is advisable to perform database validation just prior to a full backup.  It is also good practice to validate the backed up database file to ensure its integrity.


Maintenance Plans


A maintenance plans is a series of scheduled events automatically executed by the database server.  Maintenance plans are created using the visual administration tool Sybase Central.  Each time a maintenance plan runs, a report is generated that you can view to examine any errors that may have occurred.

For complete details about maintenance plans, please refer to the SQL Anywhere 12.0.1 documentation.


Creating a Maintenance Plan for the Full Database Backup


The first step is to create a maintenance plan so that a full backup of the CMS and Audit databases happen once a week.

  1. Launch Sybase Central (click Start > All Programs > SQL Anywhere 12 > Administration Tools > Sybase Central). Close the Welcome dialog if it appears.

  2. Switch to the "Folders" view by clicking Folders from the View menu.

  3. Right-click on "SQL Anywhere 12" and select Connect from the popup menu.

  4. The Connect dialog appears.

    • Enter the appropriate user ID (boeuser) and password to login to the CMS database.

    • Select the action "Connect with an ODBC Data Source".

    • Type the CMS ODBC data source name (default is "BOE120" for BOBJ 3.1) and click Connect.



  5. Sybase Central is now connected to the CMS database.  In the Folders view (left panel), right-click on "Maintenance Plans" and select New > Maintenance Plan from the popup menu.

  6. The Create Maintenance Plan Wizard appears. Name the maintenance plan "BOE120_CMSFullBackup" and make sure the two boxes are NOT checked.  Click Next to continue.If the two boxes are checked, the database server will disconnect all users and disallow any connections to the database while the backup takes place. This behaviour is not desired in a Business Objects deployment.

  7. Specify the start date and time for the initial backup (e.g. Sunday at 5:00 AM).  Take note that this start time and date must occur earlier than the first incremental backup (described in next section).  Click Next to continue.

  8. Check "Run the maintenance plan on the following" and choose "Days of the week".  Select "Sunday" to schedule the maintenance place to run every Sunday.  Click Next to continue.

  9. As stated earlier, it's a good idea to validate the database before backing it up.

    • Check "Include a validation of the database in this maintenance plan".

    • Check "Validate database pages".

    • Select "Full check" and click Next to continue.



  10. In this page, you instruct the maintenance plan to perform a full backup.

    • Check "Include a backup of the database in this maintenance plan".

    • Select "Back up to disk".

    • Select "Full image backup".

    • Enter the location to save the backed up database and click Next to continue.



  11. You can run a series to SQL statements before or after the maintenance plan executes. Earlier, we mentioned that it's a good idea to ensure the backed up database is valid and we can do that by executing a custom SQL script after the CMS database is backed up.cutCheck "Run this SQL at the end of the maintenance plan, after the database is backed up" and type the following SQL code (note that line #4 is rather long - make sure to copy it entirely):
    BEGIN
    
    DECLARE @VALIDBK_RETURN_CODE INT;
    
    DECLARE @VALIDBK_COMMAND LONG VARCHAR;
    
    SET @VALIDBK_COMMAND = 'dbisql -nogui -c "UID=dba;PWD=sql;ServerName=BOEBackupServer;DBN=BOE120;DBF=' || @BACKUP_FOLDER || '\\BOE120.db;START=C:\\Program Files\\Business Objects (x86)\\SQLAnyWhere12\\bin\\dbeng12.exe -im v" "VALIDATE DATABASE;"';
    
    @VALIDBK_RETURN_CODE = CALL dbo.xp_cmdshell(@VALIDBK_COMMAND,'no_output');
    
    IF @VALIDBK_RETURN_CODE <> 0 THEN
    
        SET @SUCCESS = 0;
    
        SET @MSG = 'dbo.xp_cmdshell() failed: (return code = ' || @VALIDBK_RETURN_CODE || ')';
    
        MESSAGE @MSG;
    
    END IF;
    
END;



    Depending on the location of your Business Object installation, the start line may be different.  Click Next to continue.

  12. Each time the maintenance plan runs, you can save its results and/or send them to an administrator via email.

    • Choose to save only the last 5 reports.

    • Check "Report the maintenance plan status to the server's console"

    • Check "Email the maintenance plan report", select "Always", type the recipien't name and choose the email protocol to use.



  13. Click Finish to complete the Create Maintenance Plan Wizard.

  14. You are now back in Sybase Central.In the Folders view, expand "Events".  You will notice the event "BOE120_CMSFullBackup_event" was created for our maintenance plan.  We need to edit the generated SQL code a bit to properly save the backed up database in a specific directory.  Click on that event.

  15. By default, the backup folder is the same for each subsequent backup and that will cause the backed up database to be overwritten every time the maintenance plan runs.  We can change the generated code to prevent that behaviour from happening.In the right panel, you see the SQL code for the event.  Add the following to the variable declarations:
    DECLARE @BACKUP_FOLDER          LONG VARCHAR;


  16. Locate the Backup section (starts with "// Backup") and modify the script as follows:
    // Backup
    
UPDATE dbo.maint_plan_status SET "status" = 'BACKUP' WHERE plan_id = @PLAN_ID;
    
SET @MSG = 'Backup started on ' || CURRENT DATE || ' at ' || CURRENT TIME || '';
    
SET @REPORT = @REPORT || @MSG || '\n';
    
MESSAGE @MSG;
    
BEGIN
    
    DECLARE SUNDAY INTEGER;
    
    DECLARE FULL_BACKUP_DATE DATE;
    
    SET SUNDAY = 1;
    
    SET FULL_BACKUP_DATE = CURRENT DATE - datepart(dw, getdate()) + SUNDAY;
    
    SET @BACKUP_FOLDER = 'D:\\BOEDBBackup\\FullBackup\\' || FULL_BACKUP_DATE;
    
    SET @MSG = 'Backing up to image: ''' || @BACKUP_FOLDER || '''';
    
    SET @REPORT = @REPORT || @MSG || '\n';
    
    MESSAGE @MSG;
    
    BACKUP DATABASE DIRECTORY @BACKUP_FOLDER
    
    WAIT BEFORE START;
    
END;
    
SET @MSG = 'Backup finished on ' || CURRENT DATE || ' at ' || CURRENT TIME || '';
    
SET @REPORT = @REPORT || @MSG || '\n';
    
MESSAGE @MSG;


  17. Click the Save icon on the toolbar to save the changes to the event.  You now have a maintenance plan that performs a full backup of the CMS database.


Repeat the above steps to create a maintenance plan for the Audit database, making sure that the backup directory is different (e.g. D:\AuditDBBackup\FullBackup).


Creating a Maintenance Plan for the Incremental Database Backup


We can create a similar maintenance plan, but this time it's for daily incremental backups of the CMS and Audit databases.

  1. Ensure you are connected to the CMS database using Sybase Central (as in the previous section).

  2. In the Folders view (left panel), right-click on "Maintenance Plans" and select New > Maintenance Plan from the popup menu.

  3. The Create Maintenance Plan Wizard appears. Name the maintenance plan "BOE120_CMSIncrementalBackup" and make sure the two boxes are NOT checked.  Click Next to continue.

  4. Specify the start date and time for the initial backup (e.g. Monday at 6:00 AM). Click Next to continue.

  5. Check "Run the maintenance plan on the following" and choose "Days of the week".  Select all seven days of the week and click Next.

  6. There is no need to validate the database for incremental backups, so simply click Next to continue.

  7. In this page, you instruct the maintenance plan to perforn an incremental backup.

    • Check "Include a backup of the database in this maintenance plan".

    • Select "Backup to disk".

    • Select "Incremental backup (transaction log only)".

    • Enter the location to save the backed up transaction log file and click Next to continue.



  8. There is no need to execute SQL statements at the start or end of the maintenance plan, so just click Next to continue.

  9. Let's save the results each time the maintenance plan runs.

    • Choose to save only the last 7 reports.

    • Check "Report the maintenance plan status to the server's console".

    • Check "Email the maintenance plan report", select "Always", type the recipien't name and choose the email protocol to use.



  10. Click Finish to complete the Create Maintenance Plan Wizard.

  11. Back in Sybase Central, expand "Events" and you'll notice the event "BOE120_CMSIncrementalBackup_event" was created for our maintenance plan. Just like in the case of the full backup, we need to edit the generated SQL code a bit to properly save the backed up transaction log in a specific directory.  Click on that event

  12. By default, the backup folder and transaction log file name are the same for each subsequent backup and that will cause the backed up transaction log file to be overwritten every time the maintenance plan runs.  We can change the generated code to prevent that behaviour from happening.In the right panel, you see the SQL code for the event. Locate the Backup section (starts with "// Backup") and modify the script as follows:
    // Backup
    
UPDATE dbo.maint_plan_status SET "status" = 'BACKUP' WHERE plan_id = @PLAN_ID;
    
SET @MSG = 'Backup started on ' || CURRENT DATE || ' at ' || CURRENT TIME || '';
    
SET @REPORT = @REPORT || @MSG || '\n';
    
MESSAGE @MSG;
    
BEGIN
    
    DECLARE SUNDAY INTEGER;
    
    DECLARE FULL_BACKUP_DATE DATE;
    
    DECLARE FOLDER_NAME LONG VARCHAR;
    
    SET SUNDAY = 1;
    
    SET FULL_BACKUP_DATE = CURRENT DATE - datepart(dw, getdate()) + SUNDAY;
    
    SET FOLDER_NAME = 'D:\\BOEDBBackup\\IncrementalBackup\\' || FULL_BACKUP_DATE;
    
    SET @MSG = 'Backing up transaction log to image: ''' || FOLDER_NAME || '''';
    
    SET @REPORT = @REPORT || @MSG || '\n';
    
    MESSAGE @MSG;
    
    BACKUP DATABASE DIRECTORY FOLDER_NAME
    
    WAIT BEFORE START TRANSACTION LOG ONLY
    
    TRANSACTION LOG TRUNCATE
    
    TRANSACTION LOG RENAME;
    
END;
    
SET @MSG = 'Backup finished on ' || CURRENT DATE || ' at ' || CURRENT TIME || '';
    
SET @REPORT = @REPORT || @MSG || '\n';
    
MESSAGE @MSG;


  13. Click the Save icon on the toolbar to save the changes to the event.  You now have a maintenance plan that performs a daily incremental backup of the CMS database.


Repeat the above steps to create a maintenance plan for the Audit database, making sure that the backup directory is different (e.g. D:\AuditDBBackup\IncrementalBackup).


Testing and Executing the Maintenance Plan


You don't need to wait until the scheduled execution time to test the maintenance plan.  In Sybase Central, simply expand "Maintenance Plans" in the left panel, right-click on the plan (e.g. "BOE120_CMSFullBackup") and select Run Now from the popup menu.



After a maintenance plan executes, its result (success or failure) is recorded inside the database and appears in the "Reports" panel in Sybase Central:



Right-click on the report and select Properties from the popup menu to see the complete details.



Similarly, we can do the same procedure to test the incremental backup maintenance plan.  Here is its report upon successful execution:



Use this method to ensure your maintance plans execute as expected.


Recovering the Database From Backup


Now that you have a process to generate CMS and Audit database backups, how do you use them for recovery?

The first thing you should do is copy the backed up database and transaction log files into a new "Recovery" folder.  It is good practice to never work off the original backed up files - make a copy and work off them.  Your backup is precious and you don't want to damage or lose it!

Recovering from a full database backup is easy:

  1. Stop Server Intelligence Agent (SIA) using Central Configuration Manager (CCM)

  2. Stop the BOBJ database service

  3. In your BOBJ system, locate the CMS and Audit database and transaction log files and erase them (if present)

  4. Copy the backup copies of the CMS and Audit database and transaction log files to their appropriate location in your BOBJ system

  5. Start the BOBJ database service

  6. Start SIA using CCM


However, most likely, you'll need to recover using both your latest full database backup and incremental backups.  Here is the procedure:

  1. Copy (not move!) the CMS backup database and transaction log files to your recovery folder.  In our example, our recovery folder is D:\Recovery.

  2. Copy the SQL Anywhere files from your BOBJ installation (default C:\Program Files (x86)\Business Objects\SQLAnyWhere12\bin) to the recovery folder, but do not copy the database and transaction log files (you don't want to overwrite your backup!)We do this so that we continue to work off our recovery folder and not directly against the production BOBJ server (in case something goes wrong).

  3. Open a Command Prompt and change to the recovery directory.  Execute the following command:dbeng12 -n BOE120_eng BOE120.db -ad D:\Recovery

  4. The SQL Anywhere server dialog appears and indicates that the database was recovered successfully.

  5. Stop the SIA and BOBJ database service.

  6. In your BOBJ system, locate the CMS database and transaction log files and erase them (if present).

  7. Copy the recovered database file 'BOE120.db' and transaction log file 'BOE120.log' to its appropriate location in your BOBJ system.

  8. Start the BOBJ database service.

  9. Start the SIA using CCM.


Repeat the same steps to recover the Audit database.


Summary and Additional Resources


Always have a proper backup and recovery plan for your mission-critical BOBJ systems.  For managing the CMS and Audit databases, SQL Anywhere provides visual tools to validate, backup and recover the database.  Use maintenance plans to simplify the definition and scheduling of backups.  Don't forget to test your backup and recovery plan to ensure the procedure executes properly and behaves as expected.

Additional resources:

12 Comments
Labels in this area
Popular Blog Posts