Synchronizing Updated Primary Key Values With MobiLink

Question: Can MobiLink handle changes to primary key values on the consolidated database?

Answer: I've been telling people "No!" for so many years that I've come to believe it myself:

"No! You can't change primary key values if you're synchronizing with MobiLink!"

Epiphany

A couple of days ago someone asked the question again, someone who really wanted the answer to be "Yes!", so much so that I started thinking about "Why not?" and "How?"... and that's when I had this epiphany:

The rule is actually this: You can't change primary key values on the remote database if you're synchronizing with MobiLink.

MobiLink is extremely flexible, and it has been used for many years to handle schema differences between consolidated and remote databases. So what if the table on the remote database had a different column as PRIMARY KEY, and the original PRIMARY KEY column from the consolidated was defined as a UNIQUE constraint on the remote?

It Works!

Yes, you can change primary key values on the consolidated database when you're using MobiLink. Here's how it works:

Let's start with a simple table on the consolidated, one primary key column, one data column, and the usual last_updated:

CREATE TABLE t (
   p1            VARCHAR ( 10 ) NOT NULL,
   data          INTEGER NOT NULL DEFAULT 0,
   last_updated  TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
   PRIMARY KEY ( p1 ) );

---

Step 1: Add a new column r1 to the table on the consolidated database, and make it UNIQUE. This column will not be used by any application code on the consolidated database, it's just there for MobiLink purposes.

CREATE TABLE t (
   p1            VARCHAR ( 10 ) NOT NULL,
   data          INTEGER NOT NULL DEFAULT 0,
   r1            BIGINT NOT NULL DEFAULT AUTOINCREMENT,
   last_updated  TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
   PRIMARY KEY ( p1 ),
   UNIQUE ( r1 ) );

---

Here's the corresponding table on the remote, before any changes; just the primary key and data columns, and as usual the last_updated column is omitted:

CREATE TABLE t (
   p1            VARCHAR ( 10 ) NOT NULL,
   data          INTEGER NOT NULL DEFAULT 0,
   PRIMARY KEY ( p1 ) );

---

Step 2: Add the new column r1 to the table on the remote database. Give r1 a GLOBAL AUTOINCREMENT default, make r1 the PRIMARY KEY, and change the original primary key column p1 into a UNIQUE constraint.

CREATE TABLE t (
   p1            VARCHAR ( 10 ) NOT NULL,
   data          INTEGER NOT NULL DEFAULT 0,
   r1            BIGINT NOT NULL 
                    DEFAULT GLOBAL AUTOINCREMENT ( 1000000000 ),
   PRIMARY KEY ( r1 ),
   UNIQUE ( p1 ) );

At this point, we are free to download primary key changes (p1) from the consolidated database to the remote database, but not changes to r1. Here are some assumptions:

• values inserted into t.r1 are globally unique across all databases, both consolidated and remote,

• once a row is inserted in t the value in t.r1 is never changed by any process anywhere,

• except for inserts and MobiLink scripts the column t.r1 is ignored by all processes running on the consolidated database, and

• changes to t.p1 are initiated by application code on the consolidated database only, and are downloaded to the remote.

The fourth assumption (t.p1 is only modified on the consolidated database) may or may not be a requirement, but this article doesn't answer that question one way or another.

---

Step 3: Code the MobiLink scripts to deal with the schema differences:

