The key here is understanding the relevance of the LSN (log sequence number) and what is stored in the transaction logs in the various Recovery Model settings.
There are a number of columns that store information regarding the LSN in the msdb.dbo.backupset table in the msdb database.
msdb.dbo.backupset.first_lsn
msdb.dbo.backupset.last_lsn
msdb.dbo.backupset.database_backup_lsn
msdb.dbo.backupset.checkpoint_lsn
msdb.dbo.backupset.differential_base_lsn
msdb.dbo.backupset.fork_point_lsn
A simplified explanation:
Full Recovery Model
In the Full Recovery Model a transaction log backup will always contain a sequence of transactions. The fist transaction in the transaction log backup file is noted in the first_lsn in the backup history tables and the last transaction in the transaction log backup will be noted as the last_lsn in the backup history tables in the msdb database.
Once a transaction log backup has been performed the system will create a checkpoint and this will be duly noted in the checkpoint_lsn column of the backup history table.
Once a chekcpoint has been set and there a no open transactions in the transaction log, then the Transaction Log file can be purged to make room for new transactions.
If the Transaction Log file of database has an open transaction and there is no more free space, then the TLOG file will have to grow (or the transaction killed / rolled back to free up space in the TLOG file).
Reference: The Transaction Log (SQL Server) (Microsoft | SQL Docs)
Simple Recovery Model
In the simple Recovery Model there is no need for a Transaction Log backup, because committed transactions are automatically written to the database once a commit has been performed and an automatic checkpoint has been set.
There is no need to store the committed transaction in the TLOG file, as the modifications are written to the database on commit.
However, you can still encounter the situation where an open transaction can result in an expanding transaction log file. Same reason as above:
If the Transaction Log file of database has an open transaction and there is no more free space, then the TLOG file will have to grow (or the transaction killed / rolled back to free up space in the TLOG file).
Difference So Far
Full Recovery Model : Transactions are written to the database after the commit and will be purged from the TLOG file after a Transaction Log backup and Checkpoint. No TLOG backup = No purging of TLOG file!
Simple Recovery Model : Transactions are written to the database after COMMIT and Checkpoint and then automatically purged from TLOG file.
Answering Your Questions
A normal transaction log backup contains all the transactions generated since the previous log backup (or since the first ever full backup if it’s the first ever log backup for the database).
This is always the case.
However, in the above scenario, when I take a log backup after the differential backup (not a full backup), then the log backup contains transactions from the last log backup or from the last full backup?
No. The TLOG backup is always a first_lsn and last_lsn set of modifications. However, when you alter your Recover Model from Simple to Full you have to re-initiate the correct backup logic / log chain:
(emphasis mine)
After switching from the simple recovery model
Immediately after switching to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
NOTE: The switch to the full or bulk-logged recovery model takes effect only after the first data backup.
Schedule regular log backups, and update your restore plan accordingly.
IMPORTANT!!!! Back up your logs!! If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space!
Reference: View or Change the Recovery Model of a Database (SQL Server) (Microsoft | SQL Server Docs)
So while you do change the Recovery Model of a database in the GUI or via script, it only actually takes effect after the first FULL or DIFF backup!
