SQL Server Performance Forum – Threads Archive

Full Backup

I am running in full recovery mode with tran log backups every two hours. Hypothetically
2:00PM T-log backup
3:00PM Full backup starts
3:15PM Insert occurs
3:30PM Full backup ends
3:45PM Disk failure 1. Will the above insert be recoverable from the full backup?
2. Once a full backup starts and committed transactions occur during the full backup, what is recoverable?

---

The above insert would not be available. If the log file was still around, you could use a tool like Log Explorer to recreate it. Otherwise, you would be out of luck. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Checkpoints minimize the portion of the log that must be processed during a full recovery of a database. During a full recovery, two types of actions must be performed: – The log may contain records of modifications that were not flushed to disk before the system stopped. These modifications must be rolled forward. – All the modifications associated with incomplete transactions (transactions for which there is no COMMIT or ROLLBACK log record) must be rolled back. The active portion of the log must include every part of all uncommitted transactions. An application that starts a transaction and does not commit it or roll it back prevents SQL Server from advancing the MinLSN. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

So when you execute a full backup with users still logged in, and users are still making updates during the backup, does that mean the full backup only includes committed transactions that took place prior to the time the full backup statement was executed?

---

YEs that is what CHECKPOINT performs the transactions maintenance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>