I'm having an issue that only happens sometimes where my SQL Server differential backup job fails with an error message similar to
Msg 3035, Sev 16, State 1, Line 1 : Cannot perform a differential backup for database "MyDatabaseName", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000]
Msg 3013, Sev 16, State 1, Line 1 : BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]
I'm currently using Ola Hallengren's SQL Server Mantenance Solution script for backup's, integrity check's and index maintenance. I've scheduled the backup job's as such:
- Full Backup of system databases every day @ 1:30 AM
- Full Backup of all user databases every week on Monday, Wednesday & Friday @ 2:30 AM
- Differential Backup of all user databases every week on Sunday, Tuesday, Thursday & Saturday @ 2:30 AM
- Transaction Log backup or all user databases every 30 minutes
I've also set the cleanup time to 168 hours...which is 7 days.
I know that usually when this error message comes up, it is due to a full backup not being present, or possible the recovery mode on a database being changed. I have checked both of these and neither seems to be the case. I can confirm that my Friday full backup was successful, however my Saturday and Sunday Differentials failed. There have also been no changes to the recovery model, and no manual backup taken through SQL Server.
It's worth noting that this only happens sometimes. Sometimes the differential backups work without a problem and other times they fail.
This server is a VM, and we are using VMWare vSphere/vCenter 6.5. I've talked to my server admin and asked how his backup is running an he told me that we are using Quest AppAssure which leverage VMWare snapshot technology, and that he is backuping the drives every x number of minutes, so it is possible that the time of his backup changes and eventually overlaps with mine.
We looked back at the time that his backups ran on the weekend and they happened within a few minutes before mine started. I'm wondering if this is causing my backup issues? if so is there a way to prevent this, or do we simply need to plan the backups at different times that don't overlap?
Thanks
COPY ONLY
. So, if we step back, can we ask what's your RPO / RTO? Perhaps your backup strategy could be adjusted. It seems almost certain. – scsimon