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>'
No your theory is wrong.
Dirty pages can be written to disc even if the transaction is not yet committed. However it is ensured that they cannot be written before the last transaction log entry that modified the page has been written to disc.
The transaction log records do contain sufficient information both for redo and undo (except for in tempdb
where only undo is necessary). If you decide to rollback the transaction then nothing is deleted from the log. Instead compensation log records are written to the log indicating this.
Best Answer
The difference is that what you call "standard commands" have implicit transactions (as in "not explicit" and not real implicit transactions which mean something different), so every time you issue an
INSERT
command without an explicit transaction, it will open a transaction, insert the data and automatically commit. This is called an autocommit transaction.This is also why you can't rollback this
INSERT
: it's already committed. So the rule is the same as explicit transactions: you can't rollback once they've been committed.You can see what I mean directly from inside SQL Server.
Microsoft ships SQL Server with a DMF called
sys.fn_dblog
that can be used to look inside the transaction log of a given database.For this simple experiment I'm going to use the AdventureWorks database:
Here I'm doing two inserts: one with and one without an explicit transaction.
In the log file you can see that there's absolutely no difference between the two:
The red one is the
INSERT
within an autocommit transaction and the blue one is theINSERT
with an explicit transaction.As for the 3rd party tools you mention, yes they analyse the database log and generate normal T-SQL code to "undo" or "redo" the operations. By normal I mean they don't do anything special other than generate a script that will have the effect of doing exactly the opposite of what is in the log file.