We all know what SQL Server's transaction log records information about the transaction. But what, in a full recovery model, is actually recorded in the transaction log? You'll say: THE TRANSACTIONS!
Ah, but there could be several ways for SQL Server to record the transactions. So the question is, how does it record the transactions?
Here are the following ways I envisage it is working:
It merely makes a record of which statement was run. In this case, the transaction log contains the actual text of the DML statement, something like this:
"INSERT INTO test_database.dbo.Test_People (FirstName,LastName) VALUES ('James' ,'Smith')"
In this case the transaction log contains the whole of what is quoted above.
Then, in a transaction log restore (after a full backup restore), SQL Server will actually execute the statement contained in the transaction log, against the database, to update it.
- It stores a record of the entire row of data, with all its values (even those values that were not updated), and the date at which the row was modified/inserted.
Then, in a transaction log restore (after a full backup restore), SQL Server will copy the row data contained in the transaction log to the database, either inserting it or overwriting the same row in the table with the data in the transaction log.
- It stores a record of the entire data page, with all its values (even those values that were not updated), and the date at which the data page was modified/updated/deleted.
Then, in a transaction log restore (after a full backup restore), SQL Server will copy the row data contained in the transaction log to the database, either inserting the page or overwriting the page with the same pageID.
Or, perhaps it is none of the above. Does anybody know?