Friday, April 29, 2011

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.


Wednesday, April 27, 2011

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.

Monday, April 25, 2011

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

Friday, April 22, 2011

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.

Wednesday, April 20, 2011

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

Monday, April 18, 2011

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.

Friday, April 15, 2011

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.

Wednesday, April 13, 2011

Techwave - Call for Conference Presentations!

from    Sybase TechWave 
date Thu, Apr 7, 2011 at 5:40 PM
subject Submit Your Session Idea Today


Have you used Sybase products to solve a business challenge, to increase your data’s mobility or to improve query speeds? If you have a solution you would like to share with your friends and colleagues, submit a session proposal for TechWave. Sessions should focus on the following topics: Data Management, Analytics, Development Tools, mCommerce, or Enterprise Mobility.

Sybase TechWave is September 12-16, in Las Vegas. Speakers must be available to present during the entire span of the conference. The deadline to submit a session for consideration is May 6. Acceptance notifications will be sent out in June.

Sessions need to be educational and technically focused. Please review the guidelines and examples provided on the session proposal website.

We offer complimentary registration to approved speakers. TechWave does not reimburse for speakers' time, travel, or hotel costs. If you are selected as a presenter, your final presentation is due July 31.

Be sure to bookmark the TechWave website, where all the latest information will be posted. And follow @SyTechWave on Twitter for conference updates.

Sincerely,
The Sybase TechWave Team


Important Dates
May 6
Session Proposals Due

July 31
Final Presentation Slides Due

Session Proposal Guidelines
Title – 75 characters
Abstract – 200 words
Speaker Bio – 250 characters
Must be detailed and
descriptive enough to catch
the interest of attendees

For additional information visit the
session proposal website.


Monday, April 11, 2011

Wishful Thinking: Monitoring Plans In Production

Updated November 25, 2013: This "April Fools' Day" article was originally published with the title "Monitoring Plans In Production". Sadly, it continues to show up in Google searches for "GRAPHICAL_PLAN", continuing to fool readers long past the original publishing date... hence, the new title, and this comment.



(This article is reprinted here with permission. It was first published on April 1, 2014.)

Folks often know exactly which queries qualify as Queries From Hell, and they often have ongoing problems with execution plans that change over time to cause performance to oscillate by orders of magnitude.

The most recent version of SQL Anywhere introduced a new feature for monitoring execution plans in production, called "Log Expensive Queries", similar to the older GRAPHICAL_PLAN function. Here's what the syntax looks like:
SET TEMPORARY OPTION LOG_EXPENSIVE_QUERIES = { 'ON' | 'OFF' };

SELECT [ other-clauses ] ...
[ LOG_EXPENSIVE_QUERY ( xml-plan, threshold-milliseconds ) ]

When a query containing a LOG_EXPENSIVE_QUERY clause is encountered during execution, the clause is ignored if the connection option LOG_EXPENSIVE_QUERIES is currently 'OFF'; there is no performance penalty in this case.

If LOG_EXPENSIVE_QUERIES is 'ON', however, the query engine preserves information about the execution plan until the query finishes executing. At that point, if the actual run time for the query was equal to or greater than the threshold-milliseconds argument in the LOG_EXPENSIVE_QUERY clause, the XML version of the plan is returned in the xml-plan argument. It is then up to the application to do something with value in xml-plan; e.g., save it in a table or write it to a file.

If LOG_EXPENSIVE_QUERIES is 'ON' but the actual run time does not reach threshold-milliseconds, the xml-plan argument is set to NULL.

To demonstrate, here is an excerpt from a large stored procedure showing a query that took almost 123 seconds to run:
CREATE PROCEDURE ...
...
SELECT TOP 1 rroad_sample_set.sample_set_number
INTO @month_older_sample_set_number
FROM rroad_sample_set
WHERE rroad_sample_set.sampling_id = @sampling_id
AND rroad_sample_set.sample_finished_at
<= DATEADD ( MONTH, -1, @sample_finished_at )
ORDER BY rroad_sample_set.sample_set_number DESC;
...
END;
Here's how the code was modified to use the new Log Expensive Queries feature:
CREATE TABLE saplan (
pkey       INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
xml_plan   XML );

CREATE PROCEDURE ...
...
DECLARE @xml_plan XML;
...

SELECT TOP 1 rroad_sample_set.sample_set_number
INTO @month_older_sample_set_number
FROM rroad_sample_set
WHERE rroad_sample_set.sampling_id = @sampling_id
AND rroad_sample_set.sample_finished_at
<= DATEADD ( WEEK, -1, @sample_finished_at )
ORDER BY rroad_sample_set.sample_set_number DESC
LOG_EXPENSIVE_QUERY ( @xml_plan, 10000 );

