Note 1611715 - SYB: How to restore a Sybase ASE database server (Windows)

Summary

Symptom

You need to restore an SAP Sybase Adaptive Server Enterprise (ASE) database instance on Windows

Other terms

'DUMP DATABASE', 'LOAD DATABASE',

Reason and Prerequisites

You are operating SAP with an ASE database server. ASE runs on Microsoft Windows. You need to restore the ASE database server.

Solution

The instructions below provide an outline of what needs to be done to restore the ASE server on a Windows operating system.

Assumptions:

• The ASE server version is 15.7 ESD#2 or higher

           (additional information is provided at the end of this note for ASE version <15.7 ESD#2)

• The 'Dump History' feature has been enabled (default in SAP systems) and an up-to-date copy of the dumphist file is available

• The Windows system is online or has been restored from a Windows image (including Windows registry entries, users, and environment settings)

• The file system <DRIVE>:\sybase\<SID> is available; all the database devices and databases are damaged and have to be recreated (including the master device)

• Current dumps of the 'master', 'sybmgmtdb', and 'saptools' databases are available.

• A current dump of the <DBSID> database is available, along with all the required dumps from the transaction log

• A current copy of the ASE server configuration file is available

If the file system of the Sybase software installation (that is, <DRIVE>:\sybase\<SID>) is not available, restore it from a file system backup. If you don't have a file system backup, you will have to reinstall ASE with the SAP installer: - install an empty SAP Netweaver System, for example, and then load your database backups into the ASE server. It is not possible to reinstall the ASE software standalone using the ASE installer from the RDBMS installation medium provided by SAP, since the necessary SAP OEM license can be installed only using the SAP installer.

Recreate the file systems and directories for the database device files, as required. In a typical SAP system these are:

- <DRIVE>:\sybase\<SID>\sapdata_[1-n]
- <DRIVE>:\sybase\<SID>\saplog_[1-n]
- <DRIVE>:\sybase\<SID>\sybsystem
- <DRIVE>:\sybase\<SID>\sapdiag
- <DRIVE>:\sybase\<SID>\sybtemp

Remark: If the file system(s) <DRIVE>:\sybase\<SID>\saplog_[1-n] have not been corrupted, the device files will probably hold transactional data which have not yet been dumped to a transaction log dump. In this case, perform an emergency dump of the transaction log in this case (see below). Make sure you retain a good offline copy of the device files before you recreate the file systems /sybase/<SID>/saplog_[1-n].

Rebuild the ASE server

If the master device of the ASE server has been corrupted, you need to rebuild the ASE server. The simplest way to do this is to use the response files for the command line tool 'sybatch.exe'.
The following files are relevant for the restoration of the ASE server:

• sqlsrv.res  - main response file to recreate the ASE server

• bsrv.res - response file for the backup server

If the original response files used during installation are no longer available, download the sample response files attached to this note.
Review the content of the files and ensure that it is suitable for your installation.
In particular, review and adapt these parameters as needed:

file sqlsrv_sample.res:
sybinit.boot_directory:<DRIVE>:\sybase\<DBSID> 
sybinit.release_directory:<DRIVE>:\sybase\<DBSID>
sqlsrv.network_hostname_list:<HOSTNAME>
sqlsrv.network_port_list:<port - 4901_is_default>
sqlsrv.server_name:<DBSID>
sqlsrv.sa_password:<sa_password>
sqlsrv.master_device_physical_name:<DRIVE>:\sybase\<DBSID>\sybsystem\master.dat
sqlsrv.master_device_size: 400
sqlsrv.master_db_size:250
sqlsrv.sybsystemprocs_device_physical_name:<DRIVE>:\sybase\<DBSID>\sybsystem\sysprocs.dat
sqlsrv.sybsystemdb_db_device_physical_name:<DRIVE>:\sybase\<DBSID>\sybsystem\sybsysdb.dat
sqlsrv.tempdb_device_physical_name:<DRIVE>:\sybase\<DBSID>\sybtemp\tempdbdev.dat
sqlsrv.errorlog:<DRIVE>:\sybase\<DBSID>\ASE-15_0\install\<DBSID>.log
sqlsrv.ase_service_account_name:<HOSTNAME>\syb<dbsid>
sqlsrv.ase_service_account_password:<password_for_syb<dbsid>>
sqlsrv.default_backup_server:<DBSID>_BS
sqlsrv.xpserver_network_hostname_list:<HOSTNAME>
sqlsrv.xpserver_network_port_list:<port_for_xp_defualt_4904>
sqlsrv.xp_service_account_name:<HOSTNAME>\syb<DBSID>
sqlsrv.xp_service_account_password:<password_for_syb<dbsid>>

bsrv_sample.res
sybinit.boot_directory:<DRIVE>:\sybase\<DBSID>
sybinit.release_directory:<DRIVE>:\sybase\<DBSID>
sqlsrv.server_name:<DBSID>
sqlsrv.sa_password:<sa_password>
bsrv.server_name:<DBSID>_BS
bsrv.errorlog:<DRIVE>:\sybase\<DBSID>\ASE-15_0\install\<DBSID>_BS.log
bsrv.network_port_list:<bssrv_port_4902_default>
bsrv.network_hostname_list:<HOSTNAME>
bsrv.bs_service_account_name:<HOSTNAME>\syb<dbsid>
bsrv.bs_service_account_password:<password_for_syb<dbid>>

Review the path and size of the ASE devices that are to be created (master, systemprocs,..). Ensure that the sizes fit your current device sizes. As it is almost never necessary to increase the size of the master device and the master database, the default value 400MB for the device size and 250MB for the master database size should be correct in most cases.
Review user names, passwords, ports, and hostname entries for correctness.
After you have verified that the file matches your needs, call the command sybatch.exe as the ASE software owner '<syb>sid' to recreate the server.

Example:

sybatch.exe -r ASE-15_0\sqlsrv.res

This command will rebuild a new ASE server. It will create a new master device and a new master database; load the utf-8 character set and set the binary sort order in ASE. A log file of the sybatch.exe command is written to %SYBASE%\ASE-15_0\init\logs\log<mmdd>.<nnn> , where <mmdd> is the month and day, and <nnn> is a three-digit number, for example,  001.
Important: Review the contents of that log file. Ensure that the rebuild of the ASE server has worked as expected. Ensure that utf-8 has been installed as the default character set and that the binary sort order is set.
The ASE error log file should contain an entry indicating that the default sort order is now 'binary' (ID = 25) on top of default character set 'utf8' (ID = 190).

Note that since you are rebuilding the master database from scratch, the default administrative login 'sa' with the password provided is now active. The SAP administrative login 'sapsa' does not yet exist on the server.

Recreate the backup server

If required, you can recreate the backup server by calling the file 'bsrv.res' with the sybatch.exe command. Review the contents and then recreate the backup server.
Example:

sybatch.exe -r bsrv.res

Review the contents of the corresponding log file %SYBASE%\ASE-15_0\init\logs\log<mmdd>.<nnn> 

Restart the server in single user mode

Switch to the directory %SYBASE%\ASE-15_0\install. Copy the file 'RUN_<SID>.bat'  to a file 'RUN_<SID>_SINGLE_USER_MODE.bat'.
Edit the new file and add the startup option '-m' at the end of the file.

Start ASE by calling this batch file from a DOS command prompt.
Imporant: ASE is now running as a user process , not as a service. Don't close the DOS command shell where you started ASE as long as the ASE server is running!

Load the master database

If you have not yet done so, start the backup server.
The original 'master' database has to be loaded with the 'load database' command. Log in to the ASE Server with isql (or another SQL editor) and issue the 'load database' command. ASE must be running in single user mode in order to load a master database. Use the login 'sa' to log on to ASE.

Example:
1> load database master from '<DUMPFILE>'
2> go

Replace <DUMPFILE> as appropriate. If your ASE version is ASE 15.7 ESD#2 or higher and the ASE configuration parameter 'enable dump history' has been set (default for SAP environments), you will get the the correct load command by executing the SQL command:

1> load database master with listonly = 'LOAD_SQL'
2> go

ASE shuts down automatically after completing the master database load.

Recreate the temporary database(s)

Recreating temporary database devices is an optional step. You can perform this step now or at a convenient point in time later.
Before you restart ASE, recreate the devices for any additional temporary database(s) you may have created. It is sufficient if you create the device(s) as an empty file in the correct location with correct permissions in the file system.
The device of the system temporary device should already exist (it was recreated during ASE initialization). As a minimum, the device of the system temporary database must exist.

If you do not know the device files of the temporay device files, start ASE normally and get the device file names either from master catalog sysdevices or from the messages in the ASE error.log.

Reconfigure ASE to your standard configuration and restart ASE as a Windows service

During the initialization of the ASE server, a new configuration file <DBSID>.cfg was created in directory %SYBASE%\%SYBASE_ASE% with an initial default configuration. Replace it with the saved copy of the file <DBSID>.cfg.
Restart ASE using the Windows service (don't use the RUN_<DBSID>.bat file).

Perform an emergency dump of the transaction log

This step applies if a database is offline, but the device files containing the log segment are still available. In a productive SAP system this will be the case for the database '<DBSID>'.
Log in to ASE with isql as user 'sapsa' and execute the command:

1> dump transaction <DB_NAME> to '<DUMPFILE_LOCATION>' with no_truncate
2> go

Replace <DUMPFILE_LOCATION> as appropriate. Replace <DB_NAME> with the name of the database.
With ASE version 15.7 SP100 and higher, there is also a tool 'sybdumptran.exe' available that can be used to perform an emergency backup of unsaved parts of the transaction log. The advantage of this tool is that it can also be used while the database server itself is offline. For the syntax of the tool 'sybdumptran', refer to the online documentation.

Load user databases

Steps to perform:
* Drop and recreate the databases and devices for load
* Load the database and the transaction logs (if applicable)
* Set the databases online.

You have to perform these steps for the database 'saptools', <DBSID>, and 'sybmgmtdb'.

1. Drop and recreate the databases and devices for load.

              The assumption is that you lost the ASE disk devices in the file systems; hence you need to recreate the disk devices. To be able to recreate devices, you have to drop the non-existing devices from the catalog and the databases on these devices first.

              Log in to ASE with isql as user 'sapsa' and run the command:

              1> load database <DB_NAME> with listonly = 'CREATE_SQL'

              2> go

              Replace <DB_NAME> with the name of the database you want to recreate. The above 'LOAD' command searches for the location of the latest database dump using the 'dumphist' file and extracts the DDL command for its devices, the database itself, and the set databases options from the header information in the dump. Save the output to a file.

              Drop the database and all its devices from the master catalog.

              1>drop database '<DB_NAME>'

              2>go

              1>exec master sp_dropdevice '< DB_DEVICE>'

              2>go

              Replace <DB_NAME> with the name of the database . Replace <DB_DEVICE> with the device name. If the database has been created on several devices, drop all the devices of the database.

              Finally recreate the devices and the database using the previously saved output. To speed up creation of a large database, it is recommended that you edit the 'CREATE DATABASE' command and add the 'FOR LOAD' clause.

              Example:

              1>CREATE DATABASE <DB_NAME> ON data<DB_NAME>_1 = '40G'

              2>LOG ON log<DB_NAME> _1 = '10G'

              3> WITH lob_compression = 100, compression = page

              4>FOR LOAD

2. Load the database and transaction logs.

              To generate the load sequence for the database, log in to ASE with isql as user 'sapsa' and execute the command:

              1>load database <DB_NAME> with listonly = 'LOAD_SQL'

              2>go

              Save the output to an SQL command file and execute it in isql.

3. Set the database online

              After the database dump and all transaction logs have been loaded, you need to bring the database online using the command 'online database':

              1>online database <DB_NAME>

              2>go

4. Restart the job scheduler

              After you have loaded all databases including 'sybmgmtdb' , restart the job scheduler. Log in to ASE with isql as user 'sapsa' and execute the command:

              1> exec sybmgmtdb.. sp_sjobcontrol '','start_js'

              2> go

Additional information:

After you have completeted the load, double check that the correct database options have been set - before you start the SAP system.

Mandatory DB options for SAP are 'ddl in tran', 'allow nulls by default', 'allow wide dol rows', 'page compression' These have to be set whatever the case.
Mandatory for a production system are database options 'enforce dump tran sequence' and 'full logging for all' (compare SAP note 1585981 )

The options listed below are set per default for the SAP database; they are recommended, but not mandatory:

'deferred table allocation' (ASE Version 15.7.0.040 and higher)
'deallocate first text page' (ASE version '15.7.0.043' and higher)
'allow incremental dumps' (ASE Version '15.7.0.100' and higher)

!Attention:
Set the database options immediately after the database has been brought online and before any user connects to the database. If any changes to the database contents are made before you set 'enforce dump tran sequence', you have to get a full database dump before you can set the option.

Additional information if your ASE Version is < 15.7 ESD#2

In ASE version < 15.7 ESD#2 the initial password for the user 'sa' must be NULL during initialization of the ASE server. Leave the password entries in all the resource files empty before building the server.

In ASE Version < 15.7 ESD#2 the 'LOAD DATABASE' command cannot be used to retrieve the device and database DDL statements from the database dump itself (see above). Instead you have to retrieve the information from the system catalog in the master database (hence the master database needs to have been loaded and be available).

If you need to retrieve the DDL statement for the database devices execute the command 'ddlgen.bat' (which is located in directory %SYBASE%\ASEP\bin) as user syb<sid> as below:

    ddlgen -S<DBSID> -Usapsa -P<PASSWORD> -TDBD -N%

Replace <DBSID> with the name of your ASE server; replace <PASSWORD> with the correct password for user 'sapsa'.
Save the output to file. Open the file with an editor and remove the DDL statements for the system devices 'master' , 'sysprocsdev', 'systemdbdev', 'tempdbdev' and other existing devices which you do not need to recreate.
Then execute the DDL statements for the missing devices in 'isql'.

If you need to create the DDL statement for a database, log in to ASE with isql as user 'sapsa' and execute the SQL command:

1>exec master..sp_ddlgen 'database','<DBNAME>' 
2>go

Replace <DBNAME> with the name of the database.

Header Data

Release Status:	Released for Customer
Released on:	16.09.2013  08:52:53
Master Language:	English
Priority:	Recommendations/additional info
Category:	Consulting
Primary Component:	BC-DB-SYB Sybase ASE Database Platform
Secondary Components:	BW-SYS-DB-SYB BW on Sybase ASE Database Platform