Friday, April 1, 2011

Demonstrating Full and Incremental Backups

This article demonstrates two Windows command files which can be used to automate both full and incremental log backups of a SQL Anywhere 12 database:

  • The first command file creates a full backup of the database and transaction log files and preserves the last 10 full backups.

  • The second command file creates an incremental backup of the transaction log file and stores an unlimited number of those backups together with the most recent full backup.
Those command files are numbers 5 and 7 in a series of 15 command files used to present the demonstration; here's where you can download the full set...


Here's the $read_me.txt file:
2011 04 01  Demonstrate Restoring from an Incremental Backup

Run these commands in order to create, backup, modify and restore a SQL Anywhere 12 database:

01_run_everything.bat                    CALLs all the command files 02 through 15
02_run_dbinit12.bat                      create database
03_run_dbspawn_dbsrv12.bat               start database
04_run_script.bat                        run 04s_script_to_initialize_data.sql
05_run_dbbackup12_full.bat               full backup
06_run_script.bat                        run 06s_script_to_change_data.sql
07_run_dbbackup12_incremental.bat        incremental backup
08_run_script.bat                        run 08s_script_to_change_data.sql
09_run_dbbackup12_incremental.bat         [ repeat 07 ]
10_run_script.bat                        run 10s_script_to_change_data.sql
11_run_dbbackup12_incremental.bat         [ repeat 07 ]
12_stop_drop_database.bat                delete database
13_restore_database.bat                  apply incremental log backups
14_run_dbspawn_dbsrv12.bat                [ repeat 03 ]
15_run_script.bat                        run 15s_script_to_display_data.sql

To delete everything so the demonstration can be repeated:

16_stop_drop_everything.bat

Command file 01_run_everything.bat automates the whole demonstration by calling every other command file from 02 through 15:
CALL 02_run_dbinit12.bat
CALL 03_run_dbspawn_dbsrv12.bat
CALL 04_run_script.bat
CALL 05_run_dbbackup12_full.bat
CALL 06_run_script.bat
CALL 07_run_dbbackup12_incremental.bat
CALL 08_run_script.bat
CALL 09_run_dbbackup12_incremental.bat
CALL 10_run_script.bat
CALL 11_run_dbbackup12_incremental.bat
CALL 12_stop_drop_database.bat
CALL 13_restore_database.bat
CALL 14_run_dbspawn_dbsrv12.bat
CALL 15_run_script.bat
PAUSE All done...

Command file 02_run_dbinit12.bat starts the demonstration by creating an empty SQL Anywhere 13 database:
"%SQLANY12%\bin32\dbinit.exe"^
ddd12.db

Command file 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 

Command file 04_run_script.bat create a table and inserts one row:
"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
READ ENCODING Cp1252 04s_script_to_initialize_data.sql

Here is the script file 04s_script_to_initialize_data.sql:
CREATE TABLE t1 (
pkey   INTEGER NOT NULL PRIMARY KEY,
data   INTEGER NOT NULL DEFAULT 0 );

INSERT t1 VALUES ( 1, 1 );

COMMIT;

Rotating Full Backup


Command file 05_run_dbbackup12_full.bat creates the full backup; you can set up a Windows Task Scheduler entry to run it on a regular basis (e.g., every day at 1 AM):
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
Line 3 of the full backup command file 05_run_dbbackup12_full.bat makes sure that the backup subfolder exists. Lines 4 and 5 deletes the subfolder backup\generation_temp if it exists, and line 6 creates it again; this is where the new backup files will reside until the backup process has finished successfully.

The ECHO command on lines 9 and 10 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 15 through 19 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 command on line 21 skips to the end of the command file if dbbackup.exe failed.

The commands on lines 23 through 37 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 38 through 42 record a successful backup in the diagnostic log file, and the commands on lines 44 through 50 record a failure.

Here's a snippet from dbbackup_log.txt showing the full backup:
*****************************************************************************************
Full dbbackup started 
Fri 04/01/2011 
09:32 AM
SQL Anywhere Backup Utility Version 12.0.1.3298
(582 of estimated 582 pages, 100% complete)
Transaction log truncated
Database backup completed
Volume in drive C is OS
Volume Serial Number is 5CD7-70E9

