Log backup after catastrophic failure

The theory says, that a database admin should always have up to date backups... unfortunately this is not always true. It is possible that you have full database backup, you perform transaction backups every 3 hours, you even have the backup and recovery plan, but... Sybase due to some hardware failure stopped working and you cannot start it and the business waits and waits...

There is a utility called sybdumptran that allows to make a transaction log backup despite the non working sybase server.

Take a look at the following SQL code:

DISK INIT NAME ='MyDbDataDev', 
     PHYSNAME = '/opt/sap/databases/MyDbDataDev.dat', 
     SIZE=10240
GO

DISK INIT NAME ='MyDbLogDev', 
     PHYSNAME = '/opt/sap/databases/MyDbLogDev.dat', 
     SIZE=2560
GO

CREATE DATABASE MyDb
ON MyDbDataDev = '20M'
LOG ON MyDbLogDev = '5M'
GO

DUMP DATABASE MyDb TO '/home/dumps/MyDb_full.dmp' WITH INIT
GO

USE MyDb
GO

CREATE TABLE TEST(info char(30))
GO

INSERT INTO TEST VALUES('Before the 1st log dump')
GO

DUMP TRAN MyDb TO '/home/dumps/MyDb_log_1.dmp'
GO

INSERT INTO TEST VALUES('Never dumped')
GO

SHUTDOWN

As you see a new database on new devices has been created. Next we took a full backup, next user inserted one record, next the transaction low was dumped, next the user continued to insert the next record and then something went wrong and server stopped! Now you will see how to take a log dump despite stopped server:

[root@SAPSybase ~]# cd /opt/sap/ASE-16_0/bin

[root@SAPSybase bin]# ./sybdumptran -m /home/dumps/MyDb_full.dmp -o /home/dumps/MyDb_emergency_log.dmp

Opening output-file '/home/dumps/MyDb_emergency_log.dmp'.
Opening metadata-file '/home/dumps/MyDb_full.dmp'.
Opening log devices used by database:
        Opening device "MyDbLogDev", path "/opt/sap/databases/MyDbLogDev.dat".
Warning: The page 5125 from the dump header is not a valid first log page. Scanning the log fragments for a valid first log page.
Building run-lists using first log page=5127.
Finished building run-lists, number of log pages=2, first log page=5127, last log page=5128.
Dumping log pages to output file "/home/dumps/MyDb_emergency_log.dmp".
Finished dumping 2 log pages.
Sybdumptran completed without errors.

[root@SAPSybase bin]# chown sap:sap /home/dumps/MyDb_emergency_log.dmp

[root@SAPSybase bin]# ll  /home/dumps/

total 23220
-rw-r-----. 1 sap sap    28672 Jul 30 22:37 MyDb_emergency_log.dmp
-rw-r--r--. 1 sap sap  1091584 Jul 30 22:31 MyDb_full.dmp
-rw-r--r--. 1 sap sap    36864 Jul 30 22:34 MyDb_log_1.dmp

This example presents the typical use of the utility:

• option -m points to a recent full or log dump of the database. This option can point to an extra generated minimal file using the option -g as well. The idea is, that the utility needs to know the structure of the database, specially where the log devices are located. This file should be considered only as a metadata file, so file that allows to describe the database (remember that the sybase server may be non functional at the moment)
  The better would be to point here to recent log backup, as than the utility would know about the correct sequence number that should be assigned to currently generated transaction dump. Currently the sequence number generated during the dump is INCORRECT! We would need to remember about it during the restore in the next step
• option -o points to the dump file that should be created by sybdumptran. Because the procedure was started on root account here, we need to change access permissions to sap user to allow backup server to read its content

It is time to test whether the backup indeed contains the last transaction created before the server was stopped.

USE master
GO

DISK INIT NAME ='MyDb1DataDev', 
     PHYSNAME = '/opt/sap/databases/MyDb1DataDev.dat', 
     SIZE=10240
GO

DISK INIT NAME ='MyDb1LogDev', 
     PHYSNAME = '/opt/sap/databases/MyDb1LogDev.dat', 
     SIZE=2560
GO

CREATE DATABASE MyDb1 ON MyDb1DataDev = '20M' LOG ON MyDb1LogDev = '5M' FOR LOAD
GO

LOAD DATABASE MyDb1 FROM '/home/dumps/MyDb_full.dmp'
GO

LOAD TRAN MyDb1 FROM  '/home/dumps/MyDb_log_1.dmp'
GO

LOAD TRAN MyDb1 FROM  '/home/dumps/MyDb_emergency_log.dmp' with override = sequence
GO

ONLINE DATABASE MyDb1
GO

SELECT * FROM MyDb1.dbo.TEST
GO

What happens here is:

• new devices have been created (we don't want to overwrite the current database)
• new database has been created on the new devices
• this database was not initialized (it is created for load)
• full dump was loaded
• the first transaction dump was loaded
• the second (emergency) dump was loaded
• the database has been onlined
• and finally the results have been displayed
• THIS WORKS!

But please note the last load operation. You can find there the WITH OVERRIDE=SEQUENCE option. If the command would be started without it, than following error would be raised:

Specified file 'dump device' is out of sequence. current time stamp is ... while dump was from ...

Well, as mentioned earlier, when the emergency backup was created we pointed to the full backup as a metadata file. So this transaction dump was not aware of the transaction dumps done in the meantime. The sequence number was guessed and this was done bad, what caused the error. You can take the responsibility of watching the correct sequence using the override option. In this case be careful and point to the correct dump files in the correct sequence!

As you see, the sybdumptran utility can be really helpful in the case of catastrophic sybase failure.