IF @xml_plan IS NOT NULL THEN
INSERT saplan ( xml_plan ) VALUES ( @xml_plan );
SET TEMPORARY OPTION LOG_EXPENSIVE_QUERIES = 'OFF'; 
END IF;
...
END;
The table defined on lines 1 to 3 is used to store the plans captured at run time. The variable declared on line 7 is used as the first argument to the LOG_EXPENSIVE_QUERY clause. The LOG_EXPENSIVE_QUERY clause on line 17 names @xml_plan as the variable to receive the plan, and sets the threshold to 10,000 milliseconds. The code on lines 19 to 22 checks to see if the LOG_EXPENSIVE_QUERY clause did in fact capture a plan, and if so, it saves that plan in the saplan table and sets the LOG_EXPENSIVE_QUERIES option to 'OFF' so no more plans are captured. After the procedure ran in production and the code save the first plan, here's what the saplan table looks like: The dbisql utility has been enhanced to recognize XML values that contain graphical plans so that when you doubleclick on a plan it opens the Plan Viewer: Note that the Plan Viewer SQL pane shows the query after all rewrite optimizations have been performed, and after the actual values 8 and '2011 04 07 13:43:05.932' have been substituted for the host variables @sampling_id and @sample_finished_at.
(There is, of course, no such thing as the LOG_EXPENSIVE_QUERY clause... but there should be :)

Friday, April 8, 2011

The fRiDaY File

Recovering Across Multiple Backup Generations

Let's say you have 10 generations of database backups when you discover that the current database file is corrupt.

You restore from the most recent full backup and apply the subsequent incremental log backups (like in Demonstrating Full and Incremental Backups)...

only to discover...

Horrors!


...that the most recent backup of the database is corrupt too!

Bite The Bullet!


Now let's say you're not sure when the database first became corrupted, but you're pretty sure that the oldest backup is OK, and you don't have time to go back one generation at a time.

The following demonstration shows how you can go all the way back to the beginning and
  • restore the oldest backup (generation 1 of 10), then

  • apply all the subsequent log files to get rid of the corruption without losing any data.
These Help topics apply...

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 to record progress,

  • make 10 generations of backup files,

    • each consisting of a full backup

    • followed by three consecutive incremental log backups

    • with a new row inserted in the table after each full backup

    • and after the third incremental backup in each generation

    • for a total of 20 rows, then

  • show the rows as they exist before the restore begins,

  • delete the current database,

  • restore the full database backup file from generation 1,

  • apply all the subsequent incremental transaction log backup files

  • plus the current log file,

  • start the database and

  • show how all the rows have been restored.

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

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

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

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

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

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

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

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

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

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

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

CALL 08_run_script_to_display_rows.bat
PAUSE Make a note how many rows exist in t1...

CALL 09_stop_drop_database.bat
CALL 10_restore_database.bat
CALL 03_run_dbspawn_dbsrv12.bat

CALL 08_run_script_to_display_rows.bat
PAUSE Note that the last row is missing from t1...
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_dbisql_to_create_table.bat creates a table:

"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
CREATE TABLE t1 ( pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY );

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
DATE /T >>backup\generation_temp\dbbackup_log.txt
TIME /T >>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 GOTO backup_failed

REM ******************************************************************
REM Move backup to generation10 subfolder
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 ..
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
DATE /T >>backup\generation_temp\dbbackup_log.txt
TIME /T >>backup\generation_temp\dbbackup_log.txt
GOTO end

:end

06_run_script.bat inserts a row into the table; this command is executed 20 times, after each full backup and each set of 3 incremental backups:

"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
INSERT t1 VALUES ( DEFAULT );

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
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt

REM ******************************************************************
REM Make sure a full backup exists
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
ECHO Create a full backup before an incremental backup. >>backup\generation10\dbbackup_log.txt
GOTO backup_failed

:full_backup_exists
REM ******************************************************************
REM Backup to generation10\logs subfolder
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 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
DATE /T >>backup\generation10\dbbackup_log.txt
TIME /T >>backup\generation10\dbbackup_log.txt
GOTO end

:end

08_run_script_to_display_rows.bat shows all the rows in the table:

"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
SELECT * FROM t1 ORDER BY pkey;

Here's what the table looks like after all the backups have been completed, before the restore process begins:

pkey
-----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

(20 rows)

Execution time: 0.051 seconds

Restore


09_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.

In the real world most administrators won't delete the current database or log file no matter how dire the situation; they'll just move them to some other location. In particular, the current transaction log may be of some use, and that is certainly the case here.

10_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 The first dbsrv12 -ad backup\generation1 step is optional.
REM All the other dbsrv12 -ad steps are required.

