SQL Server Diff not restoring from Azure BLOB storage

Question

I'm setting up a process to back up a SQL Server 2016 database to Azure blob storage and subsequently restore to another server. I'm doing a weekly full back up and hourly differential. The issue I'm having is that the differential will not restore over the full back up.

Back up to Azure:

BACKUP DATABASE [DB_NAME] TO 
URL = 
N'https://DB_BLOBNAME.blob.core.windows.net/livebackup/DB_NAME_FULL.bak'
WITH CREDENTIAL = N'BackupCred' , FORMAT, INIT, 
NAME = N'DB_NAME_FULL', SKIP, REWIND, NOUNLOAD, STATS = 10, COMPRESSION
GO
BACKUP DATABASE [DB_NAME] TO URL = 
N'https://DB_BLOBNAME.blob.core.windows.net/livebackup/DB_NAME_DIFF.bak' 
WITH CREDENTIAL = N'BackupCred', DIFFERENTIAL ,
NOFORMAT, NOINIT, NAME = N'DB_NAME_DIFF', NOSKIP, NOREWIND, NOUNLOAD, 
STATS = 10
GO

I'm using Azcopy to bring the two .bak files down to the local disk, no issues there. Then restoring from Local file system.

DROP DATABASE DB_NAME
GO
RESTORE DATABASE DB_NAME FROM DISK = 
'C:\AzSyncFolders\DB_NAME\DB_NAME_FULL.BAK' WITH NORECOVERY
GO
RESTORE DATABASE DB_NAME FROM DISK = 
'C:\AzSyncFolders\DB_NAME\DB_NAME_DIFF.BAK' WITH RECOVERY
GO

All pretty straightforward stuff, but I'm consistently getting this message.

Msg 3136, Level 16, State 1, Line 8 This differential backup cannot be restored because the database has not been>>restored to the correct earlier state. Msg 3013, Level 16, State 1, Line 8 RESTORE DATABASE is terminating abnormally.

When I do a full and differential back up on the server locally using the same scripts they both restore correctly.

Am I missing anything obvious in my scripts either in the back up or restore?

Definitely no additional full back up between FULL and DIFF

Just using .BAK out of convention, not sure that having the .DIFF extension will make a difference. Will check to see if there are multiples in the .BAK. No multiples in there.

Changed to use INIT, still getting the error.

**** Update **** Apparently there's a third party tool also taking backups to another location. This would seem to be causing my mismatch between the full and diff LSN. Looks like I'm going to have to take more full backups and abandon the use of diffs. Scott Hodgin called it on the first answer. Thanks for all the help.

• Scott Hodgin - Retired: This sounds like another full backup has been taken between your original full (that you are trying to restore) and the diff you are trying to restore.
• Scott Hodgin - Retired: See if you can use the information in Understanding SQL Server Log Sequence Numbers for Backups to verify that the DatabaseBackupLSN value for the differential backup identifies the full database backup that is required in order to apply the differential database backup.
• Kevin3NF: Any chance there are multiple backup files in DB_NAME_DIFF.bak? And why .bak, not .dff?
• Scott Hodgin - Retired: Since the file names for the FULL and DIFF are different, try using INIT on the DIFF BACKUP also.
• Scott Hodgin - Retired: As i mentioned in my comment, verify the DatabaseBackupLSN matches the FULL by using RESTORE HEADERONLY. See the post I referenced.

Answer 1

Most likely, you have multiple Differential backups in your DB_NAME_DIFF.bak file, and the restore is picking one that occurred before the most recent FULL. As I hinted in my comment, and Scott stated in his.

Edit the following to check for multiple files in a backup device:

RESTORE HEADERONLY 
FROM DISK = N'C:\AdventureWorks-FullBackup.bak' 
WITH NOUNLOAD; 
GO 

• Harvey: Yes, this may be the root of it. My DatabaseBackupLSN in the DIFF doesn't match the CheckpointLSN in the FULL. How can I check if there are multiples, Restore FILELISTONLY shows only an mdf and ldf?
• Kevin3NF: @Harvey, see edited answer above
• Harvey: Restore Headeronly shows only one row in the diff.
• Harvey: See update to question.