Technical Articles
Backing Up the CMS and Audit Databases Using SQL Anywhere Maintenance Plans
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.
- Launch Sybase Central (click Start > All Programs > SQL Anywhere 12 > Administration Tools > Sybase Central). Close the Welcome dialog if it appears.
- Switch to the “Folders” view by clicking Folders from the View menu.
- Right-click on “SQL Anywhere 12” and select Connect from the popup menu.
- The Connect dialog appears.
- 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.
- 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.
- 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.
- 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.
- As stated earlier, it’s a good idea to validate the database before backing it up.
- In this page, you instruct the maintenance plan to perform a full backup.
- 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.
- Each time the maintenance plan runs, you can save its results and/or send them to an administrator via email.
- Click Finish to complete the Create Maintenance Plan Wizard.
- 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.
- 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;
- 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;
- 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.
- Ensure you are connected to the CMS database using Sybase Central (as in the previous section).
- In the Folders view (left panel), right-click on “Maintenance Plans” and select New > Maintenance Plan from the popup menu.
- 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.
- Specify the start date and time for the initial backup (e.g. Monday at 6:00 AM). Click Next to continue.
- Check “Run the maintenance plan on the following” and choose “Days of the week”. Select all seven days of the week and click Next.
- There is no need to validate the database for incremental backups, so simply click Next to continue.
- In this page, you instruct the maintenance plan to perforn an incremental backup.
- There is no need to execute SQL statements at the start or end of the maintenance plan, so just click Next to continue.
- Let’s save the results each time the maintenance plan runs.
- Click Finish to complete the Create Maintenance Plan Wizard.
- 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
- 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;
- 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:
- Stop Server Intelligence Agent (SIA) using Central Configuration Manager (CCM)
- Stop the BOBJ database service
- In your BOBJ system, locate the CMS and Audit database and transaction log files and erase them (if present)
- Copy the backup copies of the CMS and Audit database and transaction log files to their appropriate location in your BOBJ system
- Start the BOBJ database service
- 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:
- Copy (not move!) the CMS backup database and transaction log files to your recovery folder. In our example, our recovery folder is D:\Recovery.
- 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).
- Open a Command Prompt and change to the recovery directory. Execute the following command:dbeng12 -n BOE120_eng BOE120.db -ad D:\Recovery
- The SQL Anywhere server dialog appears and indicates that the database was recovered successfully.
- Stop the SIA and BOBJ database service.
- In your BOBJ system, locate the CMS database and transaction log files and erase them (if present).
- Copy the recovered database file ‘BOE120.db’ and transaction log file ‘BOE120.log’ to its appropriate location in your BOBJ system.
- Start the BOBJ database service.
- 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:
- Business Intelligence Help Portal: http://help.sap.com/bobi
- SQL Anywhere Backup and Data Recovery: http://dcx.sybase.com/1201/en/dbadmin/da-new-backup.html
- SQL Anywhere Maintenance Plans: http://dcx.sybase.com/1201/en/dbadmin/help-maintplan-task.html
- SQL Anywhere Community: http://scn.sap.com/community/sybase-sql-anywhere
Clear Steps. Keep Posting !!
Nice one!
I'll add a link to your article in a post I made recently: Tutorial: Installing and Using SAP Sybase SQL Anywhere Database Client
Thanks.
Why SAP add ASA 12 instead ASA 16 ?
Excelente artículo, José. It was very useful. Thanks a lot for sharing it!
Excellent!
It is a very nice and easy to follow. Most of the steps worked but I am having difficulty in offline.
When I shutdown the BI 4.1 system uing CCM and then I copy the four files mentioned above "BI4_CMS.db, BI4_CMS.log, BI4_Audit.db, and BI4_Audit.log", I get the following message -
I though may be the system is not completely down so let it sit for around 10 mnts and tried to copy again and same error. How long the system takes to come down completely? I checked the processes in task manager and I could not see sia.exe, CMC.exe, tomcat7.exe so I assumed it is down.
Any advice for this issue please?
Your help will be greately appreciated.
Thank you,
SS
you can't copy those files because you're using Sybase DB and that DB is still running.
You can't stop SQL Anywhere from CCM. It is an independent service.
Thank you for letting me know. So can you please advice how should I proceed in this regard. Your help will be greately appreciated. Do I have use client tool. Thank you,
SS
I think the service for DB is SQLANYs_SQLAnywhereForBI.
Can you please confirm?
Thank you,
SS
I"m not sure I understand your question ?
If you need to copy those files, you have to stop SQL Anywhere service first.
I was asking about how to shutdown the DB. The above service I have mentioned, is good enough? I tried and it worked so I want to confirm that I am not doing anything improper.
Thank you,
SS
Yes, this is the correct SQL Anywhere service. All SQL Anywhere service names will start with "SQLANYs_<servicename>" and the displayed name in the services panel should be (by default) "SQL Anywhere - <servicename>".
Regards,
Jeff Albion
SAP Active Global Support