"%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
Wed 04/06/2011
06:54 AM
I. 04/06 06:54:56. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/06 06:54:56. Developer edition, not licensed for deployment.
...
I. 04/06 06:54:57. Database recovery in progress
I. 04/06 06:54:57. Last checkpoint at Wed Apr 06 2011 06:51
I. 04/06 06:54:57. Checkpoint log...
I. 04/06 06:54:57. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation1\ddd12.log...
I. 04/06 06:54:57. Checkpointing...
I. 04/06 06:54:57. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:54
I. 04/06 06:54:57. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:54
I. 04/06 06:54:58. Recovery complete
...
I. 04/06 06:54:58. Database recovery in progress
I. 04/06 06:54:58. Last checkpoint at Wed Apr 06 2011 06:54
I. 04/06 06:54:58. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation1\logs\110406AA.log...
I. 04/06 06:54:58. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:54
I. 04/06 06:54:59. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:54
I. 04/06 06:54:59. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:54
I. 04/06 06:54:59. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:54
I. 04/06 06:54:59. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation1\logs\110406AB.log...
I. 04/06 06:55:00. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:00. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:00. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:01. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:01. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation1\logs\110406AC.log...
I. 04/06 06:55:01. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:01. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:01. Checkpointing...
I. 04/06 06:55:01. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:01. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:01. Recovery complete
...
I. 04/06 06:55:02. Database recovery in progress
I. 04/06 06:55:02. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:02. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation2\ddd12.log...
I. 04/06 06:55:02. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:03. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:03. Checkpointing...
I. 04/06 06:55:03. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:03. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:03. Recovery complete
...
I. 04/06 06:55:04. Database recovery in progress
I. 04/06 06:55:04. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:04. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation2\logs\110406AA.log...
I. 04/06 06:55:04. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:04. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:04. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:05. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:05. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation2\logs\110406AB.log...
I. 04/06 06:55:05. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:06. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:06. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:06. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:06. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation2\logs\110406AC.log...
I. 04/06 06:55:06. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:06. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:07. Checkpointing...
I. 04/06 06:55:07. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:07. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:07. Recovery complete
...
I. 04/06 06:55:08. Database recovery in progress
I. 04/06 06:55:08. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:08. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation3\ddd12.log...
I. 04/06 06:55:09. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:09. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:09. Checkpointing...
I. 04/06 06:55:09. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:10. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:10. Recovery complete
...
I. 04/06 06:55:11. Database recovery in progress
I. 04/06 06:55:11. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:11. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation3\logs\110406AA.log...
I. 04/06 06:55:11. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:11. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:11. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:12. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:12. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation3\logs\110406AB.log...
I. 04/06 06:55:12. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:12. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:12. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:13. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:13. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation3\logs\110406AC.log...
I. 04/06 06:55:13. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:13. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:13. Checkpointing...
I. 04/06 06:55:13. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:14. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:14. Recovery complete
...
I. 04/06 06:55:15. Database recovery in progress
I. 04/06 06:55:15. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:15. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation4\ddd12.log...
I. 04/06 06:55:15. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:15. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:15. Checkpointing...
I. 04/06 06:55:15. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:16. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:16. Recovery complete
...
I. 04/06 06:55:16. Database recovery in progress
I. 04/06 06:55:16. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:16. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation4\logs\110406AA.log...
I. 04/06 06:55:17. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:17. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:17. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:17. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:17. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation4\logs\110406AB.log...
I. 04/06 06:55:17. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:18. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:18. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:18. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:18. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation4\logs\110406AC.log...
I. 04/06 06:55:18. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:18. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:18. Checkpointing...
I. 04/06 06:55:19. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:19. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:19. Recovery complete
...
I. 04/06 06:55:20. Database recovery in progress
I. 04/06 06:55:20. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:20. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation5\ddd12.log...
I. 04/06 06:55:20. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:21. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:21. Checkpointing...
I. 04/06 06:55:21. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:21. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:21. Recovery complete
...
I. 04/06 06:55:22. Database recovery in progress
I. 04/06 06:55:22. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:22. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation5\logs\110406AA.log...
I. 04/06 06:55:22. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:22. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:22. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:23. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:23. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation5\logs\110406AB.log...
I. 04/06 06:55:23. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:23. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:23. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:23. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:24. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation5\logs\110406AC.log...
I. 04/06 06:55:24. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:24. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:24. Checkpointing...
I. 04/06 06:55:24. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:25. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:25. Recovery complete
...
I. 04/06 06:55:26. Database recovery in progress
I. 04/06 06:55:26. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:26. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation6\ddd12.log...
I. 04/06 06:55:26. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:26. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:26. Checkpointing...
I. 04/06 06:55:26. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:27. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:27. Recovery complete
...
I. 04/06 06:55:27. Database recovery in progress
I. 04/06 06:55:27. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:27. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation6\logs\110406AA.log...
I. 04/06 06:55:28. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:28. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:28. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:28. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:28. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation6\logs\110406AB.log...
I. 04/06 06:55:28. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:29. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:29. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:30. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:30. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation6\logs\110406AC.log...
I. 04/06 06:55:30. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:30. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:30. Checkpointing...
I. 04/06 06:55:30. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:31. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:31. Recovery complete
...
I. 04/06 06:55:32. Database recovery in progress
I. 04/06 06:55:32. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:32. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation7\ddd12.log...
I. 04/06 06:55:32. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:32. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:32. Checkpointing...
I. 04/06 06:55:32. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:32. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:32. Recovery complete
...
I. 04/06 06:55:33. Database recovery in progress
I. 04/06 06:55:33. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:33. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation7\logs\110406AA.log...
I. 04/06 06:55:33. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:34. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:34. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:34. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:34. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation7\logs\110406AB.log...
I. 04/06 06:55:34. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:35. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:35. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:35. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:35. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation7\logs\110406AC.log...
I. 04/06 06:55:35. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:36. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:36. Checkpointing...
I. 04/06 06:55:36. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:36. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:36. Recovery complete
...
I. 04/06 06:55:37. Database recovery in progress
I. 04/06 06:55:37. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:37. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation8\ddd12.log...
I. 04/06 06:55:37. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:37. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:37. Checkpointing...
I. 04/06 06:55:37. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:38. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:38. Recovery complete
...
I. 04/06 06:55:39. Database recovery in progress
I. 04/06 06:55:39. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:39. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation8\logs\110406AA.log...
I. 04/06 06:55:39. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:39. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:39. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:40. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:40. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation8\logs\110406AB.log...
I. 04/06 06:55:40. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:40. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:40. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:41. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:41. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation8\logs\110406AC.log...
I. 04/06 06:55:41. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:41. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:41. Checkpointing...
I. 04/06 06:55:41. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:42. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:42. Recovery complete
...
I. 04/06 06:55:42. Database recovery in progress
I. 04/06 06:55:42. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:42. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation9\ddd12.log...
I. 04/06 06:55:43. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:43. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:43. Checkpointing...
I. 04/06 06:55:43. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:43. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:43. Recovery complete
...
I. 04/06 06:55:44. Database recovery in progress
I. 04/06 06:55:44. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:44. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation9\logs\110406AA.log...
I. 04/06 06:55:44. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:45. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:45. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:45. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:45. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation9\logs\110406AB.log...
I. 04/06 06:55:45. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:46. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:46. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:46. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:46. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation9\logs\110406AC.log...
I. 04/06 06:55:46. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:47. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:47. Checkpointing...
I. 04/06 06:55:47. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:47. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:47. Recovery complete
...
I. 04/06 06:55:58. Database recovery in progress
I. 04/06 06:55:58. Last checkpoint at Wed Apr 06 2011 06:55
I. 04/06 06:55:58. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation10\ddd12.log...
I. 04/06 06:55:58. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:55:59. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:55
I. 04/06 06:56:00. Checkpointing...
I. 04/06 06:56:00. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:00. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:00. Recovery complete
...
I. 04/06 06:56:01. Database recovery in progress
I. 04/06 06:56:01. Last checkpoint at Wed Apr 06 2011 06:56
I. 04/06 06:56:01. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation10\logs\110406AA.log...
I. 04/06 06:56:01. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:01. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:01. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:02. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:02. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation10\logs\110406AB.log...
I. 04/06 06:56:02. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:02. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:02. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:03. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:03. Transaction log: C:\$ blogs and websites\blog SQLAnywhere\20110499 Recovering Across Multiple Backup Generations\backup\generation10\logs\110406AC.log...
I. 04/06 06:56:03. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:03. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:03. Checkpointing...
I. 04/06 06:56:03. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:04. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:04. Recovery complete
...
I. 04/06 06:56:05. Database recovery in progress
I. 04/06 06:56:05. Last checkpoint at Wed Apr 06 2011 06:56
I. 04/06 06:56:05. Transaction log: ddd12.log...
I. 04/06 06:56:05. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:05. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:05. Checkpointing...
I. 04/06 06:56:05. Starting checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:06. Finished checkpoint of "ddd12" (ddd12.db) at Wed Apr 06 2011 06:56
I. 04/06 06:56:06. Recovery complete
I. 04/06 06:56:06. Database server shutdown automatically after log applied
I. 04/06 06:56:06. Database server stopped at Wed Apr 06 2011 06:56
Wed 04/06/2011
06:56 AM

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

E. 04/05 15:30:34. 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

Here's what the table looks like after the restore; all 20 rows have been restored, even the row that was inserted after the last backup (it was restored by the final dbsrv12 -a step in 10_restore_database.bat):

pkey
-----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

(20 rows)

Execution time: 0.039 seconds

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 *.log
ERASE /F dbsrv12_log_ddd12.txt
ERASE /F dbsrv12_log_fatal_ddd12.txt
RMDIR /S /Q backup

PAUSE