Sql-server – SQL Server Diff not restoring from Azure BLOB storage

azurebackuprestoresql server

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.

Best Answer

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