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
CHECKSUM
andRESTORE VERIFYONLY
to ensure reliability) and then get sent to server B. - Server B receives the
bak
file 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 BThe 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_LSN
and 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.