Intermittent failed backups with dbbackup utility

[Former Member]

Hello,

I have several Win2k8 Win2k12 servers running SQL Anywhere 12.0.1. I'm running the dbbackup utility on my servers to generate full backups to a share on a FreeNAS server every night.

About once a week, one of servers doesn't back up. dbbackup runs, but within 15 minutes or so it completes without generating emr.db or emr.log files, and doesn't truncate the log file.

The servers run this command, where U: is a drive mapped to a CIFS share on the FreeNAS server:

"C:\\Program Files\\SQL Anywhere 12\\Bin64\\dbbackup.exe" -o u:\\backup_dir\\backup_log.log -c "eng=databasename;userid=user;password=password" -x -y u:\\backup_dir\\latest\\dbbackup >> u:\\backup_dir\\backup_log.log 2>&1


Here is an example of what is logged, where 2/18 had a good backup and 2/19 did not:
DBBackuping Database Wed 02/18/2015  0:04:29.07
SQL Anywhere Backup Utility Version 12.0.1.3152
 (42557264 of estimated 42557264 pages, 100% complete)
Transaction log truncated
Database backup completed
DBBackup finished Wed 02/18/2015  1:49:18.57
Moving previous backups Thu 02/19/2015  0:00:05.63
DBBackuping Database Thu 02/19/2015  0:02:25.93
SQL Anywhere Backup Utility Version 12.0.1.3152
DBBackup finished Thu 02/19/2015  0:09:32.28


My database file sizes range between 60 GB and 500 GB.  The intermittent failed backups are happening both on brand new servers as well as on older servers.  The FreeNAS server isn't under any stress when this happens.  It also seems to happen whether I'm backing up to Windows server shares or FreeNAS CIFS shares. 
If I want to generate full backups every night to a FreeNAS server, is running the dbbackup utility on the server the best way to do this?

[Breck_Carter]

Were there any problems or messages in the dbsrv12 -o diagnostic log?

In the Windows event log?

Try adding some code to display %ERRORLEVEL% after the dbbackup command, then you can look it up here.

[Former Member]

The only thing logged in the dbsrv12 -o log is that it does a checkpoint immediately after the backup fails, but it also does a checkpoint when the backup is successful. Nothing is logged in the Windows event log either. The oddest thing about this is that nothing seems to be logged anywhere.

I added some code, I'm getting an exit code of 1 on the failed backups, and an exit code of 0 on the successful backups.

[VolkerBarth]

If I want to generate full backups every night to a FreeNAS server, is running the dbbackup utility on the server the best way to do this?

You could also do the backup locally (in case space is not an issue here) - possibly with "dbbackup -s" or even with an event within the database - and then move or copy the backup files to the NAS. That might be more reliable - although your current way should work, too, of course. Weird.

[MarkCulp]

Since you are logging everything to your U: drive (i.e. the NAS) then I would go on the premise that the U: drive is inaccessible at the time that the backup is running - e.g. perhaps a intermittent network issue. I would recommend that you try logging the -o output and the stdout/stderr output to a local drive and run with that for a week or two to determine if it shows any errors being reported.

[Former Member]

Yes, I unfortunately don't have enough space on the servers to back up locally.

I started logging to a local drive, and sure enough, error messages started appearing. dbbackup failed on 3 servers last night:

Server1 did not back up - backup directory empty, no .db or .log

DBBackuping Database Wed 03/25/2015 1:20:30.08 SQL Anywhere Backup Utility Version 12.0.1.3152 Database backup failed Error writing backup file "Bad file number" Exit Code is 1 DBBackup finished Wed 03/25/2015 1:55:41.00

Server2 did not back up - backup directory empty, no .db or .log

DBBackuping Database Wed 03/25/2015 0:04:36.03 SQL Anywhere Backup Utility Version 12.0.1.3152 Database backup failed Error writing backup file "Invalid argument" Exit Code is 1 DBBackup finished Wed 03/25/2015 1:02:48.80

Server3 did not backup - 0 size .log, no .db file

DBBackuping Database Wed 03/25/2015 0:01:04.85 SQL Anywhere Backup Utility Version 12.0.1.3152 Database backup failed Error writing backup file "Invalid argument" Attempted two active database requests Attempted two active database requests Exit Code is 1 DBBackup finished Wed 03/25/2015 1:03:16.34

I haven't recorded any network issues or NAS issues, what do these errors mean?

[VolkerBarth]

FWIW, 12.0.1.3152 is the original MR release of 12.0.1, IIRC, and that is very old, i.e. there has been a long list of EBFs with fixes and the like - I would recommend to apply a newer EBF... - Note: I don't claim that a newer EBF will solve your issue, it's just a general recommendation.

[jeff_albion]

Here is what these errors are indicating:

"Bad file number" is EBADF in errno.h coming back from the Windows OS - see: https://msdn.microsoft.com/en-us/library/5814770t.aspx

"Bad file number. There are two possible causes: 1) The specified file descriptor is not a valid value or does not refer to an open file. 2) An attempt was made to write to a file or device opened for read-only access."

"Invalid argument" is EINVAL in errno.h coming back from the Windows OS:

"Invalid argument. An invalid value was given for one of the arguments to a function. For example, the value given for the origin when positioning a file pointer (by means of a call to fseek) is before the beginning of the file."

"Attempted two active database requests" is SQLCODE -298 from the database server:

http://dcx.sap.com/index.html#sa160/en/saerrors/errm298.html

---

It sounds like you have some hardware / OS issues to work out for this backup location so you don't encounter the EBADF / EINVAL errors.

[Former Member]

OK.

I'm currently pushing backups from my SQL Anywhere servers to a NAS over a network filesystem.

Instead, is there a way for a different server to pull the backups from the SQL Anywhere server?

[VolkerBarth]

If the different server has SQL Anywhere client software installed, why not? You would run the dbbackup utility from that server and would supply a connection string that connects to the desired database.

Apparently then you would depend on the network connection between these two servers to creat reliable backups...