Restore database form a damaged media

In the following example you will see how to recover from a damaged media failure. Let's assume, we have a database named A1 with one database file and one log file. The files are located on different devices (in our case on two separate disk files).

USE A1
GO

SELECT * FROM TestTran
GO

DUMP DATABASE A1 TO '/home/dumps/A1_Full.dmp'
GO

INSERT INTO TestTran (SomeText) VALUES ('The transaction that was not backed up')
GO

SHUTDOWN

The table TestTran contained already 3 rows. Here additional new row has been added, but it was never dumped neither to a full database dump, nor to log dump. After that server was stopped. This will allow us to perform some destructive activities on the database file, however in normal situation this could be a disk failure.

Now in operationg system let's rename the data file for the database. Finally the files of the database devices are like here:

[root@SAPSybase]# ll /opt/sap/databases/a1
total 67584
-rw-r-----. 1 sap sap 62914560 Apr 23 16:49 a1.old.mdf
-rw-r-----. 1 sap sap  6291456 Apr 23 16:48 a1.trn

After this damage Sybase can be started again:

cd /opt/sap/ASE-16_0/install
../bin/startserver -f ./RUN_SAP01

Already during the startup, and also in the error log following error will be displayed:


00:0002:00000:00002:2017/04/23 16:51:37.70 server  Activating disk 'a1_data' of size 61440 KB.
00:0002:00000:00002:2017/04/23 16:51:37.70 kernel  dopen: open '/opt/sap/databases/a1/a1.mdf', No such file or directory
00:0002:00000:00002:2017/04/23 16:51:37.70 kernel  udactivate: error starting virtual disk -1
00:0002:00000:00002:2017/04/23 16:51:37.70 server  Activating disk 'a1_log' of size 6144 KB.
00:0002:00000:00002:2017/04/23 16:51:37.70 kernel  Initializing virtual device 10, '/opt/sap/databases/a1/a1.trn' with dsync 'off'.
00:0002:00000:00002:2017/04/23 16:51:37.70 kernel  Virtual device 10 started using asynchronous (with DIRECTIO) i/o.

This means that we probably have problem... Indeed. When trying to execute following command, an error will be displayed again:

USE A1

Could not execute statement.

Device 'a1_data' (with physical name '/opt/sap/databases/a1/a1.mdf', and
virtual device number 9) has not been correctly activated at startup
time.  Please contact a user with System Administrator (SA) role.
SQLCODE=840
Server=SAP01, Severity Level=17, State=2, Transaction State=1, Line=1
Line 1

What to do? Well, we know what has happend. The database file is lost. And the problems is also, that not all transactions have been dumped! If we would restore just the full dump, than the recently added row to the TestTran table would be lost. Unfortunately following commands will fail

--this will fail
SELECT * FROM TestTran
GO
--this will fail
DUMP DATABASE A1 TO '/home/dumps/A1_Full_2.dmp'
GO
--this will fail
DUMP TRAN A1 TO '/home/dumps/A1_Tran1.dmp'
GO

In such a case following sequence of operations should be executed:

--this should succeed, however error message was displayed
DUMP TRAN A1 TO '/home/dumps/A1_Tran1.dmp' WITH no_truncate
GO
--try to restore the database to last good state
USE master
GO
disk init name='b1_data', physname ='/opt/sap/databases/b1/b1.mdf', size='60M'
GO
disk init name='b1_log',  physname ='/opt/sap/databases/b1/b1.trn', size='6M'
GO

CREATE DATABASE B1 ON b1_data = '60M' LOG ON b1_log = '6M' FOR LOAD
GO
LOAD DATABASE B1 FROM '/home/dumps/A1_Full.dmp'
GO
LOAD TRAN B1 FROM '/home/dumps/A1_Tran1.dmp'
GO
ONLINE DATABASE B1
GO
SELECT * FROM B1.dbo.TestTran --Table is empty
GO

What is going on?

1. First we try to execute the DUMP TRAN command. Previously we have seen that this command may fail, why should it succeed now? Because additional pareameter has been used called no_truncate. This parameter means, that no action should be performed on the dumped database - no checkpoint to probably damaged data file, no truncate on the log. We just try to read the data that is still in the log, but we want not to touch the database if it is not necessary! This time the command should succeed.
2. Next we try to create a new database, where the old one could be restored. It is always a good idea to retain still the damaged database if disk capacity allows this. For this we create new devices and a new database B1 on it
3. Now we load the dumps. First of all the full backup, and next the recent log backup. Of course if you have more backups that should be loaded this should be done in correct sequence one by one. 
4. The very last step is to ONLINE the database and validate the content of the TestTran table: