Personally, I think the SQL EVENT is a wonderful thing... BUT, I still prefer running dbbackup from command (batch) files that are scheduled via cron (for Unix and Linux) or Scheduled Tasks (for Windows), instead of executing BACKUP commands from inside scheduled database events.
My preference still stands today, even though Version 11 of SQL Anywhere introduced "Maintenance Plans" in Sybase Central to make it easier for you to create SQL Anywhere events that perform database backups and validations.
Here's why I prefer batch files over events, for database backups:
- It's easier to write a batch file that records its progress in a text file.
- It's easier to make an ad-hoc run of a batch file... just doubleclick on the file name.
- It's easier to write batch file commands that copy, rename and delete files.
- It's just as easy to create a schedule via Windows Scheduled Tasks as it is via Sybase Central.
- Batch files can be administered without database access or privileges.
- More people are familiar with operating system commands than database operations.
The actual batch file is listed at the end of this blog posting; here are the contents of the document called "Backup a SQL Anywhere Database Keeping Three Generations of Backups":
1. Pick a folder to contain the command file and the backup subfolders.
Put the backup command file in that folder.
For example: C:\dbbackup\run_dbbackup_full.bat
2. Edit the backup command file.
Change the connection string so that dbbackup can connect to the consolidated database:
Note: The ^ character is the line continuation character for Windows command files.
"%ASANY9%\win32\dbbackup.exe"^
-c "ENG=ddd;DBN=ddd;UID=dba;PWD=sql"^
-o bkup\dbbackup_log.txt -x -y bkup\generation3
3. Run the backup for the first time.
Here's what will happen:
- The bkup subfolder will be created; e.g., C:\dbbackup\bkup
- The diagnostic text file will be started; e.g., C:\dbbackup\bkup\dbbackup_log.txt
- The "most recent backup" subfolder will be created: e.g., C:\dbbackup\bkup \generation3
- Backup copies of the database and transaction log will be written to the generation3 subfolder
- The original transaction log will deleted and recreated.
*****************************************************************************************
Full dbbackup started
20/04/2007
10:04 AM
Adaptive Server Anywhere Backup Utility Version 9.0.2.3456
(599 of 599 pages, 100% complete)
(49 of 49 pages, 100% complete)
Transaction log renamed to: 070420AA.LOG
Database backup completed
20/04/2007
10:04 AM
Volume in drive C has no label.
Volume Serial Number is 5E13-A7B2
Directory of C:\projects\$SA_templates\run\dbbackup
\demo_full_backup9\bkup
20/04/2007 10:04 AM <DIR> .
20/04/2007 10:04 AM <DIR> ..
20/04/2007 10:04 AM 371 dbbackup_log.txt
20/04/2007 10:04 AM <DIR> generation3
1 File(s) 371 bytes
Directory of C:\projects\$SA_templates\run\dbbackup
\demo_full_backup9\bkup\generation3
20/04/2007 10:04 AM <DIR> .
20/04/2007 10:04 AM <DIR> ..
20/04/2007 10:04 AM 2,453,504 ddd.db
20/04/2007 10:04 AM 200,704 ddd.log
2 File(s) 2,654,208 bytes
Total Files Listed:
3 File(s) 2,654,579 bytes
5 Dir(s) 6,716,006,400 bytes free
Full dbbackup OK
20/04/2007
10:04 AM
4. Run the backup for the second time.
Here's what will happen:
- More messages will be written to the diagnostic text; e.g., C:\dbbackup\bkup\dbbackup_log.txt
- The previous "generation3" subfolder will be renamed: e.g., C:\dbbackup\bkup \generation2
- The "most recent backup" subfolder will be created: e.g., C:\dbbackup\bkup \generation3
- Backup copies of the database and transaction log will be written to the generation3 subfolder
- The original transaction log will deleted and recreated.
*****************************************************************************************
Full dbbackup started
20/04/2007
10:35 AM
Adaptive Server Anywhere Backup Utility Version 9.0.2.3456
(599 of 599 pages, 100% complete)
(2 of 2 pages, 100% complete)
Transaction log renamed to: 070420AB.LOG
Database backup completed
20/04/2007
10:35 AM
Volume in drive C has no label.
Volume Serial Number is 5E13-A7B2
Directory of C:\projects\$SA_templates\run\dbbackup
\demo_full_backup9\bkup
20/04/2007 10:35 AM <DIR> .
20/04/2007 10:35 AM <DIR> ..
20/04/2007 10:35 AM 1,611 dbbackup_log.txt
20/04/2007 10:04 AM <DIR> generation2
20/04/2007 10:35 AM <DIR> generation3
1 File(s) 1,611 bytes
Directory of C:\projects\$SA_templates\run\dbbackup
\demo_full_backup9\bkup\generation2
20/04/2007 10:04 AM <DIR> .
20/04/2007 10:04 AM <DIR> ..
20/04/2007 10:04 AM 2,453,504 ddd.db
20/04/2007 10:04 AM 200,704 ddd.log
2 File(s) 2,654,208 bytes
Directory of C:\projects\$SA_templates\run\dbbackup
\demo_full_backup9\bkup\generation3
20/04/2007 10:35 AM <DIR> .
20/04/2007 10:35 AM <DIR> ..
20/04/2007 10:04 AM 2,453,504 ddd.db
20/04/2007 10:35 AM 8,192 ddd.log
2 File(s) 2,461,696 bytes
Total Files Listed:
5 File(s) 5,117,515 bytes
8 Dir(s) 6,712,197,120 bytes free
Full dbbackup OK
20/04/2007
10:35 AM
5. Run the backup for the third time.
Here's what will happen:
- More messages will be written to the diagnostic text; e.g., C:\dbbackup\bkup\dbbackup_log.txt
- The previous "generation2" subfolder will be renamed: e.g., C:\dbbackup\bkup \generation1
- The previous "generation3" subfolder will be renamed: e.g., C:\dbbackup\bkup \generation2
- The "most recent backup" subfolder will be created: e.g., C:\dbbackup\bkup \generation3
- Backup copies of the database and transaction log will be written to the generation3 subfolder
- The original transaction log will deleted and recreated.
*****************************************************************************************
Full dbbackup started
20/04/2007
10:37 AM
Adaptive Server Anywhere Backup Utility Version 9.0.2.3456
(599 of 599 pages, 100% complete)
(2 of 2 pages, 100% complete)
Transaction log renamed to: 070420AC.LOG
Database backup completed
20/04/2007
10:37 AM
Volume in drive C has no label.
Volume Serial Number is 5E13-A7B2
Directory of C:\projects\$SA_templates\run\dbbackup
\demo_full_backup9\bkup
20/04/2007 10:37 AM <DIR> .
20/04/2007 10:37 AM <DIR> ..
20/04/2007 10:37 AM 3,223 dbbackup_log.txt
20/04/2007 10:04 AM <DIR> generation1
20/04/2007 10:35 AM <DIR> generation2
20/04/2007 10:37 AM <DIR> generation3
1 File(s) 3,223 bytes
Directory of C:\projects\$SA_templates\run\dbbackup
\demo_full_backup9\bkup\generation1
20/04/2007 10:04 AM <DIR> .
20/04/2007 10:04 AM <DIR> ..
20/04/2007 10:04 AM 2,453,504 ddd.db
20/04/2007 10:04 AM 200,704 ddd.log
2 File(s) 2,654,208 bytes
Directory of C:\projects\$SA_templates\run\dbbackup
\demo_full_backup9\bkup\generation2
20/04/2007 10:35 AM <DIR> .
20/04/2007 10:35 AM <DIR> ..
20/04/2007 10:04 AM 2,453,504 ddd.db
20/04/2007 10:35 AM 8,192 ddd.log
2 File(s) 2,461,696 bytes
Directory of C:\projects\$SA_templates\run\dbbackup
\demo_full_backup9\bkup\generation3
20/04/2007 10:37 AM <DIR> .
20/04/2007 10:37 AM <DIR> ..
20/04/2007 10:04 AM 2,453,504 ddd.db
20/04/2007 10:37 AM 8,192 ddd.log
2 File(s) 2,461,696 bytes
Total Files Listed:
7 File(s) 7,580,823 bytes
11 Dir(s) 6,709,657,600 bytes free
Full dbbackup OK
20/04/2007
10:37 AM
6. Set up the command file to run repeatedly.
Use Start - All Programs - Accessories - System Tools - Scheduled Tasks - Add Scheduled Task
Browse to pick the command file run_dbbackup_full.bat
Pick: Daily, Start Time: 1:00 AM, Every Day
Here's what will happen when the scheduled task runs:
- More messages will be written to the diagnostic text; e.g., C:\dbbackup\bkup\dbbackup_log.txt
- The previous "generation1" subfolder and its contents will be deleted
- The previous "generation2" subfolder will be renamed: e.g., C:\dbbackup\bkup \generation1
- The previous "generation3" subfolder will be renamed: e.g., C:\dbbackup\bkup \generation2
- The "most recent backup" subfolder will be created: e.g., C:\dbbackup\bkup \generation3
- Backup copies of the database and transaction log will be written to the generation3 subfolder
- The original transaction log will deleted and recreated.
Here's the actual batch file run_dbbackup_full.bat...
Note: A bug has been fixed, and a new version of the batch file has been posted here: Rotating Database Backups Revisited.
Note: The ^ character is the line continuation character for Windows command files.
REM -c ... database connection string
REM -b ... maximum block size in pages
REM -d backup database file only, not the transaction log.
REM -k ... checkpoint log option: copy, nocopy, recover,
REM auto (default)
REM -l live backup
REM -n rename the backup log file to YYMMDDXX.LOG
REM -o ... where to put dbbackup console messages
REM -r rename current log to YYMMDDXX.LOG, start a new one
REM Note: It is safe to delete old YYMMDDXX.LOG files.
REM -s use internal BACKUP statement to create the backup
REM Note: With -s the -o filespec is relative to
REM the server, no progress messages or prompts
REM are displayed, and the server must have
REM permission to write the output.
REM -t backup transaction log only, not the database file.
REM -x delete the current log and start a new one
REM -y create output folder and overwrite file(s)
REM without confirmation
REM bkup\generation3 target folder for latest backup log file
MD bkup
ECHO ********************************************^
*********************************************^
>>bkup\dbbackup_log.txt
ECHO Full dbbackup started >>bkup\dbbackup_log.txt
DATE /T >>bkup\dbbackup_log.txt
TIME /T >>bkup\dbbackup_log.txt
CD bkup
RD /S /Q generation1
RENAME generation2 generation1
RENAME generation3 generation2
MD generation3
CD ..
"%ASANY9%\win32\dbbackup.exe"^
-c "ENG=ddd;DBN=ddd;UID=dba;PWD=sql"^
-o bkup\dbbackup_log.txt -x -y bkup\generation3
IF ERRORLEVEL 1 GOTO ERR_BACKUP
REM Full dbbackup OK
DATE /T >>bkup\dbbackup_log.txt
TIME /T >>bkup\dbbackup_log.txt
DIR /S bkup\*.* >>bkup\dbbackup_log.txt
ECHO Full dbbackup OK >>bkup\dbbackup_log.txt
DATE /T >>bkup\dbbackup_log.txt
TIME /T >>bkup\dbbackup_log.txt
GOTO END
:ERR_BACKUP
REM Error: Full dbbackup failed
DATE /T >>bkup\dbbackup_log.txt
TIME /T >>bkup\dbbackup_log.txt
DIR /S bkup\*.* >>bkup\dbbackup_log.txt
ECHO Error: Full dbbackup failed >>bkup\dbbackup_log.txt
GOTO END
:END
3 comments:
COOL! I have always prided myself on good backup procedures (I have gained number of clients over the years when they first had to pay me to get them out of a major jam because they didn't keep backups :-) ). But, as always, you take things a step further to add elegance.
Bill
This is one of the best solution that i have got take backup of sqlanywhere database
Thanks for presenting this kind of Information.Your blog is giving outstanding performance.This is one of the knowledgeable post.thanks for sharing with us.Good post.Android app developers
Post a Comment