Sybase Database Backup

[former_member220071]

Dear Expert,

We are using Sybase Database 15.7.0.012 with OS AIX, as i new to Sybase please guide how to take backup of Sybase Database.

As per note 158831 we tried but get below error

1> sp_config_dump @config_name='ECDDB',

2> @stripe_dir = '/inst/backup' ,

3> @compression = '101' ,

4> @verify = 'header'

5> go

Msg 2812, Level 16, State 5:

Server 'ECD', Line 1:

Stored procedure 'sp_config_dump' not found. Specify owner.objectname or use

sp_help to check whether the object exists (sp_help may produce lots of

output).

Also we want to take backup on Tape Drive instead of Harddisk & schedule backup in DB13, kindly suggest procedure.

Thanks & Regards,

Abhijit

[former_member182090 · Accepted solution]

SAP Note 158831 does not exist.

Anyway, either you have a too-early ASE version installed (15.7.0.012 sounds pretty old), or the version is right but the installation was not performed correctly. You can tell which one by finding the 'installmaster' script file and searching it for "sp_config_dump". If it exits in the script but not in the ASE server, your problem is likely the latter; if it doesn't exist, the former.

You can also help posting the output of "select @@sbssav" and "sp_version".

On backing up to tapes: all you should need to do is specify the tape drive pathname with @stripe_dir.

Note there are more direct ways of taking a backup ('dump database your-db to '..your-dump-file...') which will always work regardless of your ASE version. But given that this is a SAP environment you should really get the installation right so that you can use the dump configuration and dump history features -- but you need the right ASE version for that, and it needs to be correctly installed.

-----------------------------------------------------------------

Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):

"Tips, Tricks & Recipes for Sybase ASE"

"The Complete Sybase IQ Quick Reference Guide"

"The Complete Sybase ASE Quick Reference Guide"

"The Complete Sybase Replication Server Quick Reference Guide"

rob@sypron.nl  |  www.sypron.nl  |  Twitter: @rob_verschoor

Sypron B.V., The Netherlands  |  Chamber of Commerce 27138666

-----------------------------------------------------------------

[former_member220071 · Accepted solution]

Dear Rob Verschoor/Deepak /Konstantin,

First of all thanks for your reply, as per instruction we have down load latest Sybase Database patch level 30.

We will apply the same .

As soon as patch up gradation is done we will take Backup .

Thanks & Regards,

Abhijit

[former_member220071 · Accepted solution]

Dear Rob,

We have updated our Database Patch level to 30

1> select @@sbssav

2> go

----------

15.7.0.030

Now when we are trying to take backup it giving error shown below

1> sp_config_dump @config_name='ECDDB',

2> @stripe_dir = '/media/ECDEXP/' ,

3> @compression = '101' ,

4> @verify = 'header'

5> go

Msg 18703, Level 16, State 1:

Server 'ECD', Procedure 'sp_config_dump', Line 145:

Please execute the procedure 'sp_config_dump' from master database.

(return status = 1)

@ Deepak ,

When we are trying from dbacockpit it is now allowing us to make any changes. What other changes we need to make so we can directly take backup on tape through dbacockpit.

Kindly suggest solution.

Thanks & Regards,

Abhijit

[Former Member · Accepted solution]

Hi Abhijeet,

the command should be issued from master database (to check in what database are you after logon issue 'select db_name()', it is saptools if you are connecting with sapsa login)

In order to switch to master database, issue 'use master' before executing above mentioned script

Hope this helps,

Regards,

Konstantin

[former_member188883 · Accepted solution]

Hi Abhijeet,

Pelase refer SAP Note 1588316 - SYB: Configure automatic database and log backups

It has relevant steps as well as scripts attached for Sybase database backup.

Hope this helps.

Regards,

Deepak Kori

[former_member220071 · Accepted solution]

Dear All,

We are now able to take backup using dump database <SID> using config = 'SIDDB'

We used option

>use master

>go

Now while taking transaction backup we getting following message.

1> dump transaction SID using config = 'SIDLOG'

2> go

Msg 4208, Level 16, State 1:

Server 'SID', Line 1:

DUMP TRANsaction to a dump device is not allowed while the trunc. log on chkpt.

option is enabled.  Disable the option with sp_dboption, then use DUMP DATABASE,

before trying again.

When we trying backup on Tape Drive with option

@stripe_dir = '/dev/rmt0' ,

It gives following message

Backup Server session id is: 13. Use this value when executing the

'sp_volchanged' system stored procedure after fulfilling any volume change

request from the Backup Server.

Backup Server: 4.26.2.1: Volume validation error: failed to obtain device

