SQL Backup failed with exit code: 760

bsantosh6 · March 21, 2012 11:10AM

Hello,

--my backup files look ok (based on date modified and based on the jobs step output file )

--SQL BAckup however thows error 760 causing the job to reflect failed state.IN the step output file everything seems to success except the last line which says "Msg 50000, Sev 16: SQL Backup failed with exit code: 760 SQL error code: 0 [SQLSTATE 42000]"

--the tlog backups also fail with this error sometimes.(again,,,backup files look ok though)

--I ran this (that petey has given out in some other forum) :
SELECT a.backup_start_date, a.database_name, a.first_lsn, b.*
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
INNER JOIN master..sysdatabases c ON a.database_name COLLATE DATABASE_DEFAULT = c.name COLLATE DATABASE_DEFAULT
WHERE b.physical_device_name LIKE 'SQLBACKUP_%'

And it returns 39 rows....with backup start date showing dates beteen 2-4 years ago ( nothing recent)

---FYI..this is sql 2000 and SQL Backup v5.3.0.178

Any help would be appreciated!

thanks in advance!

petey · March 22, 2012 4:18AM

msdb..backupset stores details of your backup sets. It should contain details of all your recent backups, not just those from 2 - 4 years ago. Is the drive where the msdb database is located running out of space? Could you please check the SQL Server error log if there are any entries related to SQL Server failing to update the backup history tables?

If you are out of space, you can remove old backup history entries using the sp_delete_backup_history stored procedure to free up some space e.g. the following deletes all entries made before 1-Jan-2012:

msdb..sp_delete_backuphistory '1-Jan-2012'

bsantosh6 · March 22, 2012 11:44AM

Firstly,Thanks for your response.I really appreciate it!

-.I checked the msdb file usage.its set to auto grow at 1 MB but unrestricted.So do you think the possible issue is that the msdb's datafiles tries to autogrow and sql_backup times out?any way to confirm this?

I have gone ahead an allocated an extra quater gig to the datafile.if this is indeed the issue...then we should not see these intermiitent failures...

(fingers crossed)

-running a msdb..sp_delete_backuphistory '1-Jan-2012' right away is not a feasible option for me now.Because a regular purge progess wasnt done and the msdb has grown to about 2GB(history tables having more than 3 mil rows! I have read in posts by MVPs that deleting this would have a huge performance impact on the system....So I ll keep this as the last option for now ( i will implement it during scheduled downtime though)

petey · March 22, 2012 12:23PM

So do you think the possible issue is that the msdb's datafiles tries to autogrow and sql_backup times out?any way to confirm this?

It's not SQL Backup that inserts data into the msdb tables, it's SQL Server itself. SQL Backup then tries to retrieve details of the backup from those tables, but it's returning empty. SQL Server would usually raise a warning when it cannot access the msdb tables. Were there any such warnings recorded in the SQL Backup log for those backup processes that raised error 760?

sp_delete_backuphistory is indeed slow on SQL 2000. Some options here. There's an alternative stored procedure you could use, or add a temporary index to the media_set_id column in the backupset table prior to running sp_delete_backuphistory.

bsantosh6 · March 22, 2012 1:42PM

-nope.no errors in sql logs.nothing about msdb full or msdb being inaccessible.

--thanks for the link that you sent.looks v interesting.i ll use that to set up a purge process asap.

Most imp question:

---as i said before...i see all my Dbs were backed up regardless of the 760 error...how does the 760 error affect me? Will it not allow me to restore the full backups + log backups?

bsantosh6 · March 22, 2012 5:07PM

an update:

1) We backup about 400 DBs in one job ( sequentially).Used the sql backup logs to identify the individual db on which it was generating the 760 error.

2)ran 'restore sqbheaderonly" on the .sqb :

results:

Reading SQB file header of "E:\MSSQL\Backups\troubleshooting_760_error\PB_0605_3455\FULL_(local)_PB_0605_3455_20120323_024035.sqb"
First LSN :
Last LSN :
Checkpoint LSN :
Differential base LSN :

LSN info empty!!!

3)converted the .sqb file to .bak file using the "Convert " option

4)ran std sql server restore header only on the .bak

Voila!!!

First LSN : 40905000000006000001

Last LSN : 40905000000005800001

Checkpoint LSN : 40905000000005800002

Differential base LSN :40905000000004900002

5)restored the DB on test env for the suspect DB

(full+tlogs)

(using both sqb and bak files)

all ok!

So...the issue is most likely with sql backup?????

petey · March 24, 2012 7:18AM

bsantosh6 wrote:

how does the 760 error affect me? Will it not allow me to restore the full backups + log backups?

