1

This is what I did:

  1. Took full backup
  2. Took Differential backup (immediately after full)
  3. Moved above files to DR server
  4. Restore full (successfully)
  5. Restore differential (error)

enter image description here

RESTORE DATABASE AdventureWorks2017DR FROM DISK = 'AdventureWorks2017_1220.BAK' WITH NORECOVERY
RESTORE DATABASE AdventureWorks2017DR FROM DISK = 'AdventureWorks2017_1220_DIFF.BAK' WITH NORECOVERY

Error:

Msg 3136, Level 16, State 1, Line 34
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 34
RESTORE DATABASE is terminating abnormally.

Why it gives me an error? Those two backups are the last two. There are no backups between them.

Update: ran command RESTORE HEADERONLY FROM DISK

Thank you @AMtwo. You are correct. There are multiple backups within the same file. I couldn't find a way to simply restore them all. Is that possible?

enter image description here

3
  • 3
    Did you perform your backup WITH INIT? Could there be multiple backups within the same file? You might try adding to your question the results of the RESTORE HEADERONLY FROM DISK = 'AdventureWorks2017_1220.BAK' for both full & diff backups
    – AMtwo
    Dec 21, 2021 at 3:36
  • From the screenshot, there is clearly a gap in LSNs between the full and diff backups. Check your query and make sure another backup was not taken. Dec 21, 2021 at 18:12
  • Thank you. It's turned out I was backing up into the same file. And now, trying to restore this file, it only tries to restore the last LSN (out of 6). So is any way I can restore that one file with multiple LSN's? From 1-6th?
    – Serdia
    Dec 21, 2021 at 19:20

2 Answers 2

2

To restore from a backup device containing multiple backups, specify the FILE number, eg

RESTORE DATABASE AdventureWorks2017DR FROM DISK = 'AdventureWorks2017_1220.BAK' WITH FILE = 6, NORECOVERY
RESTORE DATABASE AdventureWorks2017DR FROM DISK = 'AdventureWorks2017_1220_DIFF.BAK' WITH FILE = 6, NORECOVERY

Although everyone uses files these days, a backup device could be a tape, with no proper filesystem. So SQL Server had to manage the raw medium and append multiple files to the tape. You can do the same thing with FILE devices, it's just normally done only by mistake, when you forget to add INIT to the BACKUP command.

0

When you pick the device where the backup is to be restored from, you can also pick the backup within the file. However, it looks like all of backups have the same name.

You may want to take a new full and diff back and give each a distinct name. Try appending the date, such AW2017_Full_20211222 and AW2017_Diff_20211222. This should get you one backup per file.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.