information, device: /dev/rmt0/ECD.DB.20121227.140325.000 error: Not a

directory.

Thanks & Regards,

Abhijit

[sap_mk · Accepted solution]

For the first error (Msg 4208), you have set the database option "trunc log on chkpt". This tells ASE to truncate the log when CHECKPOINT runs. It is very similar to the dump transaction command, except that 1) it doesn't store the transaction log records anywhere before truncating the log, and 2) it checks the log every minute when CHECKPOINT task wakes up.

The Msg 4208 is indicating that you cannot use dump transaction command while this option is set. If you want to keep your transaction log information, then disable that option and run the dump transaction command at some regular interval. To disable it, use:

use master

go

sp_dboption SID,'trunc',false

go

You will need to take a full database backup once before you can start taking the transaction log backups.

dump database SID to ...

go

For the tape drive option, please show the full command you are using and any configured dump options. Backup Server is reporting "Not a directory" so we need to see what options are influencing the dump command.

Regards,

Mark Kusma

[former_member220071 · Accepted solution]

Dear Mark,

Thanks for your reply.

We are able to take Database Backup as well as Transaction backup on HDD.

Our main concern about how  to take backup on tape drive.

We are using Tape library for backup.

We also tried to mount tape but not succeed.

Following command we are tried for backup on tape drive.

1> use master

2> go

1> sp_config_dump @config_name='SIDDB',

2> @stripe_dir = '-i /dev/rmt0' ,

3> @compression = '101' ,

4> @verify = 'header'

5> go

Please let us know how to configure backup through DBACOCKPIT.

Thanks & Regards,

Abhijit

[Former Member · Accepted solution]

Hi Abhijeet,

i'm not sure, but why are you putting this '-i' before you device path? As per docs:

You can specify a local dump device as:

•   A logical device name from the sysdevices system table
•   An absolute path name
•   A relative path name

So, in your case, why not to remove this '-i' and give it a try?

Regards,

Konstantin

[sap_mk · Accepted solution]

Hi Abhijit,

I agree with Konstantin's post about the '-i', but also wanted to provide this link with a writeup on taking backups, including DBA Coockpit.

http://scn.sap.com/community/sybase-ase/blog/2012/12/28/backup-sybase-ase-in-sap-environment--includ...

It doesn't cover tape backups, but hopefully the removal of '-i' will resolve that problem.

Regards,

Mark

[JanStallkamp · Accepted solution]

For those wondering why Mark's link is not working any more: The mentioned blog was reported for moderation and as it was copy&paste of SAP Notes without additional information it has been rejected.

Regards,

Jan

[former_member220071 · Accepted solution]

Dear Konstantin / Mark,

Thanks for your reply we removed -i option still issue is there.

1> use master

2> go

1> sp_config_dump @config_name='SIDDB',

2> @stripe_dir = '/dev/rmt0' ,

3> @verify = 'header'

4> go

The change is completed. The option is dynamic and ASE need not be rebooted for

the change to take effect.

(return status = 0)

1> dump database SID using config = 'SIDDB'

2> go

Backup Server session id is: 12. Use this value when executing the

'sp_volchanged' system stored procedure after fulfilling any volume change

request from the Backup Server.

Backup Server: 4.26.2.1: Volume validation error: failed to obtain device

information, device: /dev/rmt0/SID.DB.20130110.112800.000 error: Not a

directory.

Msg 8009, Level 16, State 1:

Server 'SID', Line 1:

Error encountered by Backup Server.  Please refer to Backup Server messages for

details.

Thanks & Regards,

Abhijit

[Former Member · Accepted solution]

HI Abhijit,

fankly i don't know what's going on, so it might be a good idea to open up a suport message to get guys have a look at your system. Apart from that i can only suggest issuing backup command directly: dump database SIDDB to '/dev/rmt0' and see how will it work out.

Regards,

Konstantin

[former_member188958 · Accepted solution]

Hi Abhijit,

The error is likely due to "/dev/rmt0" being a tape drive rather than a file system directory.

I believe that sp_config_dump currently does not support tape drives, only file system directories, though that isn't explicitly documented.  I find no mention

of tape devices in the design specifications for the procedure, and the variable name @stripe_dir does suggest a directory.

I don't know anything about dbacockpit's capabilities for dumps to tape drives.

I would personally use the older dump syntax, same as Konstantin suggested.

DUMP DATABASE <dbname> TO <devicename>

dump database SIDDB to '/dev/rmt0'

Cheers,

-bret

[former_member220071 · Accepted solution]

Dear Konstantin/Bret,

Thanks for reply.

We getting following message while executing dump database command.

1> dump database ECDDB to'/dev/rmt0'

2> go

