EDIT: The problem turned out to be that while backups are made every night, I was trying to restore to a point in time that had not yet been backed up. I would have realized this sooner if I were scripting the restore, but because I was using the GUI it simply chose the most recent log file and didn't bother to notify me that the chosen log file didn't actually contain the point in time I was specifying.
After I made a current backup of the db and transaction log then it worked perfectly.
Having said that, I still don't understand how the record with time of '2013-11-27 12:52:08.240' was restored in the scenario described below. But for now I'll just chalk that up to being something screwy with the application recording an incorrect time.
I am once again befuddled by SQL Server's (in?)ability to restore to a specific point in time.
Sometimes when I've tried this in the past it would seem that SQL Server restored to a point in time near where I asked it to but not quite where I specified. I chalked this up to being a misunderstanding on my end since I know the dates recorded in the database originate in the application and therefore may vary ever so slightly from the time that the records are actually written. The variances were actually a little more than 'slight', but this is still the best explanation I could come up with.
Around 12:55pm today I restored a database back 30 minutes to 12:25pm. Now when I do a query on the log and sort by date/time I see a record with a date of '2013-11-26 18:21:49.200' followed immediately by a record with a date of '2013-11-27 12:52:08.240' right next to each other.
So my first observation is that all of the records from the beginning of today up to the time I restored to (12:25pm) are not there. My second observation is that there is a single record at 12:52pm which is 27 minutes AFTER the time I tried to restore to (way, way to large for a slight variance in time between when the application picked a time and when the record actually got written).
Has anyone else run into this? I have no clue why this would be happening and would appreciate any help in figuring out how to backup my databases in a way that they will properly be restored to a point in time when I need to since this is the whole reason I switched to using the Full Recovery model for backups.
Best Answer
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:
(**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.