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.
Is it possible to restore the database to the last transaction (which should be just after the database was created) using just the transaction log, or is this something that can't be done?
No, restoring a transaction log is sequential. Transaction log relies on LSN (Log Sequence Number)
Also, you cannot restore your database with just transaction log. It requires the main database file (MDF) and NDF (secondary datafiles if there are any).
Best Answer
If the restore completed, then the database was restored fully. What kind of backup did you take (show the code, or explain the steps if using a 3rd party tool or SSMS GUI)? How did you restore that database (show the code, or explain the steps)? What information was missing?
This answer and the link to Paul Randal's article will help explain how a full backup happens and should answer your Are the log files anywhere question, assuming you did a Full backup. You can, of course, do transaction log backups too. - scsimon
I've seen cases where backup was done appending to the file and then restore was done from the first file (which is default) from that file. I.e., restoring not-the-latest-backup. Check using
RESTORE HEADERONLY. - tibor-karaszi