sql-server-maintenance-solution
sql-server-maintenance-solution copied to clipboard
olahallengren
Differential backups failing
My Differntantial backups have been failing on two different SQL Instances. The error message is this: Cannot perform a differential backup for database "AR_APP", 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 have FULL Backups scheduled every night, and DIFF backups every 6 hours. I have verified all the FULL Backups are in place. So, why is the DIFF backup thinking they do not exist? this issue started about two weeks ago.
SQL Server version and edition Microsoft SQL Server 2016 (SP3) (KB5003279) - 13.0.6300.2 (X64) Aug 7 2021 01:20:37 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
Version of the script Version: 2022-01-02 13:58:13
What command are you executing? EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory ='B:\SQL Backups', @BackupType = 'DIFF', @Verify = 'Y', @CleanupTime = 24, @CheckSum = 'Y', @LogToTable = 'Y'
What output are you getting? Job '__DBA_DatabaseBackup - USER_DATABASES - DIFF' : Step 1, 'DatabaseBackup - USER_DATABASES - DIFF' : Began Executing 2022-04-06 12:00:00
Date and time: 2022-04-06 12:00:02 [SQLSTATE 01000] Server: IPSAZSQL01 [SQLSTATE 01000] Version: 13.0.6300.2 [SQLSTATE 01000] Edition: Standard Edition (64-bit) [SQLSTATE 01000] Platform: Windows [SQLSTATE 01000] Procedure: [dba].[dbo].[DatabaseBackup] [SQLSTATE 01000] Parameters: @Databases = 'USER_DATABASES', @Directory = 'B:\SQL Backups', @BackupType = 'DIFF', @Verify = 'Y', @CleanupTime = 22, @CleanupMode = 'AFTER_BACKUP', @Compress = NULL, @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'Y', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @MinBackupSizeForMultipleFiles = NULL, @MaxFileSize = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTER_BACKUP', @MirrorURL = NULL, @AvailabilityGroups = NULL, @Updateability = 'ALL', @AdaptiveCompression = NULL, @ModificationLevel = NULL, @LogSizeSinceLastLogBackup = NULL, @TimeSinceLastLogBackup = NULL, @DataDomainBoostHost = NULL, @DataDomainBoostUser = NULL, @DataDomainBoostDevicePath = NULL, @DataDomainBoostLockboxPath = NULL, @DirectoryStructure = '{ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}{Partial}{CopyOnly}', @AvailabilityGroupDirectoryStructure = '{ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}{Partial}{CopyOnly}', @FileName = '{ServerName}${InstanceName}{DatabaseName}{BackupType}{Partial}{CopyOnly}{Year}{Month}{Day}{Hour}{Minute}{Second}{FileNumber}.{FileExtension}', @AvailabilityGroupFileName = '{ClusterName}${AvailabilityGroupName}{DatabaseName}{BackupType}{Partial}{CopyOnly}{Year}{Month}{Day}{Hour}{Minute}{Second}{FileNumber}.{FileExtension}', @FileExtensionFull = NULL, @FileExtensionDiff = NULL, @FileExtensionLog = NULL, @Init = 'N', @Format = 'N', @ObjectLevelRecoveryMap = 'N', @ExcludeLogShippedFromLogBackup = 'Y', @DirectoryCheck = 'Y', @StringDelimiter = ',', @DatabaseOrder = ... Version: 2022-01-02 13:58:13 [SQLSTATE 01000] Source: https://ola.hallengren.com [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:02 [SQLSTATE 01000] Database: [AR_APP] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: SIMPLE [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 314428000002130400037 [SQLSTATE 01000] Differential base is snapshot: Yes [SQLSTATE 01000] Last log backup LSN: N/A [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:02 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\AR_APP\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:02 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:02 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [AR_APP] TO DISK = N'B:\SQL Backups\IPSAZSQL01\AR_APP\DIFF\IPSAZSQL01_AR_APP_DIFF_20220406_120002.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Msg 3035, Sev 16, State 1, Line 1 : Cannot perform a differential backup for database "AR_APP", 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] Outcome: Failed [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:02 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:02 [SQLSTATE 01000] Database: [Archive_Tables] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: FULL [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 337000000024000037 [SQLSTATE 01000] Differential base is snapshot: Yes [SQLSTATE 01000] Last log backup LSN: 338000000011200001 [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:02 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\Archive_Tables\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:02 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:02 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [Archive_Tables] TO DISK = N'B:\SQL Backups\IPSAZSQL01\Archive_Tables\DIFF\IPSAZSQL01_Archive_Tables_DIFF_20220406_120002.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Msg 3035, Sev 16, State 1, Line 1 : Cannot perform a differential backup for database "Archive_Tables", 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] Outcome: Failed [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:02 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:02 [SQLSTATE 01000] Database: [CRM_MSCRM] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: FULL [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 1489654000120077600104 [SQLSTATE 01000] Differential base is snapshot: Yes [SQLSTATE 01000] Last log backup LSN: 1489694000106315200001 [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\CRM_MSCRM\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [CRM_MSCRM] TO DISK = N'B:\SQL Backups\IPSAZSQL01\CRM_MSCRM\DIFF\IPSAZSQL01_CRM_MSCRM_DIFF_20220406_120002.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Msg 3035, Sev 16, State 1, Line 1 : Cannot perform a differential backup for database "CRM_MSCRM", 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] Outcome: Failed [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] Database: [CRM_STG] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: SIMPLE [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 3968000000032800037 [SQLSTATE 01000] Differential base is snapshot: Yes [SQLSTATE 01000] Last log backup LSN: N/A [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\CRM_STG\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [CRM_STG] TO DISK = N'B:\SQL Backups\IPSAZSQL01\CRM_STG\DIFF\IPSAZSQL01_CRM_STG_DIFF_20220406_120003.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Msg 3035, Sev 16, State 1, Line 1 : Cannot perform a differential backup for database "CRM_STG", 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] Outcome: Failed [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] Database: [dba] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: SIMPLE [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 467091000000338400214 [SQLSTATE 01000] Differential base is snapshot: Yes [SQLSTATE 01000] Last log backup LSN: N/A [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\dba\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [dba] TO DISK = N'B:\SQL Backups\IPSAZSQL01\dba\DIFF\IPSAZSQL01_dba_DIFF_20220406_120003.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Msg 3035, Sev 16, State 1, Line 1 : Cannot perform a differential backup for database "dba", 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] Outcome: Failed [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:03 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database: [DBA_Rep] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: SIMPLE [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 52000004388800037 [SQLSTATE 01000] Differential base is snapshot: Yes [SQLSTATE 01000] Last log backup LSN: N/A [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\DBA_Rep\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [DBA_Rep] TO DISK = N'B:\SQL Backups\IPSAZSQL01\DBA_Rep\DIFF\IPSAZSQL01_DBA_Rep_DIFF_20220406_120004.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Msg 3035, Sev 16, State 1, Line 1 : Cannot perform a differential backup for database "DBA_Rep", 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] Outcome: Failed [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database: [Dev2016_MSCRM] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: SIMPLE [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 3932000000028800037 [SQLSTATE 01000] Differential base is snapshot: Yes [SQLSTATE 01000] Last log backup LSN: N/A [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\Dev2016_MSCRM\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [Dev2016_MSCRM] TO DISK = N'B:\SQL Backups\IPSAZSQL01\Dev2016_MSCRM\DIFF\IPSAZSQL01_Dev2016_MSCRM_DIFF_20220406_120004.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Msg 3035, Sev 16, State 1, Line 1 : Cannot perform a differential backup for database "Dev2016_MSCRM", 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] Outcome: Failed [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database: [FA_DEV] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: SIMPLE [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 2580000000893600037 [SQLSTATE 01000] Differential base is snapshot: Yes [SQLSTATE 01000] Last log backup LSN: N/A [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\FA_DEV\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [FA_DEV] TO DISK = N'B:\SQL Backups\IPSAZSQL01\FA_DEV\DIFF\IPSAZSQL01_FA_DEV_DIFF_20220406_120004.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Msg 3035, Sev 16, State 1, Line 1 : Cannot perform a differential backup for database "FA_DEV", 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] Outcome: Failed [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database: [Form_Automation] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: FULL [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 15249346000079370400289 [SQLSTATE 01000] Differential base is snapshot: Yes [SQLSTATE 01000] Last log backup LSN: 15249470000037916800001 [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\Form_Automation\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [Form_Automation] TO DISK = N'B:\SQL Backups\IPSAZSQL01\Form_Automation\DIFF\IPSAZSQL01_Form_Automation_DIFF_20220406_120004.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Msg 3035, Sev 16, State 1, Line 1 : Cannot perform a differential backup for database "Form_Automation", 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] Outcome: Failed [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database: [HR] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: FULL [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 53000004610400037 [SQLSTATE 01000] Differential base is snapshot: No [SQLSTATE 01000] Last log backup LSN: 53000004661600001 [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\HR\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:04 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [HR] TO DISK = N'B:\SQL Backups\IPSAZSQL01\HR\DIFF\IPSAZSQL01_HR_DIFF_20220406_120004.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Processed 80 pages for database 'HR', file 'HR' on file 1. [SQLSTATE 01000] Processed 2 pages for database 'HR', file 'HR_log' on file 1. [SQLSTATE 01000] BACKUP DATABASE WITH DIFFERENTIAL successfully processed 82 pages in 0.438 seconds (1.453 MB/sec). [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:01 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:05 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:05 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: RESTORE VERIFYONLY FROM DISK = N'B:\SQL Backups\IPSAZSQL01\HR\DIFF\IPSAZSQL01_HR_DIFF_20220406_120004.bak' WITH CHECKSUM [SQLSTATE 01000] The backup set on file 1 is valid. [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:01 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:06 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:06 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_delete_file 0, N'B:\SQL Backups\IPSAZSQL01\HR\DIFF', 'bak', '2022-04-05T14:00:06' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:01 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:07 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:07 [SQLSTATE 01000] Database: [MSCRM_CONFIG] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: FULL [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 5713000001960000171 [SQLSTATE 01000] Differential base is snapshot: No [SQLSTATE 01000] Last log backup LSN: 5736000000851200001 [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:07 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\MSCRM_CONFIG\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:07 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:07 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [MSCRM_CONFIG] TO DISK = N'B:\SQL Backups\IPSAZSQL01\MSCRM_CONFIG\DIFF\IPSAZSQL01_MSCRM_CONFIG_DIFF_20220406_120007.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Processed 10088 pages for database 'MSCRM_CONFIG', file 'MSCRM_CONFIG' on file 1. [SQLSTATE 01000] Processed 5 pages for database 'MSCRM_CONFIG', file 'MSCRM_CONFIG_log' on file 1. [SQLSTATE 01000] BACKUP DATABASE WITH DIFFERENTIAL successfully processed 10093 pages in 1.254 seconds (62.880 MB/sec). [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:02 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:09 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:09 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: RESTORE VERIFYONLY FROM DISK = N'B:\SQL Backups\IPSAZSQL01\MSCRM_CONFIG\DIFF\IPSAZSQL01_MSCRM_CONFIG_DIFF_20220406_120007.bak' WITH CHECKSUM [SQLSTATE 01000] The backup set on file 1 is valid. [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:09 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:09 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_delete_file 0, N'B:\SQL Backups\IPSAZSQL01\MSCRM_CONFIG\DIFF', 'bak', '2022-04-05T14:00:09' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:01 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:10 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:10 [SQLSTATE 01000] Database: [PA_Customer_Master] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: FULL [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 57000003780800037 [SQLSTATE 01000] Differential base is snapshot: No [SQLSTATE 01000] Last log backup LSN: 57000003830400001 [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:10 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\PA_Customer_Master\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:10 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:10 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [PA_Customer_Master] TO DISK = N'B:\SQL Backups\IPSAZSQL01\PA_Customer_Master\DIFF\IPSAZSQL01_PA_Customer_Master_DIFF_20220406_120010.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Processed 152 pages for database 'PA_Customer_Master', file 'PA_Customer_Master' on file 1. [SQLSTATE 01000] Processed 2 pages for database 'PA_Customer_Master', file 'PA_Customer_Master_log' on file 1. [SQLSTATE 01000] BACKUP DATABASE WITH DIFFERENTIAL successfully processed 154 pages in 0.215 seconds (5.577 MB/sec). [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:01 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:11 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:11 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: RESTORE VERIFYONLY FROM DISK = N'B:\SQL Backups\IPSAZSQL01\PA_Customer_Master\DIFF\IPSAZSQL01_PA_Customer_Master_DIFF_20220406_120010.bak' WITH CHECKSUM [SQLSTATE 01000] The backup set on file 1 is valid. [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:11 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:11 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_delete_file 0, N'B:\SQL Backups\IPSAZSQL01\PA_Customer_Master\DIFF', 'bak', '2022-04-05T14:00:11' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:11 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:11 [SQLSTATE 01000] Database: [ReportServer] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: SIMPLE [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 9445000001664800207 [SQLSTATE 01000] Differential base is snapshot: No [SQLSTATE 01000] Last log backup LSN: N/A [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:11 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\ReportServer\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:11 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:11 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [ReportServer] TO DISK = N'B:\SQL Backups\IPSAZSQL01\ReportServer\DIFF\IPSAZSQL01_ReportServer_DIFF_20220406_120011.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Processed 12192 pages for database 'ReportServer', file 'ReportServer' on file 1. [SQLSTATE 01000] Processed 9 pages for database 'ReportServer', file 'ReportServer_log' on file 1. [SQLSTATE 01000] BACKUP DATABASE WITH DIFFERENTIAL successfully processed 12201 pages in 4.096 seconds (23.271 MB/sec). [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:05 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:16 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:16 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: RESTORE VERIFYONLY FROM DISK = N'B:\SQL Backups\IPSAZSQL01\ReportServer\DIFF\IPSAZSQL01_ReportServer_DIFF_20220406_120011.bak' WITH CHECKSUM [SQLSTATE 01000] The backup set on file 1 is valid. [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:01 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:17 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:17 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_delete_file 0, N'B:\SQL Backups\IPSAZSQL01\ReportServer\DIFF', 'bak', '2022-04-05T14:00:17' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:17 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:17 [SQLSTATE 01000] Database: [ReportServerTempDB] [SQLSTATE 01000] State: ONLINE [SQLSTATE 01000] Standby: No [SQLSTATE 01000] Updateability: READ_WRITE [SQLSTATE 01000] User access: MULTI_USER [SQLSTATE 01000] Recovery model: SIMPLE [SQLSTATE 01000] Encrypted: No [SQLSTATE 01000] Is accessible: Yes [SQLSTATE 01000] Differential base LSN: 298602000001513600279 [SQLSTATE 01000] Differential base is snapshot: No [SQLSTATE 01000] Last log backup LSN: N/A [SQLSTATE 01000] Allocated extent page count: N/A [SQLSTATE 01000] Modified extent page count: N/A [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:17 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_create_subdir N'B:\SQL Backups\IPSAZSQL01\ReportServerTempDB\DIFF' IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:17 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:17 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: BACKUP DATABASE [ReportServerTempDB] TO DISK = N'B:\SQL Backups\IPSAZSQL01\ReportServerTempDB\DIFF\IPSAZSQL01_ReportServerTempDB_DIFF_20220406_120017.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Processed 12136 pages for database 'ReportServerTempDB', file 'ReportServerTempDB' on file 1. [SQLSTATE 01000] Processed 440 pages for database 'ReportServerTempDB', file 'ReportServerTempDB_log' on file 1. [SQLSTATE 01000] BACKUP DATABASE WITH DIFFERENTIAL successfully processed 12576 pages in 9.535 seconds (10.304 MB/sec). [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:10 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:27 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:27 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: RESTORE VERIFYONLY FROM DISK = N'B:\SQL Backups\IPSAZSQL01\ReportServerTempDB\DIFF\IPSAZSQL01_ReportServerTempDB_DIFF_20220406_120017.bak' WITH CHECKSUM [SQLSTATE 01000] The backup set on file 1 is valid. [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:02 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:29 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:29 [SQLSTATE 01000] Database context: [master] [SQLSTATE 01000] Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = dbo.xp_delete_file 0, N'B:\SQL Backups\IPSAZSQL01\ReportServerTempDB\DIFF', 'bak', '2022-04-05T14:00:29' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1) [SQLSTATE 01000] Outcome: Succeeded [SQLSTATE 01000] Duration: 00:00:00 [SQLSTATE 01000] Date and time: 2022-04-06 12:00:29 [SQLSTATE 01000] [SQLSTATE 01000] Date and time: 2022-04-06 12:00:29 [SQLSTATE 01000] [SQLSTATE 01000]
I've noticed this in one environment across different SQL Versions where this issue occurs. It happens infrequently enough where I just re-run the Full backup to resolve, but my error occurs even when using the changebackuptype parameter. You should try using @ChangeBackupType = 'Y' Although I've experienced this issue even using that parameter, but it may work for you.
@csdunaway Prety rude to demand for this kind of support on an open source project.
The error comes from SQL Server itself.. So guess you better open a supportcase with Microsoft if this is really a problem..
Otherwise try to debug yourself. For instance You could try running the execured command seperatly
It is mentioned in the output: BACKUP DATABASE [AR_APP] TO DISK = N'B:\SQL Backups\IPSAZSQL01\AR_APP\DIFF\IPSAZSQL01_AR_APP_DIFF_20220406_120002.bak' WITH CHECKSUM, COMPRESSION, DIFFERENTIAL
My best guess this issue has to do with a bug in SQL Server or something. Haven't seen this issue myself in our environment though.
If I see the error occur in the environment I've noticed it in previously, I'll try to dig into it and see what I can find and report back here. But it's a pretty infrequent occurrence..
Thanks. My Diff backups fails 3 out of 4 that mes every day now. I am using SQL Server 2016.
Get Outlook for Androidhttps://aka.ms/AAb9ysg
From: Ben @.> Sent: Thursday, April 14, 2022 4:10:25 PM To: olahallengren/sql-server-maintenance-solution @.> Cc: Clifton Dunaway @.>; Mention @.> Subject: Re: [olahallengren/sql-server-maintenance-solution] Differential backups failing (Issue #640)
If I see the error occur in the environment I've noticed it in previously, I'll try to dig into it and see what I can find and report back here. But it's a pretty infrequent occurrence..
— Reply to this email directly, view it on GitHubhttps://github.com/olahallengren/sql-server-maintenance-solution/issues/640#issuecomment-1099581685, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AA73RG4Z35AR4HWLEJ5WXOTVFB3TDANCNFSM5SW3UL2Q. You are receiving this because you were mentioned.Message ID: @.***>
My Diff backups are failing 3 out of 4 times every day. the only one that works is the one that runs after the FULL backup runs.
Can you send a portion of the MSDB history for one of the databases experiencing the issue? Something like the results from: SELECT TOP(50) * FROM msdb..backupset WHERE database_name = '' ORDER BY backup_start_date DESC
It's difficult to determine the issue considering it's not easily reproducable nor is only seeing the OLA Logs the best way to see all the parts and pieces for what's causing it. Is this a Production or Dev environment? Are you failing to meet your Recovery Point Objective (RPO) because of this or is there another backup solution in place in the meantime or are you just escalating to taking full backups?
Here is the msdb info for you. Thanks,Clifton Dunaway
On Friday, April 22, 2022, 08:43:07 AM EDT, Ben ***@***.***> wrote:
Can you send a portion of the MSDB history for one of the databases experiencing the issue? Something like the results from: SELECT TOP(50) * FROM msdb..backupset WHERE database_name = '' ORDER BY backup_start_date DESC
— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: @.***>
I have the same problem, error on DIFF.
SQL Server 2019 Windows Server 2019
Full every night and DIFF 2 times per day:
Command: BACKUP DATABASE [XXXXX] TO DISK = N'E:\SQLBackups\HOSTNAME$HR\XXXXX\DIFF\HOSTNAME$HR_XXXXX_DIFF_20220427_114501.bak' WITH CHECKSUM, NO_COMPRESSION, DIFFERENTIAL [SQLSTATE 01000] Msg 3035, Sev 16, State 1, Line 1 : Cannot perform a differential backup for database "XXXXX", 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 had troubleshooted via email with Clifton and was able to determine that his differential backups were failing because there was a full non-copy-only snapshot backup taking place before his differential backups, as snapshot backups that aren't copy only will break the differential chain. I would execute the same command and see if you have the same issue.. SELECT TOP(50) * FROM msdb..backupset WHERE database_name = '' ORDER BY backup_start_date DESC
Can also discuss further via email if you'd like.
You need to specify the database name that's having the issue in the database_name = ''
And just let me know the best email to reach you at and I'll reach out :)
I determined the error is due to having the virtual server doing a snapshot. This causes the log chain to get out of sync.
Thank you, Clifton Dunaway
From: Miguel Almeida @.> Sent: Wednesday, April 27, 2022 7:43:07 AM To: olahallengren/sql-server-maintenance-solution @.> Cc: Clifton Dunaway @.>; Mention @.> Subject: Re: [olahallengren/sql-server-maintenance-solution] Differential backups failing (Issue #640)
Yes, sorry! :)
miguel.salmeida19atgmail.com
— Reply to this email directly, view it on GitHubhttps://github.com/olahallengren/sql-server-maintenance-solution/issues/640#issuecomment-1110901361, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AA73RG3PRCDVTWTIINH6QD3VHER4XANCNFSM5SW3UL2Q. You are receiving this because you were mentioned.Message ID: @.***>
What do you mean with "virtual server doing a snapshot"? For example, replicas or snapshot through Veeam Backup?
I believe Clifton's issue was with Azure Backups, as it's taking a Full snapshot backup of the database, but the backup isn't copy-only so it breaks the differential chain as you can't take differential backups with a snapshot as the base.
I believe Clifton's issue was with Azure Backups, as it's taking a Full snapshot backup of the database, but the backup isn't copy-only so it breaks the differential chain as you can't take differential backups with a snapshot as the base.
You can configure Azure Backup to take copy_only VSS backups - it is controlled by a registry key and is documented at https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-troubleshoot#troubleshoot-vm-snapshot-issues:
VMs with SQL Server backup configured can cause snapshot task delay. By default, VM backup creates a VSS full backup on Windows VMs. VMs that run SQL Server, with SQL Server backup configured, can experience snapshot delays. If snapshot delays cause backup failures, set following registry key:
[HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\BCDRAGENT]
"USEVSSCOPYBACKUP"="TRUE"
It is a virtual server sitting in Microsoft Azure.
Thank you, Clifton Dunaway
From: pbarryuk @.> Sent: Wednesday, April 27, 2022 9:26:56 AM To: olahallengren/sql-server-maintenance-solution @.> Cc: Clifton Dunaway @.>; Mention @.> Subject: Re: [olahallengren/sql-server-maintenance-solution] Differential backups failing (Issue #640)
I believe Clifton's issue was with Azure Backups, as it's taking a Full snapshot backup of the database, but the backup isn't copy-only so it breaks the differential chain as you can't take differential backups with a snapshot as the base.
You can configure Azure Backup to take copy_only VSS backups - it is controlled by a registry key and is documented at https://docs.microsoft.com/en-us/azure/backup/backup-azure-vms-troubleshoot#troubleshoot-vm-snapshot-issues
VMs with SQL Server backup configured can cause snapshot task delay. By default, VM backup creates a VSS full backup on Windows VMs. VMs that run SQL Server, with SQL Server backup configured, can experience snapshot delays. If snapshot delays cause backup failures, set following registry key:
[HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\BCDRAGENT] "USEVSSCOPYBACKUP"="TRUE"
— Reply to this email directly, view it on GitHubhttps://github.com/olahallengren/sql-server-maintenance-solution/issues/640#issuecomment-1111001710, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AA73RG2LFPUJC2J25TTSEJLVHE6CBANCNFSM5SW3UL2Q. You are receiving this because you were mentioned.Message ID: @.***>