In each SQL Backup-created backup file, there is a header block that stores LSN details of the backup set. SQL Backup uses this information when you want to restore a sequence of transaction logs i.e. SQL Backup sorts the backup files in ascending LSN sequence, and restores each backup sequentially.

If this information is missing, SQL Backup would not be able to sort the files and would have problems restoring the transaction log files in the right order. You might then have to restore some backup files manually. Individually, each backup file can still be restored i.e. the missing LSN information does not affect the restorability of each backup file.

bsantosh6 · March 27, 2012 9:47AM

-Firstly,thank you! thats good news.I am somwhat relieved

-I agree my backup history is huge..but thts mostly not the issue . I say this because the DBs on which the failure occur are not always in the Results of :

SELECT a.backup_start_date, a.database_name, a.first_lsn, b.*
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
INNER JOIN master..sysdatabases c ON a.database_name COLLATE DATABASE_DEFAULT = c.name COLLATE DATABASE_DEFAULT
WHERE b.physical_device_name LIKE 'SQLBACKUP_%'

--How do we fix this? Does this require a SQL Backup patch.It Looks like the issue is with my SQL backup.
This is my Sql backup ver : 5.3.0.178

petey · April 18, 2012 12:33PM

Could you please check in the SQL Server error log if any errors were recorded at the time SQL Backup reported error 760?

bsantosh6 · April 18, 2012 1:40PM

1)
Nope.No errors in the SQL log around that time.If fact I see this msg....

Database backed up: Database: xxxxxx, creation date(time): 2008/08/19(18:20:21), pages dumped: 1904, first LSN: 36692:212:1, last LSN: 36692:214:1, number of dump devices:
1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_E14F32C7-1F7E-4618-8F80-63F9A8EBCB05'}).

....which negates the 760 error(which indicated log info could not be retrieved).

2) This is what i see in the redgate logs:

ERRORS AND WARNINGS

4/18/2012 2:44:10 AM: Backing up xxxxxxxx (full database) to:
4/18/2012 2:44:10 AM: R:\yyyyyyyyy\xxxxxxxxx_20120418_024410.sqb

4/18/2012 2:44:10 AM: Deleting old backup file: R:\yyyyyyyy\xxxxxxxxx_20120417_023755.sqb
4/18/2012 2:44:10 AM: BACKUP DATABASE [xxxxxxx] TO
VIRTUAL_DEVICE = 'SQLBACKUP_E14F32C7-1F7E-4618-8F80-63F9A8EBCB05' WITH BUFFERCOUNT = 6, BLOCKSIZE =
65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (xxxxxxxxx), 4/18/2012 2:44:10 AM',
DESCRIPTION = N'Backup on 4/18/2012 2:44:10 AM Server: xxxxxxxx Database: xxxxxxxxx'

4/18/2012 2:44:11 AM: Database size : 267.563 MB
4/18/2012 2:44:11 AM: Compressed data size: 2.816 MB
4/18/2012 2:44:11 AM: Compression rate : 98.95%

Processed 1816 pages for database 'xxxxxxx', file 'PB_0605_0001' on file 1.
Processed 1 pages for database 'xxxxxxxx', file 'PB_0605_0001_log' on file 1.
BACKUP DATABASE successfully processed 1817 pages in 0.134 seconds (111.027 MB/sec).
4/18/2012 2:44:20 AM: Error 742: Failed to get LSN data from server (0 rows returned). ()
4/18/2012 2:44:20 AM: Error 760: LSN data from server is blank.

3) I dunno if this help but:

-EXECUTE master..sqlbackup N' -SQL " RESTORE HeaderONLY.......

Works perfectly.Returns all LSN info for the db on which the failure occurred.

- EXECUTE master..sqlbackup N' -SQL " RESTORE SQBHeaderONLY.......

Returns all LSN as empty for the db on which the failure occurred.

petey · April 18, 2012 9:57PM

Could you please run the following:

DBCC TRACEON (3004, 3605, -1)

This will cause SQL Server to log details of the backup process to the SQL Server error log. When you next run a backup that raises error 760, could you then post the corresponding entries from the SQL Server error log for that backup?

Thanks.

bsantosh6 · April 24, 2012 9:31AM

Per your suggetion,I did the TraceOn. I ve pasted below the data i see in the sql logs and red gate logs during the event of a failure:

Redgate logs:

ERRORS AND WARNINGS

4/24/2012 2:18:09 AM: Backing up PB_0605_3438 (full database) to:
4/24/2012 2:18:09 AM: R:\MSSQL\Backups\FULL_BKUP\PB_0605_3438\FULL_(local)_PB_0605_3438_20120424_021809.sqb

