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.
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.
Full Backup : Backs up all the data pages.
Differential Backup : Backs up all the extents which have been modified since the last full backup.
Log backup : A transaction log backup allows you to backup the active part of the transaction log. Log backup can only work when the recovery model is Full/Bulk-logged and there has been a full backup taken . It has all transactions logged and is a very important part of point in time recovery.
