How to skip databases in Offline state in SQLBackupAndFTP

If you have database in the OFFLINE state and these databases are scheduled to be backed up, their backup will fail and the whole backup job will be shown as Failed. Same when you use Database Mirroring, you may have databases in Restoring state and they can’t be backed up either.  If you have such databases included in your backup job, you may want to skip them if they are in Offline or Restore state and show the whole job as Success, not Failure.

While we do not have the interface to exclude databases in a certain state from the backup job, it is easy to do it by editing the .jobx file directly. Just open .jobx file with a notepad and add the strings like below to show a Warning instead of the Failure (default) for any of the databases in the OFFLINE state:

<DbStatusActions>
<Item Status=”OFFLINE” Action=”Warning” />
</DbStatusActions>

Your .jobx file will look like this:SBF - DbStatusActions

In this example SQLBackupAndFTP will skip backup of this database with a Warning message.
The  supported statuses are: OFFLINE, RESTORING, RECOVERING, RECOVERY PENDING, SUSPECT, EMERGENCY.
The supported actions are : Warning, Backup, Error.

Please note, this feature works in SQLBackupAndFTP starting with version 10.0.31.

[Total: 2    Average: 5/5]

One thought on “How to skip databases in Offline state in SQLBackupAndFTP

  1. This is good info about how to handle a SQL database in OFFLINE state…..but can you please explain how to do the same thing if a file within a folder (a folder that has been included in the list of folders to b e backed up in that particular SQLBackupAndFTP job) is locked/in use at the time the backup job is running and therefore can’t be accessed to include in the back up?

    I’ve noticed that, in this situation of encountering one open file in the folder during the running of a backup job, the ENTIRE folder ends up not being backed up. Is there a way, similar to the one you mention above, to instruct the job that when an open/locked file within a folder is encountered during the running of a backup job, to simply SKIP that file and continue with the rest of the files within that folder so that all other files in that folder ARE included in the zip file, and the zip file (containing all files within the folder except the one that was locked/in use when the backup job was running) IS written to the backup destination? Thank you.

Leave a Reply

Your email address will not be published.