FAQs for Sybase

Can we protect data on non-global zones if the File System iDataAgent is installed only on the global zone?

Yes. We can protect file system data on non-global zones if the File System iDataAgent is installed only on the global zone. However, in order to enable consistent backups of application specific data on the non-global zones, you will need to install the corresponding application specific iDataAgent on the non-global zone.

How do we protect Sybase data on a non-global zone?

In order to enable consistent backups of Sybase data on a non-global zone, you need to install the Sybase agent on the non-global zone.

What folders, files and changes are created and performed when I install the agent?

When does a non-full backup automatically get converted to a full backup?

A non-full backup is automatically converted to a full backup in the following situations:

• First backup of the subclient.

• Re-associating a subclient to another storage policy.

• Promote a secondary storage policy copy that is not synchronized with a primary copy (for all the subclients of a storage policy).

• If a backup job within the most recent backup cycle is pruned or disabled from a primary copy.

• CommCell Console Migration operation.

• When a Sybase database create time is changed since the last backup.

Are remote backup servers supported?

No. Remote Backup Servers are not supported. You can only change the backup server that is associated to a Sybase server instance from the available backup servers with in the Sybase server environment.

How are Transaction Logs truncated?

Truncation of transaction logs use the following rules:

• The software truncates the transaction logs when a transaction log backup completes.

• Transaction logs will be truncated every time a checkpoint is processed, if the database is in truncate mode.

If the log segments of a database are on a separate device from the data segments, then the transaction log backup automatically truncates the in-active part of the transaction log.

If the log segments of the database are on the same device as the data segments, the full backup automatically truncates the in-active part of the transaction log.

Can we perform a table level restore?

Currently, table level restore for Sybase database is not supported. However, you can create a new database and restore your backup to this new database (target database). After the restore, you can truncate original table in the source database and replace this table with the table in the target database using bcp export/import commands.

Ensure that you have the latest full backup i.e., the backup prior to the corruption or loss of a table that you want to restore.

Use the following steps to restore a table:

1. From the CommCell Browser, navigate to Client Computers| <Client> | Sybase.

2. Right-click the <Instance>, point to All Tasks and then click Browse and Restore.

3. Click View Content in the Restore Options dialog box.

4. In the right pane of the window, select one or more databases that you wish to restore. Click Recover All Selected.

5. Select the Rename Databases check box and click Advanced.

   • Click the Options tab.

   • Click the Target Database column and type the new database name.

   • Click OK.

     Note

     When you rename a database, make sure that the tables in the database are not referencing to or referenced by tables in any other databases.

6. Click OK in the Sybase Restore dialog box. A new database (target database) is created and restored with the latest backup.

7. Truncate the table that is corrupted in source database.

   Example:

   use db1
   truncate table_abc

8. Run the following command from the target database:

    
   bcp <db_name>.owner.<table_name> out <file_name> -Y -U<user_name> -P <password> -S <server_name>

   Example:

   use db2
   bcp db2.owner.table_abc out file1 -y -sa -pwd -SYB1

9. Run the following command from the source database:

    
   bcp<db_name>.owner.<table_name> in <file_name> -Y -U<user_name> -P <password> -S <server_name>

   Example:

   use db1
   bcp db2.owner.table_abc in file1 -y -sa -pwd -SYBX

10. Execute the query in source database to confirm that the table is successfully restored.

Can you backup Sybase IQ Data?

Yes. You can use File System Agent with Pre/Post Scripts to quiesce/shutdown and backup Sybase IQ data. You cannot use Commvault's Sybase iDataAgent to backup Sybase IQ data as the backup commands used for IQ are different from the commands used for Adaptive Server Enterprise (ASE) data backup.

You need to install the Commvault File System Data Agent for Unix and Windows before you backup Sybase IQ data.

Backup:

Use the following steps to backup Sybase IQ data:

1. Create a directory to store all your Sybase IQ backup data.

   Example: /backup_Commvault

2. Create scripts to backup Sybase IQ databases. Use the following commands to backup the Sybase IQ data:

   BACKUP DATABASE
   [ backup-option... ]
   TO archive_device [ archive-option... ]
   ... [ WITH COMMENT string ]

   Examples:

   BACKUP DATABASE 
    FULL
    TO '/backup_Commvault' SIZE 15000000
    BACKUP DATABASE 
    INCREMENTAL SINCE FULL
    TO '/backup_Commvault' SIZE 15000000

3. Create a subclient for Commvault's File System iDataAgent and add the content from /backup_Commvault directory.

4. Perform or Schedule regular backups for this subclient (which includes the Sybase IQ data from /backup_Commvault directory).

Restore:

You can restore the backup files from /backup_Commvault directory.

Use the following command to restore the Sybase IQ data:

RESTORE DATABASE 'database-name' 
 [ restore-option ... ]
 FROM 'archive_device'

Examples:

RESTORE DATABASE 'iqdemo'
 FROM '/backup_Commvault'

Can we load a master database while performing a Cross-Platform restore?

