Restoring to specific point in time in SQL Server

Question

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.

Answer 1

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:

1. 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.
2. 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.
3. 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.
   4. 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.