Red Gate forums :: View topic - SQL Backup failed with exit code: 760
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 6
SQL Backup 6 forum

SQL Backup failed with exit code: 760

Search in SQL Backup 6 forum
Post new topic   Reply to topic
Jump to:  
Go to page Previous  1, 2
Author Message
bsantosh6



Joined: 21 Mar 2012
Posts: 10

PostPosted: Thu Apr 26, 2012 10:23 pm    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
bsantosh6



Joined: 21 Mar 2012
Posts: 10

PostPosted: Thu Apr 26, 2012 10:24 pm    Post subject: Reply with quote

the service account running sqlbackup is sysadmin btw
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2277

PostPosted: Sun Apr 29, 2012 5:02 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page Previous  1, 2
Page 2 of 2

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group