Point in time recovery from the same database - what does it mean?

Question

I have two questions. I have a database in full recovery mode. Lets call transaction log an LDF file for short.

1. 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?

2. 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.

Answer 1

First, a bit about the log(LDF file) in SQL Server. This isn't necessarily a blow-by-blow recording of everything that has been done in the database, but instead is more of a "scratch pad" where work that is in-flight is stored. Periodically, this in-flight work is truncated from the log file when certain events occur (checkpoints).

In regards to your #2 question, this is a matter of understanding how switching between the different recovery models affect the log chain. Going between SIMPLE and FULL modes fundamentally affects how SQL Server handles the transaction log, so whenever you do this you need to take a full backup to "reset" that log chain so SQL Server knows how to properly handle the recovery model. In your example, SQL server was handling the log as if it was in SIMPLE mode immediately after the switch and it wasn't until you took your full backup and reset things that SQL server began to handle things differently.

If anything takes place that could cause your databases to stop and be inconsistent, the log becomes very important in bringing those databases back in a usable state. This recovery happens when the the database is brought back online. SQL Server will first bring up the databases, then apply any in-flight transactions (transactions that haven't been truncated) to those databases. This allows for your database to be consistent with the time that it was taken offline. However, we can't bring those databases back online to a specific point in time this way.

Taking this into account, we need to understand what point in time recovery is for SQL Server. When you recover a database, you are restoring from a database backup. If you want this recovery to be point in time, you need the following:

• The database you wish to recover was in FULL or BULKLOGGED recovery mode.
• You have a full database backup to start with.
• You have an unbroken series of transaction log backups taken after the full backup.

Using these, you can then use your database RESTORE syntax to restore the database to a specific point in time. The basic syntax is:

Restore your full

RESTORE DATABASE foo
FROM DISK='<physical file location of backup>'
WITH NORECOVERY

Restore your logs in order, repeat for each log up to the last one

RESTORE LOG foo
FROM DISK='<physical file location of backup>'
WITH NORECOVERY

Restore your last log, where you want to stop at a specific time

RESTORE LOG foo
FROM DISK='<physical file location of backup>'
STOPAT='<date/time of stopping point>'

Answer 2

1. The process of restoring a database only ever goes forward in time, never backward ¹. 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.

2. 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.

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

Answer 3

1. Point in time restore does work. If you have a perfectly good database at 11pm and a bunch of bad changes are made at 11.10pm, then a log backup is taken at 11:15pm you can restore you database to the point in time before the 11.10pm changes using your full/diff backup plus the log backup using a time parameter.
2. Don't worry about LDF file sizes unless their size becomes a space issue - just concentrate on making sure you have good backups.