Displaying difference between revision 7 and 6 of Wiki page:Creating and restoring Sybase database dumps
Please wait, loading revisions...
display as  
!!!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 ussue 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|WIKIPAGE:2910] 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|WIKIPAGE:2910]. 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.