Directory of C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\backup\generation10

04/01/2011  09:32 AM    <DIR>          .
04/01/2011  09:32 AM    <DIR>          ..
04/01/2011  09:32 AM               291 dbbackup_log.txt
04/01/2011  09:32 AM         2,367,488 ddd12.db
04/01/2011  09:32 AM             8,192 ddd12.log
3 File(s)      2,375,971 bytes

Total Files Listed:
3 File(s)      2,375,971 bytes
2 Dir(s)  114,212,237,312 bytes free
Full dbbackup OK 
Fri 04/01/2011 
09:32 AM

Command file 06_run_script.bat inserts another row:
"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
READ ENCODING Cp1252 06s_script_to_change_data.sql

Here is the script file 06s_script_to_change_data.sql:
INSERT t1 VALUES ( 2, 2 );

COMMIT;

Incremental Log Backup


Command file 07_run_dbbackup12_incremental.bat creates an incremental log backup; you can set up a Windows Task Scheduler entry to run it on a regular basis (e.g., every hour from 6 AM to 6 PM):
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
Lines 1 through 5 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 7 through 16 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 18 through 23 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 25 through 31 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 command on line 33 skips to the end of the command file if dbbackup.exe failed.

The commands on lines 35 through 41 record a successful backup in the diagnostic log file, and the commands on lines 43 through 49 record a failure.

Here's a snippet from dbbackup_log.txt showing one incremental log backup:
*****************************************************************************************
Incremental dbbackup started 
Fri 04/01/2011 
09:32 AM
SQL Anywhere Backup Utility Version 12.0.1.3298
(2 of estimated 2 pages, 100% complete)
Transaction log truncated
Database backup completed
Volume in drive C is OS
Volume Serial Number is 5CD7-70E9

Directory of C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\backup\generation10

04/01/2011  09:32 AM    <DIR>          .
04/01/2011  09:32 AM    <DIR>          ..
04/01/2011  09:32 AM             1,221 dbbackup_log.txt
04/01/2011  09:32 AM         2,367,488 ddd12.db
04/01/2011  09:32 AM             8,192 ddd12.log
04/01/2011  09:32 AM    <DIR>          logs
3 File(s)      2,376,901 bytes

Directory of C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\backup\generation10\logs

04/01/2011  09:32 AM    <DIR>          .
04/01/2011  09:32 AM    <DIR>          ..
04/01/2011  09:32 AM             8,192 110401AA.log
1 File(s)          8,192 bytes

Total Files Listed:
4 File(s)      2,385,093 bytes
5 Dir(s)  114,212,229,120 bytes free
Incremental dbbackup OK 
Fri 04/01/2011 
09:32 AM

Command file 08_run_script.bat inserts a third row and creates a second table:
"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
READ ENCODING Cp1252 08s_script_to_change_data.sql

Here is the script file 08s_script_to_change_data.sql:
INSERT t1 VALUES ( 3, 3 );

COMMIT;

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

INSERT t2 VALUES ( 11, 11 );

COMMIT;

Command file 09_run_dbbackup12_incremental.bat creates another incremental log backup:
CALL 07_run_dbbackup12_incremental.bat

Command file 10_run_script.bat inserts a second row in the new table:
"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
READ ENCODING Cp1252 10s_script_to_change_data.sql

Here is the script file 10s_script_to_change_data.sql:
INSERT t2 VALUES ( 22, 22 );

COMMIT;

Command file 11_run_dbbackup12_incremental.bat creates another incremental log backup:
CALL 07_run_dbbackup12_incremental.bat

Restore


Command file 12_stop_drop_database.bat simulates disaster by deleting the database:
PAUSE Are you sure you want to delete the database?

"%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

Command file 13_restore_database.bat restores the full backup and applies the incremental log backups:
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\logs

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

