Diff backup fails with error 3035 but log backup succeeds
-
We have a SQL Server 2014 Enterprise where the DIFF backups fail.
This is the error message we get:Msg 3035, Level 16, State 1, Server sqltest, Line 1
Cannot perform a differential backup for database 'database1', because a current database backup does not exsist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.After analysing the output of the following query we noticed that a third party tool was taking snapshot backups.
select top 20 bs.type,bs.database_backup_lsn,bs.checkpoint_lsn,bs.backup_start_date,bs.is_snapshot, bs.is_copy_only,bs.user_name from dbo.backupset bs where bs.database_name = 'database1' order by backup_start_date desc
According to https://blog.sqlauthority.com/2017/04/19/sql-server-msg-3035-level-16-cannot-perform-differential-backup-database-sqlauthority-current-database-backup-not-exist/ these tools use VSS to take a backup which is not a normal full backup.
What I don't understand is why LOG backups succeed? To my knowledge, they are also based on the last FULL backup.
Can somebody explain this difference to me?
-
A log backup is not based on the most recent full backup. It is based on the most recent log backup.
I.e., you can "skip" a full or differential backup when you restore log backups, as long as you have an unbroken chain of log backups.
As for your particular issue: Work with the backup software/vendor and see if you can make it perform the snapshot backups so they are seen as
COPY_ONLY
by SQL Server. Possibly a setting in your backup software.