Mirrored backup failure

Powerway

We are doing mirrored full, diff, and log backups. We had the mirror server down for maintenance and when the log backup was done on the primary server, the job failed and it didn't even create the log backup file on the primary.

The job reads as FAILED and shows

Failed to create backup folder: \\alpha-db2\backups

There is no file on the local drive on the primary server where the backups was done.

The way the help reads, it leads me to believe that the log backup should of been created for the primary server even though the mirror server was down.

This is not what we are seeing. We are using SQL Backup 5.1, 2 threads, the mirror location is through a windows share.

Jason Cook

Hi,
I've looked for the section in the help you are referring to, and it does seem to be slightly ambiguous:

"During the backup process, if any of the files cannot be written a warning is raised. However, the backup process continues as long as at least one specified backup file can be written. If none of the files can be written, an error is raised and the backup process is stopped."

What this should mean, is that once the actual backing up phase has begun, a subset of the files can fail and the rest should continue. However, if a pre-backup phase failure occurs (for example, the missing directory), the backup task will error out before the backing up phase begins.

Depending on your needs, the COPYTO keyword may be able to help. The difference with COPYTO is that the primary server's backup is done first, and *then* copied to the secondary server. If for any reason the secondary server is unavailable, the backup will succeed with a warning, rather than fail.

The main downside to the COPYTO is that you only have one copy of the file available until the COPYTO completes, compared to 2 or more when the original backup completes.

I'll also pass this on to our developers and they may be able to improve the situation in a future release.

Hope that helps,
Jason

Powerway

Thanks for the better explaination. I think a better implementation would be if the mirror is not available, still go ahead and re-execute the backup to the primary location.

Jason Cook wrote:

Hi,
I've looked for the section in the help you are referring to, and it does seem to be slightly ambiguous:

"During the backup process, if any of the files cannot be written a warning is raised. However, the backup process continues as long as at least one specified backup file can be written. If none of the files can be written, an error is raised and the backup process is stopped."

What this should mean, is that once the actual backing up phase has begun, a subset of the files can fail and the rest should continue. However, if a pre-backup phase failure occurs (for example, the missing directory), the backup task will error out before the backing up phase begins.

Depending on your needs, the COPYTO keyword may be able to help. The difference with COPYTO is that the primary server's backup is done first, and *then* copied to the secondary server. If for any reason the secondary server is unavailable, the backup will succeed with a warning, rather than fail.

The main downside to the COPYTO is that you only have one copy of the file available until the COPYTO completes, compared to 2 or more when the original backup completes.

I'll also pass this on to our developers and they may be able to improve the situation in a future release.

Hope that helps,
Jason

MichaelH

I have tested this with several different accounts, as a scheduled job or interactive and constanlty get a:

Warning 145: COPYTO error: Failed to create folder: \\testsrv\backupSQL Backup exit code: 145

When I tried to do the mirror backup I get error 500 with the same description
Failed to create backup folder: \\testsrv\backupSQL Backup exit code: 500


When I tried to backup to the network share directly I get this
Backing up master (full database) to:
Error 500: Failed to create backup folder : \\testsrv\backupError 505: No valid SQL Backup file names entered.
SQL Backup exit code: 505

I also am on 5.0.0.2770

Jason Cook

Hi,
The reason you will get that message is because SQL Backup is unable to access or create the location in question. For UNC locations, the share must already exist, and the SQL Backup Agent account requires sufficient permissions to access and write to it.


You can run the following command, which will check what permissions the SQL Backup Agent has to the folder...

execute master..sqbutility 999, 'RWE', '\\testsrv\backup'

If this is successful and the agent has read (R), write (W) and execute (E) permissions, it will return:

<SQBUTILITYRESULT>:1:

If there is a problem, the 1 will be replaced by a 0, and a message will be added at the end, for example:

<SQBUTILITYRESULT>:0:Folder does not exist : \\jason\temp2\

Hope that helps,
Jason