The ScopeSET Support and Community Portal
         
View all tags
Tags:    No tags associated yet.

Sybase database backups

In bigger installations it might make sense to regularly create Sybase database dumps. This can also be required if a Sybase server needs to be relocated to another machine. This cookbook describes the necessary steps.

In order to use the Sybase backup facility, make sure that your Sybase server runs a Backup Server process or service:

  • On Windows, you should have a backup server service which can be started via the Services control panel, the name of the server starts with "Sybase BCKServer". Start this service.
  • On Unix, there should be an equivalent RUN_* script, i.e. in $SYBASE/ASE-15_0/install/RUN_*_BS script.

Creating Database Dumps

Starting with OpenAmeos 9.4 and 10.1, there is a new script  maintenance/sybase_backup (this script is however available on request for older installations)

CB:/displayDocument/sybase_backup.png?object_comment_id=29

The script can be started in two modes:

  • Perform Dumps, directly creates database dumps
  • Generate Dump Scripts, generates only the scripts to create database dumps
Make sure that the dump_path you specify for the script is a valid path on the Sybase server machine.

Perform Dumps

If you have OpenAmeos client installed on the Sybase server machine, you can directly create the database dumps, by setting the external perform_dump to True. The script will then create database dumps in the path specified by dump_path. The results of the dump commands will be stored in <dump_path>/dump_dbs.log. The script will also create 3 more files in <dump_path>:

  • <server-name>.users - a list of all users on the Sybase server
  • <server-name>.syscreators - a list of all users with system creation privilege
  • load_dbs.sql- commands to load database dumps

Generate Dump Scripts

If you have no OpenAmeos installation on the Sybase server machine, set perform_dump to False.  The script will in this case create the following required SQL script to dump and load the databases on the server: 

  • dump_dbs.log - commands to create database dumps
  • <server-name>.users - a list of all users on the Sybase server
  • <server-name>.syscreators - a list of all users with system creation privilege
  • load_dbs.sql - commands to load database dumps

These files are created in the qrl_files directory of your current system. 

The sybase dump commands look like this:

dump database uml_profile to "C:\Temp\uml_profile.dump"
go

To perform the database dumps on the Sybase server machine, copy dump_dbs.sql to the server, start a command shell with a Sybase environment and run the script like this:

isql -Usa -P<sa-password> -S<SERVER> -i<path>/dump_dbs.sql

The dumps will then be created in the path you have specified above.

You can use both alternatives to also run the database backups from a cron job, either using qrp in case of an OpenAmeos client on the server machine or using isql otherwise. In the latter case you need to make sure that the dump_dbs.sql script is regularely updated.

Now you have all you need to recover the Sybase server in case of a crash or a relocation.

Restoring Databases and Users

These are the steps to restore the Sybase server:

Once your OpenAmeos client is configured to connect to the new/recovered  Sybase server, start the desktop and copy&paste the users into Admin->Manage Users->Add Users.

Do the same with  <server-name>.syscreators and Admin->Manage Users->Grant Users System Creation Privileges.

Then go to the Sybase server and start a command shell with a Sybase environment. Make sure that the database dumps are available in the same dump_path directory as above and run the reload script:

isql -Usa -P<sa-password> -S<SERVER> -i<path>/load_dbs.sql

If the paths have changed, do a global search and replace on the SQL script to change the path names. The full sequence of commands to restore a database looks like this:

create database uml_profile on default = 18
go
load database uml_profile from "C:\Temp\uml_profile.dump"
go
online database uml_profile
go
sp_dboption uml_profile, "trunc log on chkpt", true
go
sp_dboption uml_profile, "select into/bulkcopy", true
go
use uml_profile
go
checkpoint
go
sp_changedbowner root, true
go

Note that load_dbs.sql assumes that you are running the commans on a server where the to be restored database(s) do not exist

In case of a corrupt database, you can copy&paste the command sequence for the database from the load_dbs.sql file into a new file and run the restore only for this database.