SQL Backup failed with exit code: 760

[bsantosh6]

Some Progress!

I ran the the script you provided and it retuned the result :

backup_start_date ,backup_finish_date ,logical_device_name ,physical_device_name
2012-04-24 02:18:09.000 ,2012-04-24 02:18:11.000 ,NULL, R:\ShippedLog\3184\PB_0605_3184_tlog_201011301900.TRN

This is pointing to a wrong physical_device_name!

Some more research and querying and I see that when the job runs as 'sa' it has no issues.But when it runs as the "service account that SQLBACKUP service runs under" ..it fails...!
I went back almost 3 months in backup history and saw this pattern.

I used this query to confirm the pattern:

SELECT user_name,database_name,server_name,logical_device_name,physical_device_name,device_type
FROM msdb..backupset a INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE user_name like '%SVC%'
AND a.type = 'D'
AND a.backup_start_date >= '01-Jan-2012 01:00' AND a.backup_finish_date <= '25-Apr-2012 04:00'
ORDER BY a.backup_start_date

--results show all my failures and in all these rows the physical_device_name is wrong....redgate log confirm these exact failures where the 760 error was thrown...



On Running this:

SELECT user_name,database_name,server_name,logical_device_name,physical_device_name,device_type
FROM msdb..backupset a INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE user_name like 'sa'
AND a.type = 'D'
AND a.backup_start_date >= '01-Jan-2012 01:00' AND a.backup_finish_date <= '25-Apr-2012 04:00'
ORDER BY a.backup_start_date


--all good backups and correct physical_Device_name!

[bsantosh6]

the service account running sqlbackup is sysadmin btw

[petey]

There is something wrong with how SQL Server is recording the backup history data.

When a backup is performed using SQL Backup, the physical_device_name that is stored in the backupmediafamily table is the same name stored in the SQL Server error log for that backup. In the backupset table, the user_name value stores the name of the user that performed this backup. In SQL Backup's case, this is the SQL Backup Agent service startup account. All this is performed by SQL Server, not SQL Backup.

When the backup completes, SQL Backup then updates the entry in the backupmediafamily table so that the logical_device_name stores details of the backup, and physical_device_name stores the actual SQL Backup file name. At the same time, the user_name column is updated to store the user that started the backup via SQL Backup.

In your case, SQL Backup could not find the entry to update, because the entry that represented the backup is storing a strange value in the physical_device_name column. The value 'R:\ShippedLog\3184\PB_0605_3184_tlog_201011301900.TRN' was surely not the SQL Backup backup file name for that backup. It actually looks like a backup file created on 30 November 2010.

As for the other records that are not showing the correct value, again it was because SQL Backup could not find the right entries to update. If you look at the physical_device_name values for those entries, do they make any sense to you? Also, the user_name values were never updated, hence they still store the SQL Backup Agent service startup account name.

If you look in the backupmediafamily table, are there any other entries that has the value 'R:\ShippedLog\3184\PB_0605_3184_tlog_201011301900.TRN' in the physical_device_name column? Maybe you could try clearing out all the old entries if you don't need them, using the sp_delete_backuphistory stored procedure in the msdb database, to see if it helps.
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7