Answer: Which log backups are required to restore a database?

The Quiz question from April 6 asked this:

---

Let's say you have a subfolder structure containing the following database backup files:

..\backup\generation10
       ddd12.db
       ddd12.log
..\backup\generation10\logs
       110405AA.log
       110405AB.log
       110405AC.log

The ddd12.db and ddd12.log backup files were created by a single full backup run of dbbackup:

"%SQLANY12%\bin32\dbbackup.exe"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  -x^
  backup\generation10

The yymmddXX.log files were created by three subsequent runs of an incremental log backup process:

"%SQLANY12%\bin32\dbbackup.exe"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  -n^
  -t^
  -x^
  backup\generation10\logs

Question: When it comes time to restore ddd12.db backup file and apply the subsequent *.log backup files, is it necessary to apply ddd12.log before applying the yymmddXX.log files?

Or can ddd12.log be skipped and just the yymmddXX.log files applied?

---

The Answer Is...

Maybe.

When the first run of dbbackup created backup copies of both the database file (..\backup\generation10\ddd12.db) and the transaction log (..\backup\generation10\ddd12.log), it copied the database file first and then the log. If there were any active processes making changes to the database while the first step was running (copy ddd12.db), those changes would have been written to the log backup but not to the database backup. The second step (copy ddd12.log) would then include those changes in the backup copy of the log. Since the -x option tells dbbackup to delete and restart the main transaction log, the only backup file where those changes are recorded is the ..\backup\generation10\ddd12.log, which means that file must be applied first in any subsequent restore process.

However, if no such updates were being performed while dbbackup was copying the database, the corresponding copy of the transaction log is not required in a subsequent restore.

Not required... but there's no harm in applying it.

If the ddd12.log file from the full backup is required, and you skip it, and apply the first incremental yymmddXX.log backup instead, you will get this kind of error:

04/16 07:26:03. Cannot open transaction log file -- Can't use log file "C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12b\backup\generation10\logs\110416AA.log" since it has been used more recently than the database file

So, the Real Answer is...

Yes, go ahead and apply the log file from the full backup as the first step in a restore process. When trying to recover a database, life is already exciting enough without having to deal with a "Can't use log file" message.

The code from Demonstrating Full and Incremental Backups can be modified to show what happens when the database is actively updated while dbbackup is running...

First, modify the script file 04s_script_to_initialize_data.sql as follows:

CREATE TABLE t1 (
   pkey   INTEGER NOT NULL PRIMARY KEY,
   data   INTEGER NOT NULL DEFAULT 0 );

INSERT t1 VALUES ( 1, 1 );

COMMIT;

-- Prove that applying the first log backup is necessary.

CREATE TABLE rapid (
   pkey   INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY );

CREATE EVENT rapid_insert
HANDLER BEGIN
   WHILE 1 = 1 LOOP
      INSERT rapid VALUES ( DEFAULT );
      COMMIT;
      WAITFOR DELAY '00:00:00.1';
   END LOOP;
END;

TRIGGER EVENT rapid_insert;

The TRIGGER EVENT statement starts an asynchronous process that inserts a row in the rapid table every 1/10th of a second. This process continues throughout the demonstration, through the backup processes, until the database is stopped before the restore process begins.

The second file that needs changing is 13_restore_database.bat:

ECHO ********************************************^
*********************************************>>backup\generation10\dbbackup_log.txt
ECHO Restore started >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

COPY backup\generation10\ddd12.db

REM Note: -o writes to the dbbackup diagnostic file in this example.

"%SQLANY12%\bin32\dbsrv12.exe"^
  -o backup\generation10\dbbackup_log.txt^
  -oe dbsrv12_log_fatal_ddd12.txt^
  -os 10M^
  ddd12.db^
  -ad backup\generation10

"%SQLANY12%\bin32\dbsrv12.exe"^
  -o backup\generation10\dbbackup_log.txt^
  -oe dbsrv12_log_fatal_ddd12.txt^
  -os 10M^
  ddd12.db^
  -ad backup\generation10\logs

REM ******************************************************************
REM Restore finished
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

PAUSE

Now there is a new dbsrv12 step which uses the -ad backup\generation10 option to apply the ddd12.log file created by the full backup process. This is followed by the existing dbsrv12 -ad backup\generation10\logs step which applies the incremental yymmddXX.log backup files.

Previously, that new step was unnecessary. Now, since a few changes to the rapid table are recorded only in the ddd12.log backup file, it's required.