Here's a snippet from dbbackup_log.txt showing how the dbsrv12 -ad option worked to apply the incremental transaction logs:
*****************************************************************************************
Restore started 
Fri 04/01/2011 
09:32 AM
I. 04/01 09:32:48. SQL Anywhere Network Server Version 12.0.1.3298 I. 04/01 09:32:48. Developer edition, not licensed for deployment.
I. 04/01 09:32:48.  I. 04/01 09:32:48. Copyright © 2001-2011, iAnywhere Solutions, Inc.
I. 04/01 09:32:48. Portions copyright © 1988-2011, Sybase, Inc. All rights reserved.
I. 04/01 09:32:48. Use of this software is governed by the Sybase License Agreement.
I. 04/01 09:32:48. Refer to http://www.sybase.com/softwarelicenses.
I. 04/01 09:32:48.  I. 04/01 09:32:48. Connection limit (licensed seats): 3
I. 04/01 09:32:48. Processors detected: 1 (containing 8 logical processors)
I. 04/01 09:32:48. Maximum number of physical processors the server will use: 1
I. 04/01 09:32:48. This server is licensed to:
I. 04/01 09:32:48.     Developer Edition
I. 04/01 09:32:48.     Restricted Use
I. 04/01 09:32:48. Running Windows 7 Build 7600  on X86_64
I. 04/01 09:32:48. Server built for X86 processor architecture
I. 04/01 09:32:49. 3216K of memory used for caching
I. 04/01 09:32:49. Minimum cache size: 2956K, maximum cache size: 3473660K
I. 04/01 09:32:49. Using a maximum page size of 4096 bytes
I. 04/01 09:32:49. Multiprogramming level: minimum:8, current:20, maximum:80
I. 04/01 09:32:49. Automatic tuning of multiprogramming level is enabled
I. 04/01 09:32:49. Starting database "ddd12" (C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:49. Database recovery in progress
I. 04/01 09:32:49.     Last checkpoint at Fri Apr 01 2011 09:32
I. 04/01 09:32:49.     Checkpoint log...
I. 04/01 09:32:49.     Transaction log: C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\backup\generation10\logs\110401AA.log...
I. 04/01 09:32:49. Starting checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:49. Finished checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:50. Starting checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:50. Finished checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:50.     Transaction log: C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\backup\generation10\logs\110401AB.log...
I. 04/01 09:32:50. Starting checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:51. Finished checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:51. Starting checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:51. Finished checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:51.     Transaction log: C:\projects\$SA_templates\run\dbbackup\demo_restore_incremental_backup12\backup\generation10\logs\110401AC.log...
I. 04/01 09:32:51. Starting checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:52. Finished checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:52.     Checkpointing...
I. 04/01 09:32:52. Starting checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:52. Finished checkpoint of "ddd12" (ddd12.db) at Fri Apr 01 2011 09:32
I. 04/01 09:32:52. Recovery complete
I. 04/01 09:32:52. Database server shutdown automatically after log applied
I. 04/01 09:32:52. Database server stopped at Fri Apr 01 2011 09:32
Fri 04/01/2011 
09:32 AM

Command file 14_run_dbspawn_dbsrv12.bat starts the freshly-restored database:
CALL 03_run_dbspawn_dbsrv12.bat

Command file 15_run_script.bat displays all the data in the database:
"%SQLANY12%\bin32\dbisql.com"^
-c "ENG=ddd12;DBN=ddd12;UID=dba;PWD=sql"^
READ ENCODING Cp1252 15s_script_to_display_data.sql

Here is the script file 15s_script_to_display_data.sql:
SELECT 't1', * FROM t1
UNION ALL
SELECT 't2', * FROM t2 
ORDER BY 1, 2;

The output shows that both tables and all 5 rows have been successfully restored:
't1' pkey        data
----------------------------
t1   1           1
t1   2           2
t1   3           3
t2   11          11
t2   22          22

(5 rows)

Execution time: 0.02 seconds

Command file 16_stop_drop_everything.bat can be used to clean up so the demonstration can be started over again from scratch:
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

1 comment:

Bill said...

This has to be the GREATEST post of all time! You have laid it out so clearly for all of us to follow. Thanks!