4/24/2012 2:18:09 AM: Deleting old backup file: R:\MSSQL\Backups\FULL_BKUP\PB_0605_3438\FULL_(local)_PB_0605_3438_20120423_021437.sqb
4/24/2012 2:18:09 AM: BACKUP DATABASE [PB_0605_3438] TO VIRTUAL_DEVICE = 'SQLBACKUP_CDDEB33A-EFC3-48B1-AE33-17F97F044EEC' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (PB_0605_3438), 4/24/2012 2:18:09 AM', DESCRIPTION = N'Backup on 4/24/2012 2:18:09 AM Server: CLTEBIZBPSQLCL Database: PB_0605_3438'

4/24/2012 2:18:12 AM: Database size : 375.313 MB
4/24/2012 2:18:12 AM: Compressed data size: 45.069 MB
4/24/2012 2:18:12 AM: Compression rate : 87.99%

Processed 22240 pages for database 'PB_0605_3438', file 'PB_0605_0001' on file 1.
Processed 1 pages for database 'PB_0605_3438', file 'PB_0605_0001_log' on file 1.
BACKUP DATABASE successfully processed 22241 pages in 2.359 seconds (77.232 MB/sec).
4/24/2012 2:18:21 AM: Error 742: Failed to get LSN data from server (0 rows returned). ()
4/24/2012 2:18:21 AM: Error 760: LSN data from server is blank.

SQL Logs ( with traceON) :

2012-04-24 02:18:09.22 spid4240 BackupDatabase: Database PB_0605_3438
2012-04-24 02:18:09.22 spid4240 BackupDatabase: Streams open
2012-04-24 02:18:09.47 spid4240 BackupDatabase: Checkpoint done
2012-04-24 02:18:09.47 spid4240 BackupDatabase: Work estimates done
2012-04-24 02:18:09.47 spid4240 BackupDatabase: Configuration section done
2012-04-24 02:18:11.83 spid4240 BackupDatabase: Database files done
2012-04-24 02:18:11.83 spid4240 BackupDatabase: Log files done
2012-04-24 02:18:11.83 spid4240 BackupDatabase: Final configuration done
2012-04-24 02:18:11.83 spid4240 BackupDatabase: MBC done
2012-04-24 02:18:11.86 spid4240 BackupDatabase: Writing history records
2012-04-24 02:18:11.86 backup Database backed up: Database: PB_0605_3438, creation date(time): 2008/08/07(01:48:33),
pages dumped: 22336, first LSN: 52900:52:1, last LSN: 52900:54:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'SQLBACKUP_CDDEB33A-EFC3-48B1-AE33-17F97F044EEC'}).

2012-04-24 02:18:11.89 spid4240 BackupDatabase: Finished

petey · April 25, 2012 10:30PM

What is the result when you run the following in Query Analyzer:

SELECT TOP 1 a.type, a.backup_set_uuid, a.first_lsn, a.last_lsn, a.checkpoint_lsn,
	a.database_backup_lsn, a.media_set_id, c.name 
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
INNER JOIN master..sysdatabases c ON a.database_name = c.name
WHERE b.physical_device_name = 'SQLBACKUP_CDDEB33A-EFC3-48B1-AE33-17F97F044EEC'
ORDER BY a.media_set_id DESC

bsantosh6 · April 26, 2012 9:54AM

SELECT TOP 1 a.type, a.backup_set_uuid, a.first_lsn, a.last_lsn, a.checkpoint_lsn,
a.database_backup_lsn, a.media_set_id, c.name
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
INNER JOIN master..sysdatabases c ON a.database_name = c.name
WHERE b.physical_device_name = 'SQLBACKUP_CDDEB33A-EFC3-48B1-AE33-17F97F044EEC'
ORDER BY a.media_set_id DESC

RESULT:

NULL

petey · April 26, 2012 11:21AM

If you have not deleted the backup history records for the 24th, are there any entries in the msdb..backupset table for that backup? Try

SELECT a.backup_start_date, a.backup_finish_date,  b.logical_device_name, b.physical_device_name
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
FROM msdb..backupset a 
WHERE a.database_name = 'PB_0605_3438'
  AND a.type = 'D'
  AND a.backup_start_date &gt;= '24-Apr-2012 02:00' AND a.backup_finish_date &lt;= '24-Apr-2012 03:00'
ORDER BY a.backup_start_date

bsantosh6 · April 26, 2012 5:23PM

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 · April 26, 2012 5:24PM

the service account running sqlbackup is sysadmin btw

petey · April 29, 2012 12:02PM

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.

SQL Backup failed with exit code: 760

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk