Backup problem Cannot insert duplicate key in object dbo.backupset

isthgr · May 30, 2018, 9:10am

A few days ago i suddenly started having the following backup fail:

• 10:34:25 AM : Starting job "Backup Job - 1 " - “Full”. Computer “----------”. App v.“11.6.3”
• 10:34:25 AM : The backup folder “c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup” has “438,265GB” free space. The temporary folder “C:\Users\cert\AppData\Local\Temp\Pranas.NET\SQLBackupAndFTP\backup” has “438,265GB” free space
• 10:34:25 AM : Backing up “.\SQLEXPRESS” LocalSqlServer databases
• 10:34:26 AM : Getting maximum file size…
• 10:34:26 AM : The maximum file size: “”
• 10:34:26 AM : Backing up “certificates” database to .bak file. Backup type: “Full”. Checksum: “Disabled”
• 10:34:28 AM : ERROR: Failed to backup “--------” database with “Full” backup type: Violation of PRIMARY KEY constraint ‘PKbackupse21F79AAB0E391C95’. Cannot insert duplicate key in object ‘dbo.backupset’. The duplicate key value is (917). Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful. 10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed. Processed 1704 pages for database ‘--------’, file ‘-------’ on file 1. 100 percent processed. Processed 1 pages for database ‘------’, file ‘ISTH_CERT_log’ on file 1. BACKUP DATABASE successfully processed 1705 pages in 0.615 seconds (21.659 MB/sec). The statement has been terminated.
• 10:34:28 AM : Cleaning up backups of "Backup Job - 1 " job
• 10:34:28 AM : ERROR: Job "Backup Job - 1 " finished with “1” errors

Backing up manually from sql server works fine!

Alexander · May 31, 2018, 10:47am

Hi,

It seems this issue isn’t related to SQLBackupAndFTP. It can happen when one of the system databases is damaged. We can recommend you to check your system databases with the help of dbcc command: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-transact-sql?view=sql-server-2017

isthgr · May 31, 2018, 11:10am

I executed all the DBCC commands and no error was found.

As i mentioned earlier, i have no problem executing a manual backup from sqlserver.
Shouldn’t i get the same backup error with a manual backup as well?

Alexander · June 1, 2018, 3:29pm

Hi,

Thank you for the details.

Could you please enable the advanced logs and run the backup job, then open the advanced backup log by clicking the “Open folder” at the “Advanced Log” window and find the following T-SQL Command: “BACKUP DATABASE”. Then copy it and paste into SQL Server Management Studio, substitute @PathToBackup to the full backup’s name like ‘C:\BackupFolder\Backup.bak’.

If the issue will not be reproduced in SQL Server Management Studio then please send logs to our developers. Please find more details on how to enable and send logs at How to send Log to developers | SQLBackupAndFTP's blog

isthgr · June 5, 2018, 5:50am

I can’t seem to find any advanced logs, just plain Sql Server 2008 logs

Alexander · June 5, 2018, 10:14am

To find the advanced logs, please click “Open folder” at the “Advanced log” Window.

Or find them in the following directory: C:\Windows\Temp\Pranas.NET\log

isthgr · June 13, 2018, 5:15am

Thanks.

I managed to solve the problem by truncating the backup history tables (sp_delete_backuphistory ) in SQL Server.

Alexander · June 14, 2018, 10:55am

Good news! Thanks for the feedback.