I am using SQL Server 2014 and this is the situation:
- I have server A and server B.
- Overnight ETL is processed on server A.
- After the loading process completes, database X gets backed-up (with
RESTORE VERIFYONLYto ensure reliability) and then get sent to server B.
- Server B receives the
bakfile and then restore the database there.
I want to use differential backup strategy so that:
Full backup is only done only on Saturday
i.e. Full backup on server A on Saturday -> ship to server B -> Restore the full backup on server B
The rest of the days will be differential backup
i.e. Differential backup on server A -> ship to server B -> Restore the differential backup on server B
I have tried but I got an error, saying:
the log or differential backup cannot be restored because no files are ready to rollforward.
Not sure why. I checked
sys.database_files on server A and server B, and I can see that the
differential_base_GUID are the same.
Anywhere/anything else to check?
By the way, on step 2 above, when I am restoring the diff backup on server B, do I always need to restore both the Full backup + the differential backup each time?
I only restored the differential backup
WITH RECOVERY (and got that error message) because the full backup was restored already the day before.
To clarify: Yes, I want the db on server B to be readable between differentials. How can I get around that? Is my only option to
RESTORE FULL (WITH NORECOVERY) +
RESTORE DIFF (WITH RECOVERY) combo sequence each night?
Any guidance will be much appreciated.