Point in time recovery from the same database - what does it mean? I have two questions. I have a database in full recovery mode. Lets call transaction log an LDF file for short.


SQL Server allows me to restore to point in time in the past when restoring from database to itself. Does this even make sense? When I tried it did not yield expected results. Why is this even allowed if it doesn’t work? Can it have any practical purposes?
When playing with the LDF files I discovered a curiosity: Consider having a database in simple mode. Then I switch it to full recovery mode. Then I insert some 10000 rows into the database, check the LDF and find out that the LDF file size did not increase. When I take a full backup and then insert 10000 rows again into the database only then the LDF file increases. Is this some sort of optimization? NOTE: I had LDF file shrinked before both operations.


Thanks for all explanations.
The process of restoring a database only ever goes forward in time, never backward 1. So restoring a database to a point in time involves starting from a full backup, and then applying transaction log backups up to/containing the point in time to which you wish to restore.

The idea is that the ability exists to revert the database to a specific state that existed previously. This could be required if the entire database is lost (disaster) and it has to be reconstituted from nothing but the backups; or, "something bad" could have happened, either maliciously, or due to corruption, and it has to be restored in-place.

If you're having issues with getting this to work, we'll need more information about how you're going about doing it.
As mentioned in the comments, a database in FULL or BULK_LOGGED remains in SIMPLE mode internally until a full backup is taken, which gives a "base" from which the database can be restored. I've seen this called "pseudo-simple" mode.

The behaviour you saw with regards to the log file size was likely due to the above, although it depends on how the rows were INSERTed. In SIMPLE (and pseudo-simple) recovery, if the log file starts getting full, a CHECKPOINT will be initiated, which will internally free space in the log file as part of the process. So if the INSERTs were done in separate transactions, this would not grow the log. Doing all the INSERTs in a single transaction would grow the log, as the log records required to roll back the whole transaction cannot be cleared until the transaction commits or rolls back.


1 Not counting things like restoring in STANDBY mode, crash recovery, etc.