Msg 3052, Level 16, State 1:

Server 'ECD', Line 1:

DUMP DATABASE requires a dump configuration name because the configuration

option 'enforce dump configuration' is enabled.

Kindly suggest how to over come this message.

Thanks & Regards,

Abhijit

[sap_mk · Accepted solution]

Hi Abhijit,

Please use the following command to disable the dump configuration feature and then retry the dump command.

sp_configure 'enforce dump configuration',0

go

Regards,

Mark Kusma

Message was edited by: Mark Kusma (fixed command)

[marco_veisa]

Hi,

I hope this will be usefull for everything.

You have two ways.
The first:

Login using a console.

isql -Usapsa -X -DSID

Input Password

use master

go

dump database SID to '/inst/backup/backupname' with compression = '101'

go

Obviously, the path depends on the host operating system or storage device.

With this, you'll have your backup file with the name and path chosen.

The second:

isql -Usapsa -X -DSID

Input Password

use master

go

sp_config_dump @config_name='DUMPDATABASE',

@stripe_dir = '/inst/backup',

@compression = '101'

@verify = 'header'

go

Now if you enter sp_config_dump you will get something like:

For reference parameters, see this link:

SyBooks Online

Now, if you want a backup, all you have to do is:

dump database SID using config = 'dumpdatabase'

go

It is necessary to use the master database to make it work:

use master

go

[Former Member]

Dear siras,

It's simple.. login to sidadm level and give the below commands

isql -Usapsa -SSID -Ppassword -X

use SID

go

dump database SID to "/db/backupname.dmp"

go

..this is how are taking in suse linux...

Regards,

sudheer

[Former Member]

Dear Abhi,

we are also using the sybase database. I will take at os level using the following procedure. It's very simple.

Login as <SID>ADM and execute the below command. I think no need to use master command for taking backup.

sidadm: isql -Usapsa -SSID -Ppassword -X

go

dump database SID to "/db/<name>.dmp"

go

Regards,

venkata  sudheer akondi.

[Former Member]

Hey man

Take a look on a Blog that I wrote. I believe it will be helpfull..

http://scn.sap.com/community/sybase-ase/blog/2013/05/31/backup-challenges-on-sybase

BR  

Fred

[Former Member]

Hi  Dear

Please, Can you help me? I need know as see if the data base is running backup.

My system operative is AIX with data base sybase.

I need know the command or file log that I would like see for checking.

Thanks

[Former Member]

Hi  Dear

Please, Can you help me? I need know as see if the data base is running backup.

How to know the estimated completion time and the current percentage of the backup?

Thanks

[tilman_model-bosch]

Dear Mr Panche,

Please check the error log of Sybase Backupserver, typically this is

$SYBASE/$SYBASE_ASE/install/<DBSID>.log

With kind regards

Tilman Model-Bosch

[Former Member]

Dear Tilman Model-Bosch,

Much thanks, this is perfect.

regards

William Panche

[tilman_model-bosch]

I just relalised - there is an error in my above reply , it should say:

" typically this is 

$SYBASE/$SYBASE_ASE/install/<DBSID>_BS.log"

You realised it obviously.

The backup serer eerrror log displays messages of how far a DB dump has gone in terms of percentage.

With kind regards

Tilman Model-Bosch

[former_member220071]

Dear All,

Sorry for late reply , since due to some reason one of the log file in server deleted .

I am closing this discussion and newly open session for the recovery of database.

Thanks for your support.

Regards,

Abhijit

[Former Member]

Hi Abhijit,

Please use fallowing commands to take the sybase backup from os level ,

login with sidadm

isql -Usapsa -SSID -PMasterpassword

1>dump database SID to "/backup/SIDbkp050113.dmp"

2>go

Regards,

Prabhakar

[Former Member]

i followed your suggestion

plz help in below

[Former Member]

Hi Abhijit,

in order to use functionality you are referring to, you should run at least ASE 15.7.0.021. So please upgrade your ASE as per Note 1599814 - SYB: Installing ESDs for Sybase ASE 15.7 (UNIX + Linux)

Once upgraded, please refer to Note 1588316 - SYB: Configure automatic database and log backups

and also Note 1585981 - SYB: Ensuring Recoverability for Sybase ASE

It is also always worth reading through the following note Note 1772967 - FAQ - Important SAP Notes - Sybase ASE and apply recommendations, which applicable to your installation.

Hope this helps

Regards,

Konsatntin

[former_member188883]

Hi Abhijeet,

Instead of using command line tools to take backup why not use transaction code dbacockpit from sapgui to take database backup. This is very easy to use and you may plan the backups like we do in transaction code db13.

Regards,

Deepak Kori