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?