CALL ml_add_table_script  ( 'v1', 't', 'upload_insert', '
INSERT t
       ( p1,
         data,
         r1 )
VALUES ( {ml r.p1},
         {ml r.data},
         {ml r.r1} )' );

CALL ml_add_table_script  ( 'v1', 't', 'upload_update', '
UPDATE t
   SET data = {ml r.data}
 WHERE r1 = {ml r.r1}' );

CALL ml_add_table_script  ( 'v1', 't', 'upload_delete', '
DELETE t
 WHERE r1 = {ml r.r1}' );

CALL ml_add_table_script  ( 'v1', 't', 'download_cursor', '
SELECT p1,
       data,
       r1
  FROM t
 WHERE last_updated >= {ml s.last_table_download}' );

The upload_insert and download_cursor scripts look no different than they would if p1 was still the primary key on the remote database.

The upload_update script ignores the t.p1 column because it is never changed on the remote. The WHERE clause uses t.r1 to locate the row to be updated, and that's OK because

• t.r1 is UNIQUE on the consolidated and

• t.r1 is never modified on the consolidated.

The upload_delete also uses t.r1 in the WHERE clause, for the same reasons as the upload_update script.

---

Here's a demonstration; first, one row is inserted on the consolidated database, and dbmlsync is run to download that row to the remote:

INSERT t VALUES ( 'a', 0, DEFAULT, DEFAULT );
COMMIT;

Here's the step-by-step with expected output:

--------------------------------------------------------------------------- 
-- 1. Run this on the consolidated and remote database to display the data.

SELECT * FROM t;

/* Expected output from consolidated and remote...

p1,data,r1,last_updated
'a',0,1,'2011-05-04 11:39:55.523'

p1,data,r1
'a',0,1

*/

--------------------------------------------------------------------------- 
-- 2. Change the primary key on the consolidated database.

UPDATE t SET p1 = 'xxx' WHERE p1 = 'a';

COMMIT;

--------------------------------------------------------------------------- 
-- 3. Run a sync to download the new primary key.

--------------------------------------------------------------------------- 
-- 4. Display the new primary key on the remote database.

SELECT * FROM t;

/* Expected output from remote...

p1,data,r1
'xxx',0,1

*/

--------------------------------------------------------------------------- 
-- 5. Use the new primary key to modify the data on the remote database.

UPDATE t SET data = 1 WHERE p1 = 'xxx';

COMMIT;

--------------------------------------------------------------------------- 
-- 6. Run a sync to upload the modified data.

--------------------------------------------------------------------------- 
-- 7. Display the modified data on the consolidated database.

SELECT * FROM t;

/* Expected output from consolidated..

p1,data,r1
'xxx',1,1

*/

Latest SQL Anywhere EBFs: 11.0.1.2596 and 10.0.1.4213

11.0.1.2596 - Windows 32/64 - April 28, 2001

10.0.1.4213 - Windows 32/64 - April 29, 2001

10.0.1.4213 - Linux x86/Linux x64 - April 28, 2001

---

Misunderstanding SQL Anywhere

Here are five of my favorite misunderstandings about SQL Anywhere...

---

1. Index scans are always faster than table scans.

Not true... index scans are often faster, but not "always", not even "usually". Here's what the Help says about table scans:

"Sequential table scans are used when it is likely that most of the table pages have a row that match the query's search condition or a suitable index is not defined. Although sequential table scans may read more pages than index scans, the disk I/O can be substantially less expensive because the pages are read in contiguous blocks from the disk (this performance improvement is best if the database file is not fragmented on the disk). Sequential I/O reduces disk head movement and rotational latency. For large tables, sequential table scans also read groups of several pages at a time. This further reduces the cost of sequential table scans relative to index scans."

Table access is not limited to "index scans versus table scans"; there are actually 8 different table access methods.

---

2. Performance is affected by your SQL coding style.

In a complex query there is an almost infinite variety of ways you can code the SQL to obtain exactly the same result set and exactly the same execution plan. Unlike most programming languages SQL syntax is very loosely coupled to the underlying semantics, making SQL clauses more like spreadsheet cells where the order and arrangement of the cells make little difference to the underlying calculations.

For an introduction to how SQL Anywhere transforms your query, see Query processing phases, in particular the 12 different semantic query transformations described here.

---

3. SQL Anywhere writes your data to the database when it does a commit.

A commit guarantees your changes are permanent, but it doesn't guarantee they've been written to the physical database file. In fact, no high-performance database management system makes that guarantee because for a busy database with many connections executing many commits it would crush performance by beating the disk drives to death.

Here are some snippets from Chapter 9: Protecting in SQL Anywhere Studio 9 Developer's Guide:

During execution, database data may be stored in seven different locations:

• Cache in memory

• Database files on disk, including other dbspace file

• Transaction log file on disk

• Optional mirror log file on disk

• Checkpoint log in the database file

• Rollback log in the cache and database file

• Temporary file on disk

The cache contains table and index pages that have been read from the database files into memory. The cache is always up to date; to be specific, logical database changes made by SQL statements are immediately reflected in the table and index pages in the cache, including both committed and uncommitted changes.

...there is no direct correspondence between COMMIT operations and data in the physical database files; uncommitted changes may be written to the database files while committed changes may not be written immediately.

The database files are up to date after a checkpoint; to be specific, all table and index pages in the physical database files are guaranteed to be up to date with respect to data in the cache whenever a checkpoint is complete.

The transaction log file, also known as the redo log, contains a sequential record of logical changes made to the database since this log file was created. Both committed and uncommitted changes may be written to the physical transaction log file, and they may be written before a COMMIT is executed on the corresponding connection. COMMIT and CHECKPOINT operations are recorded in the transaction log as well as other changes.

The transaction log file is up to date after a COMMIT; to be specific, the changes made by a particular transaction are guaranteed to be written to the physical transaction log by the time control is returned to the connection after a COMMIT ending that transaction is successfully completed. In fact, all changes, committed or otherwise, are guaranteed to be written to the transaction log whenever a COMMIT is performed on any connection.

---

4. The transaction log is used for rollbacks.

Nope... the rollback log is used for rollbacks, the transaction log is used for forward recovery.

Here's another excerpt from SQL Anywhere Studio 9 Developer's Guide:

A rollback log, also known as an undo log or row level undo log, is maintained for each connection to hold a sequential record of the reverse operations that would undo the logical changes made to the database by this connection since the last COMMIT or ROLLBACK was executed on the connection.

The rollback log pages are stored in the SYSTEM dbspace database file, together with table and data pages, and they are written to the physical database file before and during a checkpoint. They are not necessarily written to the physical database file when the logical database changes are first made by SQL statements. Note that rollback log pages are included in the checkpoint log.

The rollback log is up to date after a checkpoint; to be specific, all current rollback log pages in the physical database file are guaranteed to be up to date whenever a checkpoint is completed.

The data in a rollback log may include changes made before and after a checkpoint; there is no correspondence between a checkpoint and the end of any particular transaction.

The data in a rollback log is used during normal operation to roll back the changes when a ROLLBACK is executed on the connection, and it is then discarded. It is also discarded when a COMMIT is executed on the connection.

---

5. SQL Anywhere saves the execution plan when it compiles your SQL query, and uses the same plan every time it executes the query.

Some other database products save execution plans in the database, but not SQL Anywhere, not ever. What SQL Anywhere does do, sometimes, is temporarily save or "cache" the plan at runtime.

Here's what the Help says about the subject:

Normally, the optimizer selects an execution plan for a query every time the query is executed. Optimizing at execution time allows the optimizer to choose a plan based on current system state, and the values of current selectivity estimates and estimates based on the values of host variables. For queries that are executed frequently, the cost of query optimization can outweigh the benefits of optimizing at execution time. To reduce the cost of optimizing these statements repeatedly, the SQL Anywhere server considers caching plans... [more]

---

The fRiDaY File - Empowerment

---

Latest SQL Anywhere EBFs: 12.0.1.3324, 11.0.1.2587, ...

12.0.1.3324 - Windows 32/64 - April 21, 2011

11.0.1.2587 - Windows 32/64 - April 21, 2011

12.0.0.2670 - Mac OS - April 21, 2011

11.0.1.2588 - Sun Solaris SPARC - April 19, 2011

... and a warning about Foxhound

11.0.1.2587 is OK for use with Foxhound 1.1. However, the previous EBF is not OK; if you run Foxhhound 1.1 on SQL Anywhere 11.0.1.2558, the database engine may crash when you try to open a second Foxhound menu in the browser. If you experience this symptom, either stick to using one Foxhound session, or apply the SQL Anywhere 11.0.1.2587 EBF.

---

Techwave Agenda

The Techwave Preliminary Agenda doesn't show much detail yet, but it does reveal a few significant differences from recent years:

• First, the one-and-only keynote has "SAP Executive" in the title, no mention of Sybase.

• Second... did you miss it? There's only one keynote. But it's a doozy, all morning long. [yawn]

• Third, the Plenary session(s) are crammed into a single afternoon, along with Technical Breakout sessions.

• Finally, all remaining time slots (2.5 of the 3.5 days in total) are devoted to Technical Breakout sessions.

Combine that with the return of the TechWave Call for Papers and it looks like Techwave 2011 will have more technical content than recent years.

---

Demonstrating Backup, Validation and Restore

This article updates the backup and recovery demonstration first shown in Recovering Across Multiple Backup Generations to include the database validation steps described in Validating Backups; it also includes the rapid-fire INSERT statements shown in Answer: Which log backups are required to restore a database?

---

The following demonstration shows how you can create, validate and preserve multiple generations of full online database and incremental online log backup files.

It then shows how you can go all the way back to the beginning and

• restore the oldest backup (in this case generation 1 of 10), then

• apply all the subsequent log files without losing any data.

These Help topics apply:

• dbbackup, in particular the -n, -t and -x options,

• dbvalid, and

• dbsrv12 -ad to apply all the transaction logs in a single folder and stop.

Command file 01_run_everything.bat automates the whole demonstration by calling the other command files in the right order:

• Create the database and a table and event to test success,

• make 10 generations of backup files,
  • each consisting of a full backup

  • followed by three consecutive incremental log backups

  • with new rows being inserted in a rapid fire fashion before, during and after every backup, then

• delete the current database,

• restore the full database backup file from generation 1,

• apply all the transaction log backup files

• plus the current log file,

• start the database and

• show how all the inserted rows have been restored.

CALL 02_run_dbinit12.bat
CALL 03_run_dbspawn_dbsrv12.bat
CALL 04_run_script_to_create_table_and_event.bat

REM Generation 1...
CALL 05_run_dbbackup12_full.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat

CALL 06_display_table.bat

REM Generation 2...
CALL 05_run_dbbackup12_full.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat

REM Generation 3...
CALL 05_run_dbbackup12_full.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat

REM Generation 4...
CALL 05_run_dbbackup12_full.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat

REM Generation 5...
CALL 05_run_dbbackup12_full.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat

REM Generation 6...
CALL 05_run_dbbackup12_full.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat

REM Generation 7...
CALL 05_run_dbbackup12_full.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat

REM Generation 8...
CALL 05_run_dbbackup12_full.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat

REM Generation 9...
CALL 05_run_dbbackup12_full.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat

REM Generation 10...
CALL 05_run_dbbackup12_full.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 07_run_dbbackup12_incremental.bat

REM Drop and restore...
CALL 08_stop_drop_database.bat
CALL 09_restore_database.bat
CALL 03_run_dbspawn_dbsrv12.bat

CALL 06_display_table.bat

PAUSE All done...

Backup

02_run_dbinit12.bat starts the demonstration by creating an empty SQL Anywhere 12 database:

"%SQLANY12%\bin32\dbinit.exe"^
  ddd12.db

03_run_dbspawn_dbsrv12.bat starts that database:

"%SQLANY12%\bin32\dbspawn.exe"^
  -f "%SQLANY12%\bin32\dbsrv12.exe"^
  -o dbsrv12_log_ddd12.txt^
  -oe dbsrv12_log_fatal_ddd12.txt^
  -os 10M^
  -x tcpip^
  -zl^
  -zp^
  -zt^
  ddd12.db 

04_run_script_to_create_table_and_event.bat runs a script to start the rapid-fire INSERT process:

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  READ ENCODING Cp1252 04s_script_to_create_table_and_event.sql

04s_script_to_create_table_and_event.sql contains the script:

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;

05_run_dbbackup12_full.bat creates a full backup of the database and transaction log:

REM ******************************************************************
REM Create empty backup\generation_temp subfolder

MD backup
CD backup
RD /S /Q generation_temp
MD generation_temp
CD ..

ECHO ********************************************^
*********************************************>>backup\generation_temp\dbbackup_log.txt
ECHO ***** Full dbbackup started >>backup\generation_temp\dbbackup_log.txt
SET ERRMSG=
DATE /T >>backup\generation_temp\dbbackup_log.txt
TIME /T >>backup\generation_temp\dbbackup_log.txt

REM ******************************************************************
ECHO ***** Step 1 Full backup >>backup\generation_temp\dbbackup_log.txt

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

IF ERRORLEVEL 1 SET ERRMSG=Step 1 Full backup failed with ERRORLEVEL = %ERRORLEVEL%
IF NOT "%ERRMSG%z" == "z" GOTO backup_failed

REM ******************************************************************
ECHO ***** Step 2 Apply log >>backup\generation_temp\dbbackup_log.txt

CD backup\generation_temp
MD validate
XCOPY ddd12.db validate /V /Q /K 
CD ..\..

REM Note: The -ad folder is relative to the folder containing the database.

"%SQLANY12%\bin32\dbsrv12.exe"^
  -o backup\generation_temp\dbbackup_log.txt^
  backup\generation_temp\validate\ddd12.db^
  -ad "%CD%\backup\generation_temp"

IF ERRORLEVEL 1 SET ERRMSG=Step 2 Apply log failed with ERRORLEVEL = %ERRORLEVEL%
IF NOT "%ERRMSG%z" == "z" GOTO backup_failed

REM ******************************************************************
ECHO ***** Step 3 Start read-only copy >>backup\generation_temp\dbbackup_log.txt

"%SQLANY12%\bin32\dbspawn.exe"^
  -f "%SQLANY12%\bin32\dbsrv12.exe"^
  -n readonlycopy^
  -o backup\dbsrv12_readonlycopy_log.txt^
  -r^
  backup\generation_temp\validate\ddd12.db 

IF ERRORLEVEL 1 SET ERRMSG=Step 3 Start read-only copy failed with ERRORLEVEL = %ERRORLEVEL%
IF NOT "%ERRMSG%z" == "z" GOTO backup_failed

REM ******************************************************************
ECHO ***** Step 4 Validate >>backup\generation_temp\dbbackup_log.txt

"%SQLANY12%\bin32\dbvalid.exe"^
  -c "ENG=readonlycopy;DBN=ddd12;UID=dba;PWD=sql"^
  -o backup\generation_temp\dbbackup_log.txt^
  -q

IF ERRORLEVEL 1 SET ERRMSG=Step 4 Validate failed with ERRORLEVEL = %ERRORLEVEL%

REM ******************************************************************
ECHO ***** Step 5 Stop read-only copy >>backup\generation_temp\dbbackup_log.txt

"%SQLANY12%\bin32\dbstop.exe"^
  -c "ENG=readonlycopy;DBN=ddd12;UID=dba;PWD=sql"^
  -y
IF NOT "%ERRMSG%z" == "z" GOTO backup_failed

REM ******************************************************************
ECHO ***** Step 6 Move files >>backup\generation_temp\dbbackup_log.txt

CD backup
RD /S /Q generation1
RENAME generation2 generation1
RENAME generation3 generation2
RENAME generation4 generation3
RENAME generation5 generation4
RENAME generation6 generation5
RENAME generation7 generation6
RENAME generation8 generation7
RENAME generation9 generation8
RENAME generation10 generation9
RENAME generation_temp generation10
CD ..

REM ******************************************************************
REM Backup OK

DIR /S backup\generation10\*.* >>backup\generation10\dbbackup_log.txt
ECHO ***** Full dbbackup OK >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt
GOTO end

:backup_failed
REM ******************************************************************
REM Backup failed

ECHO ***** Error: Full dbbackup failed... >>backup\generation_temp\dbbackup_log.txt
ECHO ***** %ERRMSG% >>backup\generation_temp\dbbackup_log.txt
DATE /T >>backup\generation_temp\dbbackup_log.txt
TIME /T >>backup\generation_temp\dbbackup_log.txt
GOTO end

:end

Line 4 of the full backup command file 05_run_dbbackup12_full.bat makes sure that the backup subfolder exists. Lines 5 and 6 delete the subfolder backup\generation_temp if it exists, and line 7 creates it again; this is where the new backup files will reside until the backup process has finished successfully.

The ECHO command on lines 10 and 11 writes a separator line to diagnostic log file dbbackup_log.txt. A new copy of dbbackup_log.txt is started for each full backup, and more records are added by this command file and by the later incremental log backups.

Lines 20 through 24 run dbbackup.exe to create a backup copy of the database and log files in the backup\generation_temp folder. The -x option tells dbbackup to delete and restart the primary transaction log file after the backup log file has been written. The -y option is not used because this command file doesn't overwrite any files.

The IF commands on lines 26 and 27 put an error message in an environment variable and skip to the end of the command file if dbbackup.exe failed.

The commands on line 32 through 35 make a second copy of the database backup file in a subfolder, and the dbsrv12 -ad command on lines 39 to 42 apply the transaction log backup file to that second copy of the database.

Tip: Use the %CD% current directory environment variable to construct an absolute path specification for the -ad option. Unlike other path and file specifications in SQL Anywhere, the dbsrv12 -ad option is relative to the folder containing the database file, rather than relative to the current folder or the folder where the dbsrv12 was started. If you make a mistake, you will see a less-than-helpful "unable to start database" message.

The dbsrv12 command on lines 50 through 55 starts the second copy of the database backup file in read-only mode so it can be validated by the command on lines 63 to 66.

The commands on lines 73 through 76 stop the read-only database and then check whether the previous database validation step was successful.

The commands on lines 81 through 93 preserve up to 10 most recent full backups:

• The oldest subfolder (generation1) is deleted if it exists,

• the 9 newer subfolders generation2 to 10 are given earlier names generation 1 through 9, if they exist, and

• the newest subfolder generation_temp is renamed generation10.

The commands on lines 95 through 102 record a successful backup in the diagnostic log file, and the commands on lines 104 through 112 record a failure.

06_display_table.bat displays the current state of the rapid-fire INSERT process:

"%SQLANY12%\bin32\dbisql.com"^
  -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
  SELECT COUNT(*), MAX ( pkey ) FROM rapid

PAUSE Note: COUNT should equal MAX

The output from that script shows that there are no gaps because of missing rows:

COUNT()     MAX(rapid.pkey)
---------------------------
209         209

(1 rows)

Execution time: 0.106 seconds

C:\...>PAUSE Note: COUNT should equal MAX
Press any key to continue . . .

07_run_dbbackup12_incremental.bat is called to create each incremental log backup:

ECHO ********************************************^
*********************************************>>backup\generation10\dbbackup_log.txt
ECHO ***** Incremental dbbackup started >>backup\generation10\dbbackup_log.txt
SET ERRMSG=
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

REM ******************************************************************
ECHO ***** Step 1 Check full backup >>backup\generation10\dbbackup_log.txt

IF EXIST "backup\generation10\ddd12.db" ( GOTO check_full_log_backup ) ELSE ( GOTO full_backup_is_missing )

:check_full_log_backup
IF EXIST "backup\generation10\ddd12.log" ( GOTO full_backup_exists ) ELSE ( GOTO full_backup_is_missing )

:full_backup_is_missing
SET ERRMSG=Step 1 Check full backup failed
GOTO backup_failed

:full_backup_exists
REM ******************************************************************
ECHO ***** Step 2 Incremental backup >>backup\generation10\dbbackup_log.txt

CD backup\generation10
MD logs
CD ..\..

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

IF ERRORLEVEL 1 SET ERRMSG=Step 2 Incremental backup failed with ERRORLEVEL = %ERRORLEVEL%
IF NOT "%ERRMSG%z" == "z" GOTO backup_failed

REM ******************************************************************
ECHO ***** Step 3 Apply log >>backup\generation10\dbbackup_log.txt

REM Note: The -ad folder is relative to the folder containing the database.

"%SQLANY12%\bin32\dbsrv12.exe"^
  -o backup\generation10\dbbackup_log.txt^
  backup\generation10\validate\ddd12.db^
  -ad "%CD%\backup\generation10\logs"

IF ERRORLEVEL 1 SET ERRMSG=Step 3 Apply logs failed with ERRORLEVEL = %ERRORLEVEL%
IF NOT "%ERRMSG%z" == "z" GOTO backup_failed

REM ******************************************************************
REM Backup OK

DIR /S backup\generation10\*.* >>backup\generation10\dbbackup_log.txt
ECHO ***** Incremental dbbackup OK >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt
GOTO end

:backup_failed
REM ******************************************************************
REM Backup failed

ECHO ***** Error: Incremental dbbackup failed >>backup\generation10\dbbackup_log.txt
ECHO ***** %ERRMSG% >>backup\generation10\dbbackup_log.txt
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt
GOTO end

:end

Lines 1 through 6 of the incremental log backup command file 07_run_dbbackup12_incremental.bat assume that a full backup already exists in the backup\generation10 subfolder, so it's OK to continue writing information to the diagnostic log file dbbackup_log.txt.

Lines 11 through 18 put that assumption to the test by ensuring the backup file copies of the database and transaction log exist in the backup\generation10 subfolder; if they don't exist, there's really no point creating a further incremental log backup.

Lines 24 through 26 make sure that the backup\generation10\logs subfolder exists. It might exist already, and that's ok; unlike the full backup command file, this command file always writes the new incremental log backup file to the same subfolder rather than creating a new subfolder.

Lines 28 through 34 run dbbackup.exe to create an incremental backup copy of the transaction log file in the backup\generation_temp\logs folder. The

• -n option tells dbbackup to rename the backup copy of the transaction log file from ddd12.log to yymmddxx.log, where yymmdd is the date and xx is AA, AB, AC, ...,

• the -t option to backup the transaction log file, not the database file, and

• the -x option tells dbbackup to delete and restart the primary transaction log file after the backup log file has been written.

• The -y option is not used because this command file doesn't overwrite any files.

The IF commands on lines 36 and 37 skip to the end of the command file if dbbackup.exe failed.

The dbsrv12 -ad command on lines 44 through 47 check the validity of the new transaction log backup by applying it to the second copy of the database backup file what was created in the previous run of 05_run_dbbackup12_full.bat.

Tip: Use the %CD% current directory environment variable to construct an absolute path specification for the -ad option. Unlike other path and file specifications in SQL Anywhere, the dbsrv12 -ad option is relative to the folder containing the database file, rather than relative to the current folder or the folder where the dbsrv12 was started. If you make a mistake, you will see a less-than-helpful "unable to start database" message.

The commands on lines 52 through 63 record a successful backup in the diagnostic log file, and the commands on lines 61 through 69 record a failure.

Restore

08_stop_drop_database.bat makes way for the restore process by stopping and deleting the current database files:

"%SQLANY12%\bin32\dbstop.exe" -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql" -y

PAUSE Wait until the database is stopped, then

ERASE /F ddd12.db

REM Note: The transaction log is not deleted.

09_restore_database.bat uses the COPY command to restore the ancient "generation 1" database backup, and then it runs dbsrv12.exe twenty-one times to apply all 40 of the transaction log backup files plus the current log:

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\generation1\ddd12.db

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

REM Note: -ad is relative to the folder containing the database.

"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation1
"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation1\logs

"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation2
"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation2\logs

"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation3
"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation3\logs

"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation4
"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation4\logs

"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation5
"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation5\logs

"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation6
"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation6\logs

"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation7
"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation7\logs

"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation8
"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation8\logs

"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation9
"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation9\logs

"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation10
"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -ad backup\generation10\logs

REM Use dbsrv12 -a to apply the most recent log.

"%SQLANY12%\bin32\dbsrv12.exe" -o backup\generation10\dbbackup_log.txt ddd12.db -a ddd12.log

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

Here are some snippets from the diagnostic log messages produced by the 21 dbsrv12 -ad and dbsrv12 -a runs:

*****************************************************************************************
Restore started 
Sun 04/24/2011 
12:24 PM
I. 04/24 12:24:25. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:25. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:25. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:25. Database recovery in progress
I. 04/24 12:24:25.     Last checkpoint at Sun Apr 24 2011 12:15
I. 04/24 12:24:25.     Checkpoint log...
I. 04/24 12:24:25.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation1\ddd12.log...
I. 04/24 12:24:25.     Checkpointing...
I. 04/24 12:24:25. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:25. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:25. Recovery complete
I. 04/24 12:24:25. Database server shutdown automatically after log applied
I. 04/24 12:24:25. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:26. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:26. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:26. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:26. Database recovery in progress
I. 04/24 12:24:26.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:26.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation1\logs\110424AA.log...
I. 04/24 12:24:26. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:26. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:26. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:27. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:27.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation1\logs\110424AB.log...
I. 04/24 12:24:27. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:27. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:27. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:27. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:28.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation1\logs\110424AC.log...
I. 04/24 12:24:28. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:28. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:28.     Checkpointing...
I. 04/24 12:24:28. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:28. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:28. Recovery complete
I. 04/24 12:24:28. Database server shutdown automatically after log applied
I. 04/24 12:24:28. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:28. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:28. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:29. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:29. Database recovery in progress
I. 04/24 12:24:29.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:29.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation2\ddd12.log...
I. 04/24 12:24:29. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:30. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:30.     Checkpointing...
I. 04/24 12:24:30. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:30. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:30. Recovery complete
I. 04/24 12:24:30. Database server shutdown automatically after log applied
I. 04/24 12:24:30. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:30. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:30. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:30. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:30. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:31. Database recovery in progress
I. 04/24 12:24:31.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:31.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation2\logs\110424AA.log...
I. 04/24 12:24:31. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:31. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:31. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:31. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:31.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation2\logs\110424AB.log...
I. 04/24 12:24:31. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:32. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:32. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:32. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:32.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation2\logs\110424AC.log...
I. 04/24 12:24:32. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:32. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:32.     Checkpointing...
I. 04/24 12:24:32. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:33. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:33. Recovery complete
I. 04/24 12:24:33. Database server shutdown automatically after log applied
I. 04/24 12:24:33. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:33. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:33. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:33. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:33. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:33. Database recovery in progress
I. 04/24 12:24:33.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:33.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation3\ddd12.log...
I. 04/24 12:24:33. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:34. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:34.     Checkpointing...
I. 04/24 12:24:34. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:34. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:34. Recovery complete
I. 04/24 12:24:34. Database server shutdown automatically after log applied
I. 04/24 12:24:34. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:34. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:34. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:34. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:34. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:35. Database recovery in progress
I. 04/24 12:24:35.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:35.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation3\logs\110424AA.log...
I. 04/24 12:24:35. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:35. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:35. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:35. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:35.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation3\logs\110424AB.log...
I. 04/24 12:24:36. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:36. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:36. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:36. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:36.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation3\logs\110424AC.log...
I. 04/24 12:24:36. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:36. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:36.     Checkpointing...
I. 04/24 12:24:37. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:37. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:37. Recovery complete
I. 04/24 12:24:37. Database server shutdown automatically after log applied
I. 04/24 12:24:37. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:37. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:37. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:37. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:37. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:38. Database recovery in progress
I. 04/24 12:24:38.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:38.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation4\ddd12.log...
I. 04/24 12:24:38. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:38. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:38.     Checkpointing...
I. 04/24 12:24:38. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:38. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:38. Recovery complete
I. 04/24 12:24:38. Database server shutdown automatically after log applied
I. 04/24 12:24:38. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:39. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:39. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:39. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:39. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:39. Database recovery in progress
I. 04/24 12:24:39.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:39.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation4\logs\110424AA.log...
I. 04/24 12:24:39. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:39. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:39. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:40. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:40.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation4\logs\110424AB.log...
I. 04/24 12:24:40. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:40. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:40. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:40. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:41.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation4\logs\110424AC.log...
I. 04/24 12:24:41. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:41. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:41.     Checkpointing...
I. 04/24 12:24:41. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:41. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:41. Recovery complete
I. 04/24 12:24:41. Database server shutdown automatically after log applied
I. 04/24 12:24:41. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:42. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:42. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:42. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:42. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:42. Database recovery in progress
I. 04/24 12:24:42.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:42.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation5\ddd12.log...
I. 04/24 12:24:42. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:42. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:42.     Checkpointing...
I. 04/24 12:24:42. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:43. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:43. Recovery complete
I. 04/24 12:24:43. Database server shutdown automatically after log applied
I. 04/24 12:24:43. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:43. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:43. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:43. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:43. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:43. Database recovery in progress
I. 04/24 12:24:43.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:43.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation5\logs\110424AA.log...
I. 04/24 12:24:43. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:44. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:44. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:44. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:44.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation5\logs\110424AB.log...
I. 04/24 12:24:44. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:44. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:44. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:45. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:45.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation5\logs\110424AC.log...
I. 04/24 12:24:45. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:45. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:45.     Checkpointing...
I. 04/24 12:24:45. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:45. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:45. Recovery complete
I. 04/24 12:24:45. Database server shutdown automatically after log applied
I. 04/24 12:24:46. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:46. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:46. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:46. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:46. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:46. Database recovery in progress
I. 04/24 12:24:46.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:46.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation6\ddd12.log...
I. 04/24 12:24:46. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:46. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:46.     Checkpointing...
I. 04/24 12:24:46. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:47. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:47. Recovery complete
I. 04/24 12:24:47. Database server shutdown automatically after log applied
I. 04/24 12:24:47. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:47. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:47. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:47. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:47. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:47. Database recovery in progress
I. 04/24 12:24:47.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:47.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation6\logs\110424AA.log...
I. 04/24 12:24:47. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:48. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:48. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:48. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:48.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation6\logs\110424AB.log...
I. 04/24 12:24:48. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:48. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:49. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:49. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:49.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation6\logs\110424AC.log...
I. 04/24 12:24:49. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:49. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:49.     Checkpointing...
I. 04/24 12:24:49. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:49. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:50. Recovery complete
I. 04/24 12:24:50. Database server shutdown automatically after log applied
I. 04/24 12:24:50. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:50. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:50. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:50. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:50. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:50. Database recovery in progress
I. 04/24 12:24:50.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:50.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation7\ddd12.log...
I. 04/24 12:24:50. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:51. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:51.     Checkpointing...
I. 04/24 12:24:51. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:51. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:51. Recovery complete
I. 04/24 12:24:51. Database server shutdown automatically after log applied
I. 04/24 12:24:51. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:51. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:51. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:51. Automatic tuning of multiprogramming level is enabled
I. 04/24 12:24:51. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:51. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:52. Database recovery in progress
I. 04/24 12:24:52.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:52.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation7\logs\110424AA.log...
I. 04/24 12:24:52. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:52. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:52. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:52. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:52.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation7\logs\110424AB.log...
I. 04/24 12:24:52. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:53. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:53. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:53. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:53.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation7\logs\110424AC.log...
I. 04/24 12:24:53. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:53. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:53.     Checkpointing...
I. 04/24 12:24:53. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:53. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:53. Recovery complete
I. 04/24 12:24:54. Database server shutdown automatically after log applied
I. 04/24 12:24:54. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:54. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:54. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:54. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:54. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:54. Database recovery in progress
I. 04/24 12:24:54.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:54.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation8\ddd12.log...
I. 04/24 12:24:54. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:55. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:55.     Checkpointing...
I. 04/24 12:24:55. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:55. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:55. Recovery complete
I. 04/24 12:24:55. Database server shutdown automatically after log applied
I. 04/24 12:24:55. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:55. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:55. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:55. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:55. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:56. Database recovery in progress
I. 04/24 12:24:56.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:56.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation8\logs\110424AA.log...
I. 04/24 12:24:56. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:56. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:56. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:56. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:56.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation8\logs\110424AB.log...
I. 04/24 12:24:56. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:57. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:57. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:57. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:57.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation8\logs\110424AC.log...
I. 04/24 12:24:57. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:57. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:57.     Checkpointing...
I. 04/24 12:24:57. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:58. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:58. Recovery complete
I. 04/24 12:24:58. Database server shutdown automatically after log applied
I. 04/24 12:24:58. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:58. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:58. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:58. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:58. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:24:58. Database recovery in progress
I. 04/24 12:24:58.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:24:58.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation9\ddd12.log...
I. 04/24 12:24:58. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:59. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:59.     Checkpointing...
I. 04/24 12:24:59. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:59. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:59. Recovery complete
I. 04/24 12:24:59. Database server shutdown automatically after log applied
I. 04/24 12:24:59. Database server stopped at Sun Apr 24 2011 12:24
I. 04/24 12:24:59. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:24:59. Developer edition, not licensed for deployment.
...
I. 04/24 12:24:59. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:24
I. 04/24 12:24:59. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:25:00. Database recovery in progress
I. 04/24 12:25:00.     Last checkpoint at Sun Apr 24 2011 12:24
I. 04/24 12:25:00.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation9\logs\110424AA.log...
I. 04/24 12:25:00. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:00. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:00. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:00. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:00.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation9\logs\110424AB.log...
I. 04/24 12:25:01. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:01. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:01. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:01. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:01.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation9\logs\110424AC.log...
I. 04/24 12:25:01. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:01. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:01.     Checkpointing...
I. 04/24 12:25:02. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:02. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:02. Recovery complete
I. 04/24 12:25:02. Database server shutdown automatically after log applied
I. 04/24 12:25:02. Database server stopped at Sun Apr 24 2011 12:25
I. 04/24 12:25:02. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:25:02. Developer edition, not licensed for deployment.
...
I. 04/24 12:25:02. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:02. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:25:12. Database recovery in progress
I. 04/24 12:25:12.     Last checkpoint at Sun Apr 24 2011 12:25
I. 04/24 12:25:12.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation10\ddd12.log...
I. 04/24 12:25:12. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:13. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:13.     Checkpointing...
I. 04/24 12:25:13. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:13. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:13. Recovery complete
I. 04/24 12:25:13. Database server shutdown automatically after log applied
I. 04/24 12:25:13. Database server stopped at Sun Apr 24 2011 12:25
I. 04/24 12:25:13. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:25:13. Developer edition, not licensed for deployment.
...
I. 04/24 12:25:13. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:13. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:25:14. Database recovery in progress
I. 04/24 12:25:14.     Last checkpoint at Sun Apr 24 2011 12:25
I. 04/24 12:25:14.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation10\logs\110424AA.log...
I. 04/24 12:25:14. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:14. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:14. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:14. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:14.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation10\logs\110424AB.log...
I. 04/24 12:25:14. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:14. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:15. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:15. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:15.     Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\backup\generation10\logs\110424AC.log...
I. 04/24 12:25:15. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:15. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:15.     Checkpointing...
I. 04/24 12:25:15. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:15. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:16. Recovery complete
I. 04/24 12:25:16. Database server shutdown automatically after log applied
I. 04/24 12:25:16. Database server stopped at Sun Apr 24 2011 12:25
I. 04/24 12:25:16. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/24 12:25:16. Developer edition, not licensed for deployment.
...
I. 04/24 12:25:16. Starting database "ddd12" (C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:16. Performance warning: Database file "C:\$ blogs and websites\blog SQLAnywhere\20110425 Demonstrating Backup, Validation and Restore\ddd12.db" consists of 2 disk fragments
I. 04/24 12:25:16. Database recovery in progress
I. 04/24 12:25:16.     Last checkpoint at Sun Apr 24 2011 12:25
I. 04/24 12:25:16.     Transaction log: ddd12.log...
I. 04/24 12:25:16. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:17. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:17.     Checkpointing...
I. 04/24 12:25:17. Starting checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:17. Finished checkpoint of "ddd12" (ddd12.db) at Sun Apr 24 2011 12:25
I. 04/24 12:25:17. Recovery complete
I. 04/24 12:25:17. Database server shutdown automatically after log applied
I. 04/24 12:25:17. Database server stopped at Sun Apr 24 2011 12:25
Sun 04/24/2011 
12:25 PM

The output from 06_display_table.bat shows there are no gaps after the restore:

SELECT COUNT(*), MAX ( pkey ) FROM rapid
COUNT()     MAX(rapid.pkey)
---------------------------
4064        4064

(1 rows)

Execution time: 0.023 seconds

C:\..>PAUSE Note: COUNT should equal MAX
Press any key to continue . . .

Here's the kind of message you will see if one of the log files is missing:

I. 04/24 12:25:16. Cannot open transaction log file -- Can't use log file "C:\ ... \backup\generation9\logs\110405AA.log" since it has been used more recently than the database file

Tip: It's tempting to move all the incremental log backup files into a single folder so only one dbsrv12 -ad step is required. However, it is possible for two different incremental log backup files to have the same file name if they were created on the same day with a full backup in between. That's because the yymmddXX is naming convention is restarted at yymmddAA after each full backup. In this demonstration, because everything happens in rapid succession on a single day, all 10 sets are named yymmddAA, BB and CC.

Finally, here is the 99_stop_drop_everything.bat command file that deletes everything so that 01_run_everything.bat run over again:

PAUSE Are you sure you want to delete all the files?

"%SQLANY12%\bin32\dbstop.exe" -c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql" -y

PAUSE Wait until the database is stopped, then

ERASE /F ddd12.db
ERASE /F ddd12.log
ERASE /F dbsrv12_log_ddd12.txt
ERASE /F dbsrv12_log_fatal_ddd12.txt
RMDIR /S /Q backup

PAUSE

The fRiDaY File

This was first published on March 29, 2006:

Validating Backups

Question: What's the best way to validate a database?

Answer: Don't do it. Create a backup and run dbvalid.exe against that.

Here are the advantages:

• If dbvalid says your backup copy is valid, then you know two things: that your backup is valid, and your running database is valid... or at least it was when the backup was taken. If you run dbvalid on your running database first and then create a backup, you don't know for sure if your backup is valid... the backup process itself could have introduced disk errors, for example.

• You can run dbvalid in read-only mode on your backup copy, thus avoiding false validation errors caused by transactions in progress.

• You can move the backup copy to a different computer and run dbvalid there, thus avoiding database performance problems.

• You can combine and automate and schedule the whole process without interfering with day-to-day operations, and without forgetting to run one or the other (backup and validation).

---

Question: What's the best way to validate incremental transaction log backups?

Answer: By running dbsrv12 -ad to apply each new log backup to a copy of the database backup.

Here are the advantages:

• If you run dbsrv12 -ad right away, to apply each incremental transaction log backup file to a running copy of your previous full database backup file, then you know that all the transaction log backups are fully operational and ready when you need them... because dbsrv12 -ad is exactly what you'll be using after disaster strikes.

• The running copy of your database backup is relatively up to date, in case that's the version you need when it comes time to restore... but, you don't have to use it, if you need to back up to an earlier restore point.

• Only one extra copy of the database is required, above and beyond the running database, the full database backup file and all the incremental log backup files. That extra copy may also be used to validate the database as described above; if you start the database with dbsrv12 -r when running dbvalid, you can still run dbsrv12 -ad later on to apply more transaction logs.

Knowing your backup is valid is more important than knowing your running database is valid.

Sybase TechDays 2011

Ah, London, Paris, Madrid! ...here's your chance to see a bit of Europe, but hurry!

---

Sybase Showcases Latest Innovation at 6-City European Tour

To help today's businesses to better compete, drive business value and effectively grow, Sybase is hosting the Sybase TechDays 2011 Tour in 6 cities across Europe. Sybase has rapidly become a recognized leader in the database, data warehousing, analytics and mobility markets through its fast-paced innovations. The 2010 acquisition of Sybase by SAP opens up very exciting opportunities for our customers and our products. The event series presents the opportunity to hear about Sybase's technology advancements in areas such as real-time, business analytics and in-memory technologies and helping attendees understand how to capitalize on these innovations to capture and extend competitive advantage.

Running in May 2011, the Sybase TechDays 2011 Tour is a must attend event for Sybase customers, partners and prospects. Each event will feature keynote addresses, roadmaps presentations and demonstrations.

Frankfurt/Offenbach, Germany - May 10 - Register Now
London, UK - May 11 - Register Now
Paris, France - May 12 - Register Now
Madrid, Spain - May 17 - Register Now
Amsterdam, Netherlands - May 18 - Register Now
Vienna, Austria - May 19 & 20 - Register Now

---

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.

The fRiDaY File

This was first published on March 28, 2006:

Performance Versus Perfection

Sometimes you have to accept a different result set to get a query to perform adequately... sometimes, it's not enough to just add indexes or rely on the query optimizer. Here is the story of one such query...

---

In Foxhound, a "sample set" consists of a single snapshot of SQL Anywhere statistical properties for a target database: one set of server- and database-level properties for that target, plus one set of connection-level properties for each connection to that target. The sample set is stored in three tables with rroad_sample_set being the parent table:

-- DBA.rroad_sample_set (table_id 759) in foxhound1 - Apr 14 2011 7:32:37AM - Print - Foxhound © 2011 RisingRoad

CREATE TABLE DBA.rroad_sample_set ( -- 3,709,758 rows, 425M total = 285M table + 0 ext + 140M index, 121 bytes per row
   sampling_id                              /*         X */ UNSIGNED INT NOT NULL,
   sample_set_number                        /* PK        */ UNSIGNED BIGINT NOT NULL DEFAULT autoincrement,
   sample_lost                                              VARCHAR ( 1 ) NOT NULL DEFAULT 'N',
   connected_ok                                             VARCHAR ( 1 ) NOT NULL DEFAULT 'Y',
   exception_description                                    VARCHAR ( 32767 ) NOT NULL DEFAULT '',
   sample_elapsed_msec                                      BIGINT NOT NULL COMPUTE ( ... ),
   sample_started_at                        /*         X */ TIMESTAMP NOT NULL DEFAULT '1900-01-01',
   sample_finished_at                                       TIMESTAMP NOT NULL DEFAULT '1900-01-01',
   canarian_query_started_at                                TIMESTAMP NOT NULL DEFAULT '1900-01-01',
   canarian_query_finished_at                               TIMESTAMP NOT NULL DEFAULT '1900-01-01',
   datediff_msec_between_target_and_local                   BIGINT NOT NULL DEFAULT 0,
   CONSTRAINT ASA254 PRIMARY KEY ( -- 52.8M
      sample_set_number )
 );

CREATE CLUSTERED INDEX ix_sample_started_at ON DBA.rroad_sample_set ( -- 45M
   sample_started_at );

CREATE INDEX ix_sampling_id ON DBA.rroad_sample_set ( -- 42.6M
   sampling_id );

For the purposes of this article, here are the important columns:

• sampling_id identifies which target database the sample applies to,

• sample_set_number is the autoincrementing primary key which uniquely identifies the sample set across all samples for all targets,

• sample_started_at records the timestamp when Foxhound started the recording process, and

• sample_finished_at records when Foxhound finished recording the sample.

The sample_set_number and sample_started_at columns are both candidates for CLUSTERED indexes, but previous query optimization efforts (involving different queries elsewhere in Foxhound) indicated that a CLUSTERED index on sample_started_at was the more useful of the two.

Sadly, many important queries use sample_finished_at because that's the natural order of samples from the application's point of view; most samples take very little time to record, but some take longer and it is the time the sample finished at that is important. That was the case with the following Query From Hell which over 16 seconds to return a single value in one particular test (and sometimes required over a minute).

Version 1: 16 seconds

This query is used by the Foxhound Monitor History display to scroll backwards by one week (sample_finished_at <= DATEADD(week,-1,CURRENT TIMESTAMP) in the samples gathered for one particular target database (sampling_id = 8 in this case):

  select top 1 rroad_sample_set.sample_set_number
    from rroad_sample_set
    where rroad_sample_set.sampling_id = 8
    and rroad_sample_set.sample_finished_at
     <= DATEADD(week,-1,CURRENT TIMESTAMP)
    order by rroad_sample_set.sample_set_number desc;

An earlier optimization had already changed the query from ORDER BY sample_finished_at (which was functionally correct) to ORDER BY sample_set_number because there's an index on sample_set_number but no index on sample_finished_at. That change sometimes (rarely?) resulted in a slightly different return value, but because it was so very slow the decision was made to live with that possibility... after all, scrolling up and down isn't quite as important as calculating a bank balance [or so went the rationalization, more of which comes later].

The graphical plan showed that yes indeed, the query optimizer chose the primary key index on sample_set_number:

Version 2: 15 seconds

The next attempt at optimization changed the WHERE clause to use sample_started_at instead of sample_finished_at because there's an index on the former but not the latter.

select top 1 rroad_sample_set.sample_set_number
  from rroad_sample_set 
    where rroad_sample_set.sampling_id = 8
    and rroad_sample_set.sample_started_at
     <= DATEADD(week,-1,CURRENT TIMESTAMP)
    order by rroad_sample_set.sample_set_number desc;

This turned out to be a disappointment, probably because SQL Anywhere only uses one index per table and it was already using the index on sample_set_number. The graphical plan showed that the existence of a CLUSTERED index on sample_started_at, plus the fact the WHERE clause was using a range query (sample_started_at <= DATEADD...), wasn't enough to get the query optimizer to switch; it still used the primary key index on sample_set_number:

Version 3: 59 seconds

The next attempt, forcing SQL Anywhere to use the CLUSTERED index on sample_started_at, turned out to be a disaster; instead of just being slow the query now moved with all the grace and speed of continental drift (four times slower):

select top 1 rroad_sample_set.sample_set_number
  from rroad_sample_set

          FORCE INDEX ( ix_sample_started_at )

    where rroad_sample_set.sampling_id = 8
    and rroad_sample_set.sample_started_at
     <= DATEADD(week,-1,CURRENT TIMESTAMP)
    order by rroad_sample_set.sample_set_number desc;

The graphical plan shows that SQL Anywhere did obey the FORCE INDEX clause but still took forever:

Version 4: 0.08 seconds

The final attempt was a test of the following proposition:

Perhaps the ORDER BY is more important than the range query in the WHERE clause.

The ORDER BY was changed to use sample_started_at instead of sample_set_number, and the FORCE INDEX was ripped out:

select top 1 rroad_sample_set.sample_set_number
  from rroad_sample_set
    where rroad_sample_set.sampling_id = 8
    and rroad_sample_set.sample_started_at
     <= DATEADD(week,-1,CURRENT TIMESTAMP)
    order by rroad_sample_set.sample_started_at desc;

Success! The graphical plan showed the same index was used but this time the numbers were much lower; clearly it was more important to use the CLUSTERED index for the ORDER BY than for the range query:



Here's the moral of the story: Sometimes you have to consider trading off perfection for performance, especially if absolute correctness isn't required. In this case, using a query that is guaranteed to return the exact row that was created one week earlier isn't necessary, for two reasons:

• the perfect result changes over time depending on when the query is run, and

• in practice all versions of the query returned the same result when run at the same time.

In other words, the imperfect query wasn't just good enough, it actually returned the right result.

And the original perfect query was unusable... nobody's going to wait 16 seconds to scroll.