How to automatically backup the DDL statements?

[Former Member]

Hello experts,

I'd like to implement a complete plan for a backup of my Sybase ASE based SAP ERP system, according to the Administrator Guide and the SAP Note 1585981.

I have the "dump database" and "dump transaction" parts done and working. I have some problems with the DDL part of the plan. Ideally, I would like a script, SQL or batch (I'm working on Windows Server 2008 R2), that saves the DDL of the <SID>, sybsystemprocs, sybmgmtdb and saptools databases somewhere on the filesystem, and that I can schedule as a DBA Cockpit job or a Windows Scheduled Task.

So far, the best I could do is a batch script that runs ddlgen, like this:

%SYBROOT%\ASEP\bin\ddlgen.bat -S%DBSERVNAME% -U<user> -P<password> -Dmaster -N<database> -TDB -F%% -O<output file>

This works, but I have to write the database user and its password in plain text in the script, and the user can only be one with the sa_role, otherwise it doesn't work. So there'd be the credentials of a fully authorized user right there in plain sight - I don't like that.

Is there a better way to do this?

The Administrator Guide suggests to use sp_ddlgen, but doesn't explain how to save its output to a file. Also, I like its output less than the ddlgen.bat command because that one will also print the database options, like 'trunc log on chkpt', and sp_ddlgen doesn't.

Thanks.

[tilman_model-bosch · Accepted solution]

Dear Marcelllo

The issue with the plain text password is not nice at best and often will break security policies in a  company.

With 15.7 ESD#2 there is a new syntax in LOAD DATABASE command, which makes saving the DDL statement superfluous.

load database <DBNAME> from '<DUMPLOCATION'>'

with listonly= create_sql

The ouput generates the DDL for the database .

So it is not necessary to  save the DDL seprately.

With eralier ASE version you could still generate the DDL statement for the database , as long as the master is still available.

You might also want to review the 'sybrestore' tool which is avialable since 15.7 ESD3 (see Sybase Infocenter for documentation) .

For the database options this does not help at the moment, some of these are not automatically set ina loaded database at the moemtn.

HTH

Tilman