SQL SERVER – Msg 3035, Level 16 – Cannot Perform a Differential Backup for Database “SQLAuthority”, Because a Current Database Backup Does not Exist

If we look at the error in the subject line, it is very clear that a full backup is needed so that the differential backup chain can be initiated. If this happens to a newly created database, then it makes sense and there is no need for me to write a blog. There was an interesting finding about the cause of this error, other than obvious one, so sharing it.  Let us see another error related to differential backup.

My client contacted me saying that they are having a database in production for which they have full and differential backups configured using maintenance plan. Sometimes they see below failure message for the differential backup plan under the history.
Failed:(-1073548784) Executing the query “BACKUP DATABASE [HRMS] TO DISK = N’O:\\Backup\\HRMS…” failed with the following error: “Cannot perform a differential backup for database “HRMS”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP DATABASE is terminating abnormally.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

They also showed me successful run on the full backup plan and it was not having any errors. So, why is SQL confused and thinks there is not full backup. I asked them to try the same using T-SQL in management studio and it failed with the same error.

Msg 3035, Level 16, State 1, Line 1
Cannot perform a differential backup for database “HRMS”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

I looked into SQL Server ERRORLOG and found something interesting for this database.

2017-04-13 06:39:50.10 spid52 I/O is frozen on database HRMS. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2017-04-13 06:40:50.11 spid52 I/O was resumed on database HRMS. No user action is required.
2017-04-13 06:40:50.24 Backup Database backed up. Database: HRMS, creation date(time): 2016/09/29(00:37:12), pages dumped: 426, first LSN: 55:395:37, last LSN: 55:414:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {‘{1A8E69AB-E97B-4CCE-8B3C-7E62573B8FD4}4’}). This is an informational message only. No user action is required.

Solarwinds

If we take SQL native backups, we would never see “I/O is frozen” and “I/O was resumed” messages. Moreover, the TYPE of backup would be FILE not VIRTUAL_DEVICE as shown in 3rd message. We used query from my blog to check the backups for this database. https://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database

SQL SERVER - Msg 3035, Level 16 - Cannot Perform a Differential Backup for Database "SQLAuthority", Because a Current Database Backup Does not Exist vss-fail-01-800x57

We also check the report https://blog.sqlauthority.com/2014/08/04/sql-server-ssms-backup-and-restore-events-report/

SQL SERVER - Msg 3035, Level 16 - Cannot Perform a Differential Backup for Database "SQLAuthority", Because a Current Database Backup Does not Exist vss-fail-02-800x394 SQL SERVER - Msg 3035, Level 16 - Cannot Perform a Differential Backup for Database "SQLAuthority", Because a Current Database Backup Does not Exist vss-fail-01-800x57

At this point, we were clear that these are backups happening from some 3rd party tool. These tools use VSS to take backup of the machine. This is not a normal full database backup carried directly by SQL Server but through the Virtual Device Interface (VDI). I search and found there’s a similar known issue below:

https://support.microsoft.com/en-us/help/903643/the-ntbackup-exe-operation-may-break-the-differential-backup-chain-of-a-sql-server-database-and-may-invalidate-the-differential-backups-when-you-perform-a-snapshot-backup-of-the-database

WORKAROUND/SOLUTION

In this case, we figured out that these were Azure Virtual Machines and recently they configured VM level backup from Azure Portal. I looked into Microsoft’s documentation and I did find a solution to fix this on Azure VMs. We can enable VSS copy using the registry:

https://azure.microsoft.com/en-in/documentation/articles/backup-azure-vms-introduction/#how-does-azure-back-up-virtual-machines

Azure Backup takes VSS Full back-up on the Windows VMs. To enable VSS Copy Backups the below registry key needs to be set on the VM.

[HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\BCDRAGENT] “USEVSSCOPYBACKUP”=”TRUE”

We created key and verified that Azure was taking copy_only backup and maintenance plan were able to run a maintenance plan successfully along with Azure VM backups.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Login Failed. The Login is From an Untrusted Domain and Cannot be Used with Windows Authentication
Next Post
SQL SERVER – FIX: The SQL Server Failed to Initialize VIA Support Library [QLVipl.dll]

Related Posts

4 Comments. Leave new

  • Great Tutorial

    Reply
  • Karlheinz Kuder
    June 1, 2017 8:10 pm

    Thanks for this blog entry.
    I have a similar issue on private VM-Farm, Win 2012R2 and SQL-Server 2014 R2. We use EMC legato networker to do a snapshot backup of our SQL Server. Interesting thing is, that there are 3 instances of MSSQL Server on my host. But only at one of them the diff-queque is broken.

    Reply
  • A really great explanation and Tutorial. i got a similar issue in Azure in a Win 2008 Data Center x64 and SQL Server 2008 R2. The differential backup Job was broken by the Azure Backup Process. We stopped the Azure backup and ran the SQL Maintenance Backup Plan again and the diff backup Jobs ran successfully . i will try the regedit entry to test. Thanks a lot !

    Reply

Leave a Reply

Menu