Loading transaction dumps. Point in time restore

The load operation of log files is not so boring as the dump operation, because:

• during the load operation, the correct sequence of dump files needs to be loaded
• the databases to be loaded, needs to be created first, and it's size must be big enough to accommodate the backups that should be restored
• the restore can stop at some, chosen by you date and time
• the last command during the restore is to ONLINE the database

Let's take look at the following example:

USE ALFA
GO

CREATE TABLE TestTran
(
   id INT IDENTITY PRIMARY KEY,
   SomeText NVARCHAR(300),
   Date DATETIME DEFAULT GETDATE()
)
GO

DUMP DATABASE ALFA TO '/home/dumps/alfa_full.dmp' WITH INIT
GO

INSERT INTO TestTran (SomeText) VALUES ('This is the first transaction')
GO
DUMP TRAN ALFA TO '/home/dumps/alfa_log1.dmp'
GO
SELECT * FROM TestTran
GO

INSERT INTO TestTran (SomeText) VALUES ('This is the second transaction')
GO
--wait a moment, remember the current time (in between the inserts)
SELECT GETDATE()
GO
INSERT INTO TestTran (SomeText) VALUES ('This is the third transaction')
GO
DUMP TRAN ALFA to '/home/dumps/alfa_log2.dmp'
GO

SELECT * FROM TestTran
GO

Let's take look, what happened there:

• first we create a table
• next we dump the database, in the dump the empty table will be present
• next we insert a row and again perform a transaction log dump, so in that dump only that one record will be present
• next we insert a new row, wait a little and insert the third record. After that we will have three rows in the table and additionally the last two rows are present in the last backup. Note that we haven't any backup that was done in-between the two inserts. In this case the date received was: '2017-04-23 11:54:39.236', and the rows created are:

Now we will perform a couple of test restores. First of all we need to have a new database where the data will be restored (I don't want to overwrite the original database).

USE master
GO
disk init name='a1_data', physname ='/opt/sap/databases/a1/a1.mdf', size='60M'
GO
disk init name='a1_log',  physname ='/opt/sap/databases/a1/a1.trn', size='6M'
GO
CREATE DATABASE A1 ON a1_data = '60M' LOG ON a1_log = '6M' FOR LOAD
GO

As you see, we have added new devices and having them we can now restore the database created in the last step using the previously created backups.

When switching from one scenario to the other, we need to drop the A1 database, and finally after all the tests, we will probably would like to remove the newly created devices as well. This can be achieved using following commands:

DROP DATABASE A1
GO
sp_dropdevice a1_data
GO
sp_dropdevice a1_log
GO

Test 1 - only full backup will be restored

LOAD DATABASE A1 FROM '/home/dumps/alfa_full.dmp'
GO
ONLINE DATABASE A1
GO
SELECT * FROM A1.dbo.TestTran --Table is empty

What will be received? In the moment when the database had the first backup taken, the table was still empty. So now after the restore the table in the new database A1 is also empty.

Test 2 - full and the first log backup will be restored

LOAD DATABASE A1 FROM '/home/dumps/alfa_full.dmp'
GO
LOAD TRAN A1 FROM  '/home/dumps/alfa_log1.dmp'
GO
ONLINE DATABASE A1
GO
SELECT * FROM A1.dbo.TestTran --Table has only 1 record
GO

Note that after the full dump was restored the database was not onlined. This allowed to proceed with next tran backups being restored. After the first transaction log was restored, the database has been changed to ONLINE. What can be now found in the table? One row, as the row was added before the first transaction log backup was taken:

Test 3 - full and the booth transaction logs will be restored

LOAD DATABASE A1 FROM '/home/dumps/alfa_full.dmp'
GO
LOAD TRAN A1 FROM  '/home/dumps/alfa_log1.dmp'
GO
LOAD TRAN A1 FROM  '/home/dumps/alfa_log2.dmp'
GO
ONLINE DATABASE A1
GO
SELECT * FROM A1.dbo.TestTran --Table has 3 records
GO

The only difference in this example is that between the LOAD of the first transaction log dump and the online operations the second transaction log was loaded. As you see it is allowed to perform as many transaction loads as you wish (and have). The only conditions are that the first operation was a full dump load, the transaction dumps were restored in exactly the same sequence as they where taken and that the last operation was ONLINE DATABASE.

If you would mix the sequence of transaction log dumps than following error message will  be shown:

Could not execute statement.
Specified file 'dump device' is out of sequence. Current time stamp is
Apr 23 2017 10:08:51:570AM while dump was from Apr 23 2017
11:53:07:580AM.
SQLCODE=4305
Server=SAP01, Severity Level=16, State=1, Transaction State=0, Line=1
Line 1

If something like that happens, than - no worries, just look for the correct transaction log dump and continue with the restore.

However in our case the sequence should be correct. After the restore following rows will be present in the table:

Test 4 - full and the booth transaction logs will be restored, but the restore will be stopped in a specified time

LOAD DATABASE A1 FROM '/home/dumps/alfa_full.dmp' 
GO
LOAD TRAN A1 FROM  '/home/dumps/alfa_log1.dmp' 
GO
LOAD TRAN A1 FROM  '/home/dumps/alfa_log2.dmp' WITH until_time = '2017-04-23 11:54:39.236'
GO
ONLINE DATABASE A1
GO
SELECT * FROM A1.dbo.TestTran --Table has 2 records
GO

Now, in the last LOAD additional parameter has been added 'until_time'. This parameter causes the load operation to stop with the restore of transactions that finished after crossing the specified time. Also here a small problem may be met. If you add the until_time parameter when the log will be restored and that date will not be present in the dump, error message will be returned:

Could not execute statement.
The specified recovery date (Apr 23 2011 11:54:39:236AM) is not between
the start (Apr 23 2017 11:53:07:580AM) and end (Apr 23 2017
11:55:55:586AM) date.
SQLCODE=4325
Server=SAP01, Severity Level=16, State=1, Transaction State=0, Line=1
Line 2

However our parameters have been passed correctly, so currently in the table 2 records should be visible. This type of restore is also known under the name "point in time restore".