backupsql servertransaction-log
Like I am trying to ask that, suppose we run a full backup using GUI or T-SQL, which file SQL Server takes backup of i.e, only data file or log file as well.
I have read that it takes active transaction portion (Log File) of SQL Server as well. please confirm.
If yes, then what will happen to a transaction that was running at that time, and committed next, logs of the transaction will there on backup or not.
how will sql server revert back to it, if the database fails.
The last LSN of the FULL BACKUP should match the Last LSN of the log backup. This will be your first log backup and then after that the consecutive log backups will be having A.LAST_LSN = B.FIRST_LSN --> where B = log backup taken immediately after A.
From BOL :
Now look at the error you are getting :
The log in this backup set begins at LSN 421814812000000025600001, which is too recent to apply to the database. An earlier log backup that includes LSN 421787067000000013800001 can be restored.
So, essentially, you have a gap in the LSN and hence you cannot restore your log backup.
I would suggest to use Restore Gene - to automate DB restores. You can even use it using PowerShell to fully automate your restores. (I have used it and its very useful.)
Refer to my answer to better understand how full backup and LSN works.
Sean Gallardy provided the information that led to the answer when he commented about the Backup forcing a Checkpoint:
Best Answer
A Full Backup contains a copy of all the database pages, and all the log records from the LSN at which the first database page was copied through the LSN at which the last database page was copied.
On restore the database pages are copied into the new database files, then the log records are replayed to to bring the database up to the point-in-time at which the backup completed.
If you restore WITH RECOVERY, then any transactions that were open at the time the backup completed are rolled-back and the database is brought online.
If you restore WITH NORECOVERY, then you can restore additional Log Backups to restore the database to a later point-in-time.