No. Sybase do not support loading a master database while performing a cross-platform restore.

Is cross-platform loading available for transaction dumps?

No. Cross-platform loading is only available for database dumps and not to the transaction dumps.

Can we restore from the transaction log backups?

Yes. You can restore the transaction logs along with the database to a specific point in time. The point in time should be in between the start and end point of a transaction log. Point in Time restore will restore the database followed by transaction logs restored until the point of time. The nearest database backup job before the point-in time will be picked up followed by one or more transaction log backups. The immediate transaction log backup after a Point in Time will be restored.

For example:

Let us assume that you have performed a Full backup (F1) and transaction log backup (L1) for the first time at 1 P.M. Subsequently you have performed L2 at 3 P.M. Later, you have performed L3, F2, L4,L5 etc at subsequent intervals. If you perform a point in time restore for the transaction log backups at 2 P.M, the Full backup (F1) along with the log backups of L1 and L2 (transaction log after point in time) will be restored.

We do not support the restores from the transaction log backups that are not truncated. Transaction log jobs picked for Point in Time restore should fall with in one backup cycle and not across job cycles.

You can perform a point of time restore of transaction logs for clients and CommServe on different time zones.

Can I schedule step-by-step restores?

No. You cannot schedule step-by-step restores.

Can we perform SQL Anywhere Backups and Restores using Sybase iDataAgent?

No. We currently support Sybase ASE for backups and restores. However, we can perform SQL Anywhere backups and restores using Commvault's file system iDataAgent with pre-post scripts. See Configuring Sybase SQL Anywhere, Performing SQL Anywhere Backups and Restoring SQL Anywhere Backups for more information on how to configure and perform SQL Anywhere backups and restores.

How do I get the size of the master database in the event of a disaster recovery?

In the case of a disaster recovery, use the QS_GetSybaseJobList QScript command to view and alter the size of the master database.

Usage:

qoperation execscript -sn QS_GetSybaseJobList -si "@i_clientName = 'clientname'" -si "@i_instanceName='instanceName'" -si "@i_startTime = 'starttime'" -si "@i_endTime = 'endtime'" -si "@i_databaseName = 'dbname'" si "@i_noTimeRestriction = '0 or 1'" si "@i_enableDateTimeFormat = '0' or '1'"

Master Database Example

F:\Program Files\CommVault\ContentStore\Base>qoperation execscript -sn QS_GetSybaseJobList -si "@i_clientName='sybserver4'" -si "@i_instanceName='S155syb4'" -si "@i_ startTime='07-21-2014 01:00:00'" -si "@i_endTime='07-21-2014 15:00:00'" -si "@i_databaseName = 'master'" -si "@i_noTimeRestriction = '0'" -si "@i_enableDateTimeFormat = '1'"

QScript[QS_GetSybaseJobList] CS[leonard64] DB[CommServ] SP[QS_GetSybaseJobList]
 Qscript Output:
 jobid DatabaseId DatabaseName BackupLevel backup_start_time backup_end_time logicalDeviceName physicalDevicePath DBSize_in_MB
 ----- ---------- ------------ ----------- ----------------- --------------- ----------------- ------------------ ------------
 112404 1 master FULL 2014-07-21 13:41:24.000 2014-07-21 13:56:26.000 master /sybase/syb155/data/master.dat 100
 Qscript Execution Succeeded!

Database Example

F:\Program Files\CommVault\ContentStore\Base>qoperation execscript -sn QS_GetSybaseJobList -si "@i_clientName='sybserver4'" -si "@i_instanceName='S155syb4'" -si "@i_ startTime='07-21-2014 01:00:00'" -si "@i_endTime='07-21-2014 15:00:00'" -si "@i_databaseName = 'db4'" -si "@i_noTimeRestriction = '0'" -si "@i_enableDateTimeFormat = '1'"

Database Output

QScript[QS_GetSybaseJobList] CS[leonard64] DB[CommServ] SP[QS_GetSybaseJobList]
 Qscript Output:
 jobid DatabaseId DatabaseName BackupLevel backup_start_time backup_end_time logicalDeviceName physicalDevicePath DBSize_in_MB
 ----- ---------- ------------ ----------- ----------------- --------------- ----------------- ------------------ ------------
 112404 13 db4 FULL 2014-07-21 13:41:24.000 2014-07-21 13:56:26.000 db4 /sybase/syb155/data/db4.dat 1400
 112404 13 db4 FULL 2014-07-21 13:41:24.000 2014-07-21 13:56:26.000 db4_log /sybase/syb155/db_log/db4_log.dat 80
 112407 13 db4 INCR 2014-07-21 13:57:05.000 2014-07-21 13:57:39.000 N/A N/A N/A
 112408 13 db4 INCR 2014-07-21 13:58:19.000 2014-07-21 13:58:49.000 N/A N/A N/A
 Qscript Execution Succeeded!

How do I separate the data device and log device for a Sybase database?

