Which DBs needing a backup?

[Private_Member_19084]

Hello experts,

which DBs of ASE (model, saptempdb, ...) do we also have to backup excepting to the <SID>-DB?

Is there a guideline, which db is including what or which db needs to be available in case of an restore?

(worst case restore, starting from scratch)

Kind regards

[Private_Member_19084 · Accepted solution]

So according to note 1585981 the following should be enough

<SAP-SID>-Database

• Master-database
• sybsystempprocs
• sybmgmtdb
• saptools

But which one else would you recommend to backup?

[crisnormand · Accepted solution]

Hi Christian,

You only need to do backup of the databases listed in the SAP Note 1585981:

-> the SID database, because it is the database that contains your data

-> the Master, because it contains the main dataserver structure information (devices, databases, logins, roles, etc) and you cannot start an ASE without this database

-> the sybsystemprocs, because it contains the system stored procedures

-> the sybmgmtdb, because it is the Job Scheduler database

-> the saptools, because it is the DBA Cockpit database

The model is just a template, used when a new database is created, it doesn't contain relevant data.

And the temporary databases are recreated at ASE start. You are not supposed to store there data you would like permanent.

Regards,

Cris

[Private_Member_19084 · Accepted solution]

Hello Cris,

and thanks again for your big help regarding ASE.

You wrote temporary database. Which one is it? tempdb or saptempdb?

What about sybsystemdb?

[former_member3043 · Accepted solution]

Hello Christian,

tempdb and saptempdb are both temp. databases. Using BS on ASE there could be three temporary databases:

tempdb -> The default one, it will exists right after the ASE installation.

sapsatempdb -> could be created for sapsa and sa logins

saptempdb -> is used from the SAPSR3 / SAPSR3DB logins

If sapsatempdb or saptempdb aren't available (in case of a device crash) the ASE will bind these logins automatic to tempdb. This was done to avoid problems when sapsa is filling up the tempdb cause of jobs from saptools. Normally the sapsatempdb is bigger than the saptempdb, cause SAP isn't using that much temp tables during normal processing.

About sybsystemdb, this database is used for distributed transactions, example: You will have two ASEs and would like to execute procedure xyz on ASE2 but you are currently on ASE1, then you can use a remote procedure call, this can be done in a transaction from ASE1. In this case we need to be sure that the procedure on ASE2 was successful or not, for this communication the sybsystemdb is used.

In ASE for BS this isn't the case, cause you won't have this scenario, but the database is a system default database and therefore it exists, but you don't need to dump it.

Here an overview of the possible database and what they are used for:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31654.1600/doc/html/san13...

Hope this will help.

Regards

Stefan

[Private_Member_19084 · Accepted solution]

Hello Stefan,

thank you very much for helpful and detailed description.

We only have saptempdb (no sapsatempdb).

So this one will be filled up by starting ASE and it makes no sense to dump it, right?

What is the actual role of this DB?

Is it the db in between the SID-DB and the SAP-System? So will each sql-execution go over that db?

(selects, inserts, deletions, ...)

Kind regards

[Private_Member_19084 · Accepted solution]

So, is my interpretation correct regarding saptempdb?

[crisnormand · Accepted solution]

Hi Christian,

Documentation gives you a good summarize of the role of a temporary database:

SyBooks Online

"

Temporary databases are server-wide resources, and are used primarily for processing sorts, creating worktables, reformatting, and storing temporary tables and indexes created by users. Anyone can create objects in temporary databases. Many processes use them silently.

Many applications use stored procedures that create tables in temporary databases to expedite complex joins or to perform other complex data analysis that cannot be performed easily in a single step.

"

This applies to all temporary databases existing in an ASE.

In the case of the saptempdb, the above applies specifically to SAPSR3 (SAPSR3DB in a Java stack) processes, as this login is the owner of all objects in the SID database and also the SAP application login. And as Stefan explained, this temporary database is created and bound to SAPSR3 (or SAPSR3DB) because we have a lot of sapsa processing in the saptools database,  so we want to avoid the SID database to be affected by a possible full tempdb situation.

I hope this clarifies

Regards,

Cris

[Private_Member_19084 · Accepted solution]

So my question....at the moment we only have the saptempdb on SSD-devices, as we got the information this makes sense and it does not bring an improvement to put also data devices on SSD.

But if I got you right, the reading/writting is also done directly on data devices (of <sid>db) and so it would make sense to put them also on SSD, right?

I will check the online documentation of sybase again, however you know, it is very often now available...I think yesterday the complete day

Kind regards and thank you very much for your great help