unable to take backup in sybase with dump command

[Former Member]

Hello experts,

                  we have installed SAP ECC6.0 EHP6 on Sybase 15.7.0.42 on SUSE Linux 11 sp2.

                 now I am trying to take backup with dump command but unable to proceed further.

                 procedure i had followed is

                  created a folder in backup folder in /sybase

              &  from command prompt

                logged in as SYBSID.

               isql -Usapsa -SSID

              1>dump database SID to "/sybase/backup/"

              after i enter.

              it goes to second line.like show below

              2>

               is something else to be specified.

thanks

[Former Member · Accepted solution]

Hi all,

Thanks for tehe reply,

now i am able to take backup.

I had scheduled it from DBACOCKPIT, it runs fine for the first day.

it takes around 65 GB of space. I had scheduled it for a week,

now do the next backup overwrite the existing backup or will it add the content to the same file or will

it delete the old backup file and create a new one.

if it is not deleting and  adding to the same file, then in no time my disk will be filled up.

and also I had seen that there is no incremental backup option in ASE.(correct me if i am wrong).

thanks and regards

[Former Member · Accepted solution]

It will be overwritten if you do not change the dump file name. See this link for 15.7 esd #2.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1572/html/commands/...

This is from the same location

"

• If the device file already exists, Backup Server overwrites it; it does not truncate it. For example, suppose you dump a database to a device file and the device file becomes 10MB. If the next dump of the database to that device is smaller, the device file is still 10MB."

You will be better off going through the link above.

Another suggestion, will be better off using "with compression=4" clause (the number 4 you can deduce based on your performance or compression requirement. refer to the link )

so something like. 

1>dump database SID to "/sybase/backup/SID.dmp" with compression = 4

2>go

If you are on an older version of sybase ase then the command will look like below

1>dump database SID to "compress::4::/sybase/backup/SID.dmp"

2>go

This will increase the cpu utilization but will compress the dump a lot and will eventually help you in a lot of ways. More dumps can be stored on disk, plus moving them around will be easy.

Also you can stripe your dump file into multiple dump files to improve performance.

warm regards,

sudhir

[Former Member · Accepted solution]

To answer your question about incremental backup. it was added only in the new release sp100

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01165.1570100/doc/html/ca...

So if you upgrade to this version then you have incremental backups.

warm regards,

sudhir

[former_member188958 · Accepted solution]

sudhir dubey wrote:
It will be overwritten if you do not change the dump file name. See this link for 15.7 esd #2. 
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1572/html/commands/...
This is from the same location
"
If the device file already exists, Backup Server overwrites it; it does not truncate it. For example, suppose you dump a database to a device file and the device file becomes 10MB. If the next dump of the database to that device is smaller, the device file is still 10MB."

That note in the Reference Manual about the device file being overwritten if it already exists is actually no longer correct as of 15.0.2 due to a change made under CR 377674.  The original dump file is now deleted and a new one created, so the size will be correct even if the new dump is smaller.  I've just filed a documentation change request, CR 743959, to have this bullet point removed from the documentation.

Also, I note that in your first post you wrote that you had created a backup folder under /sybase.

If this same /sybase holds your databases's data and log devices, it isn't the best place to put the backups too, as a device failure would then wipe out both your database devices and your backup.  You can't get too paranoid about backups, I once worked a support case where my customer's data center got hit by lightning and fried the production system.  They switched over to the hot standby, only to be hit by lightning a second time.   Fortunately, they had their backups on tape, stored in a desk in another room, so they were able to get running again the next day.  I'm not sure their business would have survivied a flood, though, unless they also sent some backups to a remote site.

-bret

[Former Member · Accepted solution]

Hello Amar,

You might want to check note 1588316 to create dump configurations which then can be easily used in DBACOCKPIT. This will create separate files for each backup which you can clean from the OS using a script, providing the files are backed up using file system tools.

