Rotating Database Backups Revisited

When two people say they like an article, like they have for Rotating Database Backups, it's a banner day for this blog!

So, it behooves me to report a bug: If the database isn't running but the batch file keeps on getting launched, it will carry on rotating folders! The oldest backup folder is deleted, the other folders are renamed... and because the database isn't running the dbbackup utility doesn't write anything to the newest backup folder.

Eventually (if the database stays offline), you end up with no backups at all!

Rotate This, You %@&^@?!

The fix involves waiting until after the backup finishes successfully before renaming and deleting folders. The dbbackup utility writes the backup files to a folder with a temporary name, and nothing else is touched if dbbackup fails.

A couple of other changes have been made as well:

• SQL Anywhere Version 11 is being used instead of Version 9, and

• 10 backup generations are kept instead of just three, showing how easy it is to change the number of generations.

Why 10 generations? Because the code presented here came from a real client who wanted full backups every hour starting at 9AM and ending at 6PM. Nightly Norton Ghost runs take care of making multi-generational copies of the entire disk so the history of backups crosses day boundaries.

(... plus, every single insert, update and delete is logged at the table level (in addition to the transaction log level) using the technique described in Revisited: Triggering an Audit Trail... but that's drifting off topic.)

Much of the original article Rotating Database Backups still applies, especially where it talks about setting up a Windows scheduled task, and why I prefer batch files over CREATE EVENT for making backups.

Also, the first ECHO command has been fixed from this

ECHO ********************************************^
*********************************************^
>>bkup\dbbackup_log.txt

to this

ECHO ********************************************^
*********************************************>>bkup\dbbackup_log.txt

The original syntax caused the first ">" character to be ignored, which in turn caused the ECHO command to overwrite the file instead of appending.

Even the extended demo is the same, except you have to run it 11 times before you see a folder deleted. I am not going to repeat the demo here; except for being a lot longer, the diagnostic text file dbbackup_log.txt looks the same.

---

Here's the updated code for run_dbbackup_full.bat...

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 the database file only, not the transaction log.
REM -k ...  checkpoint log option: copy, nocopy, recover, 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 the current log to YYMMDDXX.LOG and start a new one
REM           Note: It is safe to delete the old YYMMDDXX.LOG files.
REM -s      use an internal BACKUP statement to create the backup
REM           Note: With -s the -o filespec is relative to the server,
REM                 no progress messages or prompts are displayed, and
REM                 the server must have permission to write the output.
REM -t      backup the 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) without confirmation
REM bkup\generation_temp  temporary folder name for latest backup

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

REM Backup to empty subfolder
CD bkup
RD /S /Q generation_temp
MD generation_temp
CD ..
"%SQLANY11%\bin32\dbbackup.exe"^
   -c "ENG=ddd11;DBN=ddd11;UID=dba;PWD=sql"^
   -o bkup\dbbackup_log.txt -x -y bkup\generation_temp
IF ERRORLEVEL 1 GOTO ERR_BACKUP

REM Backup OK
CD bkup
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 ..
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 Backup 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

---

Here's what the dbbackup_log.txt file contains when a backup doesn't work because the database isn't running:

***************************************************************************************** 
Full dbbackup started 
21/03/2009 
05:07 AM
SQL Anywhere Backup Utility Version 11.0.1.2044
No database file specified
21/03/2009 
05:07 AM
  ... 
Error: Full dbbackup failed