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.
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 :endLine 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.
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 :endLines 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 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
2 comments:
This has to be the GREATEST post of all time! You have laid it out so clearly for all of us to follow. Thanks!
Thannk you for sharing this
Post a Comment