The differential backup is always associated with the last full backup. It is that simple.
So any differential restore is looking for a certain full restore.
So yes, the base is reset. You can't mix and match.
Personally, I wouldn't have monthly full backups and hourly diffs. The frequencies don't match (example: weekly full, daily diff, hourly log). And I wouldn't use any binary file diff rubbish for my backups because I expect to be able to restore them.
The quick answer here is: What you are describing/insinuating is kind of a big deal, and I've not experienced this and this is not the default or expected behavior of SQL Server.
The ability to restore is a fundamental function of an RDBMS. The ability to restore to a point in time is a fundamental part of that fundamental function. And the underpinnings of this ability is the logging mechanism of a Relational Database Management System.
This is not only what gives us the ability to restore to a point in time, but the ability to recover to a consistent state during a crash or restart. It is the same transaction log that is used there.
In a nutshell (there are lot of resources that describe the process. This is a good one to start) - SQL Server uses a write-ahead-logging mechanism. This means everything is written to your transaction log and confirmed to be written there before that transaction is considered durable and your transaction can be considered done. If this didn't work, we could never trust the reliability of SQL Server and the data in your app, because data could become inconsistent with IO issues, memory issues, a restart, etc. We'd have no guarantee of maintaining the ACID properties of the database anymore.
So with that caveat in mind there is one of three things going on here:
- An Error/Fault during the restore - I am guessing that is not the case here. Why? Because you were able to restore and query the database after your restore and you are not dealing with a corrupt database or restore failures.
- A Bug - I am not saying this isn't possible, but I am skeptical that this is the case. Especially with your comment update indicating some uncertainty about what should be in the application log you are looking at.
- A Misunderstanding - This is the most likely guess of what you are experiencing. You can confirm this a bit by doing the same process to a dev server or test server. Either the wrong database could be being restored, the table you are querying is not showing you the data you are expecting to see and not logging the data correctly or there weren't the transactions you were expecting to see.
- You actually aren't doing transaction log backups/restores - I suppose there could also be some confusion in what you are doing. To be clear - to restore to a point in time you have to restore a Full backup with NORECOVERY, then apply the necessary transaction logs between your last full backup and the time you want to restore to, specifying a WITH STOPAT on the final transaction log restore and specifying WITH RECOVERY on that final one.. Also, if the time you want to go back to is after your last transaction log backup, you need to first backup the tail of the log one last time to be able to restore to that point in time. (This article talks about how to restore transaction log backups. This one talks about backing up the tail of the log.)
(**note:**I would also confirm time zone settings, the time zone on the server where the backups were taken is the time zone used in your restore as this question got into. So if you are restoring from one server to the other - check your time zones and all. )
One simple way you can prove this out?
Query the table you are looking at for logging after normal activity for a bit. Do you see what you are expecting to see based on your understanding for the activity today? Okay good, go through your normal restore process or the one indicated in the documentation linked to above if you've done something different. Do this restore to another machine/dev machine so you aren't affecting your primary server! Restore to the time you ran your query in this table. Look at your table - if you did the steps above correctly you should see that the data looks as it should. If it doesn't, I would first look at your process, re-read the instructions, confirm the database, confirm the time zones and times on the two servers, and if something is still off and you've totally ruled out something missing in the process? I would open a call immediately with Microsoft, but I'd honestly be surprised if you were experiencing a bug or issue.
Best Answer
Short Answer: From when the backup was taken
I actually wasn't sure of the answer, so I just made a database, put it in full recovery model, took a full backup, did some work (create a couple tables named after the time I created them) and then started restores. Restored the full and then attempted to apply the log backups.
When I did that I had to specify the time zone from when the changes were made - from when the backup was taken. If I tried to use the new time zone's setting, it errored - bad timing.
So the answer to your question in my experience with SQL Server 2012 and 2008R2 - appears to be "The local time from when the backup was taken"
This backs up my expectation before testing. The way the log records are written and the way the backups are taken - that makes sense.
That said - I can't imagine a ton of situations where the time zone is changing with the need to worry about point in time recovery?