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.
FULL
andDIFF
are different, try usingINIT
on theDIFF
BACKUP
also.FULL
by usingRESTORE HEADERONLY
. See the post I referenced.