what really happens during the full backup

Question

There are my understanding about full backup please correct me

 

1. Lock database blocking all transactions
2. Place a mark in the Tran log
3. Release the database lock
4. Back up all data pages in the database
5. Lock database, mark transaction log, unlock database
6. Write only *committed* transactions between the two marks to the full backup file (but they also stay in the log file)  . Uncommited transactions are not written to the full backup.

 

Questions: 

 

a)  How about if transaction are not yet committed? I believe they will not be in the full backup

 

b)       Full backup does not affect transaction log. Transaction log is not backed up during the full backup and log is not emptied.

      c)   Transaction log can be backed up simultaneously during the full backup process?

Answer 1

No, that is not really how backup is implemented, more like:

1. Perform a checkpoing in the database
2. Copy all extents from the database files to the backup media
3. Copy all log records that was produced uding the backup to the backup media

Based on above, we can answer your questions:

a) The modifications on the pages might or moght not be in the backup of the pages, but when you restore, then SQL Server uses the log records that is also in the database backup to make sure those modifications are rolled back.

b) Ldf file is not emptied, that is corret. But as stated above, the database backup do imclude the log records produced during the db backup was taken.

c) 2000 and earlier: no. 2005 and later: yes.

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 2

Tibor,
can you clarify

>>Copy all log records that was produced during the backup to the backup media

does that mean everything that written to T Log during the backup process is written to backup media.

I believe there is a checkpoint at the very end of the backup process??

Answer 3

<<does that mean everything that written to T Log during the backup process is written to backup media.>>

Correct.


<<I believe there is a checkpoint at the very end of the backup process??>>

No, I have never seen such statement, nor is that mentiones here:

http://msdn.microsoft.com/en-us/library/ms188748.aspx

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 4

Tibor, I just found this

http://www.sqlskills.com/BLOGS/PAUL/post/Debunking-a-couple-of-myths-around-full-database-backups.aspx

Answer 5

Uncommited transactions are not backed up during full backup because they do not affect any current status of the database.

Part of the transaction log is backed up during full backup to help during recovery,bearing in mind that it is the transaction log that contains all the transactions that help during start up!

Answer 6

Uncommited transactions are not backed up during full backup because they do not affect any current status of the database.

Uncommitted transactions must be backed up - all active parts of the log between the backup start and the backup end must exist in order for the full backup to be transactionally consistent.

Consider this situation:

1. A transaction is started and updates a row on a page (original value of "OLD", new value is "NEW"). This change occurs in memory.

2. A backup starts, and runs for 30 minutes. Due to the checkpoint at the start of the backup, the updated row ("NEW") is written to disk, and the backup contains the "NEW" row.

3. After the backup completes, the transaction is rolled back, reverting back to "OLD".

At the point in time when the backup completes, the database's transactionally consistent state is to have the row rolled back ("OLD"), as it wasn't committed before the end of the backup. As the current value of the row during the backup was "NEW", the uncommitted transaction must be stored in the full backup in order to perform the rollback.

When the backup is restored, the active portion of the log is replayed, and as there is no commit for the transaction (the rollback didn't occur until after the backup finished), the "NEW" row is rolled back to "OLD". If the uncommitted transactions weren't stored in the backup, this couldn't happen, and the database would be inconsistent.