it is required to have data and log existing on separate devices to attain transaction log backups. A user cannot run a transaction log backup if data and log are on a same device for a particular database. Either create a new database having a data and a log device or alter existing database to have the data and the log device separate. Create a database command to have separate data and log device during database creation. If a database already has data and log on the same device, separate the data and log device using the step below.:

use master 
 Go 
 alter database <dbname> log on logdevice
 GO
 sp_dboption <dbname> , ‘single user’, True
 Go
 sp_logdevice <dbname>,<logdevice> 
 Go 
 dump tran <dbname> with truncate_only
 go 
 create table dummy (a char(1800)) go insert into dummy values (“aaa”)
 go 50
 drop table dummy 
 go 
 dbcc traceon (3604) 
 go 
 dbcc findstranded (<dbname>) 
 go 
 dump tran <DB-NAME> with truncate_only 
 go 
 sp_helpdb <dbname> --- Verify data and log is separate 
 go
 sp_dboption <dbname>,single,false 
 go

Example:

The database “mix” has data on logs initially on same device

Change log device and separate it from data device.

1> alter database mix log on dev2="10M" 
2> go 
Extending database by 2560 pages (10.0 megabytes) on disk dev2 
Warning: Using ALTER DATABASE to extend the log segment will cause user thresholds on the log segment within 128 pages of the last chance threshold to be disabled. 
1> sp_logdevice mix,dev2 
2> go 
DBCC execution completed. If the DBCC printed error messages, contact a user with a System Administrator (SA) role. 
DBCC execution completed. If DBCC printed error messages, contact a user with a System Administrator (SA) role. 
syslogs moved. 
The last-chance threshold for database mix is now 16 pages. 
(return status = 0)
 1> dump tran mix with truncate_only 
2> go
1>use mix 
 2>go

Create a temporary table and drop it.

1>create table dummytab (a char(1800)) 
2>go 
1>insert into dummytab values ('aaa') 
2>go 50 
(1 row affected) 
50 xacts:
1> drop table dummytab 
2> go 
1> use mix 
2> go 
1> dbcc traceon(3604) 
2> go 
 DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role. 
1> dbcc findstranded (mix) 
2> go 
Found 1 stranded extent(s) in database 8. 
DBCC execution completed. 
If DBCC printed error messages, contact a user with System Administrator (SA) role. 
1> dump tran mix with truncate_only 
2> go 
1> dbcc findstranded (mix) 
2> go 
There are no stranded extents in database 8. 
DBCC execution completed. 
If DBCC printed error messages, contact a user with System Administrator (SA) role. 
1> sp_helpdb mix ->Check if mix data and log status is removed 
2> go 
name db_size 
owner dbid 
created 
durability 
lobcomplvl 
inrowlen 
status
---------------- ---------------------------------------------------- 
-------------------- ---------------- 
------------------------------------------------ ----------------------------------------
 ---------------------------------------- 
-------------------------------- 
-------------------------------------------- 
mix 30.0 MB 
sa 8 
Mar 13, 2014 
full 
    0 
NULL single user 
(1 row affected) 
device_fragments size usage 
created free kbytes
 ------------------------------ ------------- -------------------- 
  -------------------------
 ----------------
dev1 20.0 MB data only 
Mar 13 2014 4:07PM 17056 
dev2 10.0 MB log only 
Mar 20 2014 11:11AM not applicable 
-------------------------------------------------------------------------------------------------------------------------------------- 
------------------------------------------------------------------------------------------------------------------------------------- 
__
log only free kbytes = 
10168 
(return status = 0) 
SP_dboption mix , “single user”, false

Are there additional steps to take when the Sybase Agent is installed on an existing client?

If you install the agent on an existing client, you must run the cvpkgchg command to update the group permissions after you install the agent. You can use this command to set the group permissions to the correct level, which prevents the backup job from failing. For information on how to change the group permissions, see Changing UNIX Groups and Permissions on UNIX Clients.

Is there a way to back up transaction logs with a separate storage policy?

Yes. You can associate an incremental storage policy with the data storage policy. After you make the storage policy association, the transaction log backups use the incremental storage policy.

How can you place a Sybase server offline?

Follow these steps to place a Sybase server offline:

1. To connect to the Sybase server using ISQL, execute the following command with suitable parameters:

   isql -U<username> -P<password> -S<server name>

2. Execute the following command to shut down the Sybase server:

   shutdown

Alternatively, for Windows, you can also shut down the Sybase server by stopping the dataserver service SAP SQLSERVER_<sybase_servername>.

For example, if ASE server name is "winsybase", then stop the service SAP SQLSERVER_WINSYBASE.

Can I use the Commvault software to protect SAP Advantage Databases?

You can use the File System Agent to protect SAP Advantage Databases. Perform the following operations to protect SAP Advantage Databases:

1. Configure pre- scripts and post-scripts that use the SAP Advantage native database commands (Subclient Properties dialog box, Pre/Post Process tab, Pre-Backup Process box, Post-Backup box).

2. Perform backup operations on the subclient.