Also, take note 1864348 into account regarding an issue with "load database" on 15.7.0.042.

Kindest regards, Wilbert

[Former Member]

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

there i created a Store procedure just for simplify the commands. Than, i used to compression and split backup file to decrease the backup time.

BR  

Fred

[Former Member]

hello all,

            thanking all of you for the response.

            the old backup file was deleted and automatically created new file and backup goes fine.

           but I think this type of procedure might be a bit risking, suppose if i have to restore data of

           day-before-yesterday. so I am now planing to change backup strategy.

          i want to take a backup of each day ex: monday, tuesday .....

          one way is scheduling(for each day) it with DBACOCKPIT.

           so if i have to do it from dump command how can i do it

thanks 

[Former Member]

Hello Amar,

Using the procedure described in note 1588316 you will get one dump file per day. Files will look like this:

Database Dump: <DBSID>.DB.<DATE>.<TIME>.000

Transaction Dump: <DBSID>.TRAN.<DATE>.<TIME>.000

So, no backup will be overwritten as the timestamp will make the files unique.

I use DBACOCKPIT to schedule the dumps for the transaction log (several times a day, which results in several dump files for the transaction log), and the databases master, sybmgmtdb, saptools and <SID> (all resulting in separate files for each database).

Kindest regards, Wilbert

[Former Member]

Amar,

In case you are worried about getting in a recovery situation then do make sure that you keep a copy of the backups on a location on a storage system separate from your database server.

Imagine your server or storage on which the database server resides is hosed then how do you recover your databases?

Most large organization will deploy complex backup to tape, dr site, offsite backup  strategies to make sure they are able to get their hands on their application applications in case a true disasters (disk corruption, hurricane, flood, fire) falls.

If your organization does not have a tape backup solution then the least you can do is to make sure the backups are copied over to a different storage system than the one on which your database server is. You can take the backup files and then copy it over to a separate server using a simple ftp or scp command on unix via a scheduled script. Other cheap options may be backup to a nfs storage or a shared storage.

Good luck with your deployment.

warm regards,

sudhir

[Former Member]

Hello Wilbert,

I am trying to schedule backups of master , sybmgmtdb and saptools DB from DBACOCKPIT. But I cannot find any general instructions or SAP Notes to do that. Can you advice me in this regards.

I have already configured my DB backup and Tran Log backup to run from my DBACOCKPIT.

Thanks in advance,

Regards

Surajit.

[Former Member]

Hello Surijat,

Once you have created the dump configurations from note 1588316, you can use these to also backup other databases. The configuration specifies the target location for the backup, so for master you can use:

dump database master using config = '<SID>_DB'

This will create a dump of master (file = master.DB.<DATE>.<TIME>.000) in the location specified in the dump configuration. Same goes for saptools and sybmgmtdb.

You can schedule these from the DBA Planning Calendar from DBACOCKPIT. Just schedule a SQL Script and enter the dump command specified above.

Kindest regards, Wilbert

[Former Member]

Hi Wilbert,

URGENT

i am trying to configure automatic backup using note 1566316, but i am unable to understand where should type those scripts?,
i am really new to sybase and linux, please help me in this regard. i am really helpless.

Please help me, with proper guidance,

Thanks .

[Former Member]

Dear Amar,

Use the Sap note 1588316 to configure automatically in which you have to use sp_config_dump in isql console and then schedule it through dbacockpit which will avoid overwriting the existing file and you will have list of dump files

Thanks

Asim

[Former Member]

your command will go like

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

2>go

the file name also was missing besides the go statement as Bret mentions.

[Former Member]

Amar,

Press Go

[former_member188958]

I think you just need to enter the default isql batch terminator "go".  

The first letter of the batch terminator has to be the first character on the line, i.e.

the line has to be "go", it doesn't work to enter "<space>go"

or on one line to have "dump database SID to "/sybase/backup/" go".

1> dump database SID to "/sybase/backup/"

2> go