Full Backup Scenarios

Transaction A 1:00AM started and the full backup began at 1:05AM, Transaction A completed at 1:10AM and made some changes on Transaction A at 1:15AM and the backup completed at 1:20AM Transaction A was committed at 1:30AM. is full contains the changes made on transaction A?

In the scenario you described:

• Transaction A started at 1:00 AM.
• Full backup began at 1:05 AM.
• Transaction A completed at 1:10 AM.
• Changes were made on Transaction A at 1:15 AM.
• Backup completed at 1:20 AM.
• Transaction A was committed at 1:30 AM.

The full backup that completed at 1:20 AM would not contain the changes made on Transaction A at 1:15 AM. Here's why:

1. Backup Process: When a full backup starts, it reads the database pages as they exist at the start of the backup operation. Any changes made to the database after the backup process begins are not captured in the backup file.
2. Backup Timestamp: The backup timestamp represents the point in time at which the backup process started. In your scenario, the backup process started at 1:05 AM and completed at 1:20 AM. It reflects the database state as of 1:05 AM.
3. Transaction Commit: Transaction A was committed at 1:30 AM, which is after the completion of the full backup at 1:20 AM. Therefore, the changes made on Transaction A at 1:15 AM and committed at 1:30 AM would not be included in the full backup taken at 1:20 AM.

In summary, a full backup captures the database state as of the start of the backup process. Any changes made to the database after the backup process begins and before the backup completes are not included in the backup file. Therefore, the changes made on Transaction A at 1:15 AM would not be part of the full backup completed at 1:20 AM.

 

Transaction A 1:00AM started and the full backup began at 1:05AM, Transaction A completed at 1:10AM and made some changes on Transaction A at 1:15AM and the backup completed at 1:20AM Transaction A was committed at 1:18AM. is full contains the changes made on transaction A?

In the scenario you described:

• Transaction A started at 1:00 AM.
• Full backup began at 1:05 AM.
• Transaction A completed at 1:10 AM.
• Changes were made on Transaction A at 1:15 AM.
• The backup completed at 1:20 AM.
• Transaction A was committed at 1:18 AM.

Since Transaction A was committed at 1:18 AM, which is before the backup completed at 1:20 AM, the changes made on Transaction A at 1:15 AM would indeed be included in the full backup.

Here's why:

1. Transaction Commit Time: Transaction A was committed at 1:18 AM, and the full backup completed at 1:20 AM. This means that at the time of the backup completion, Transaction A had already been committed, and its changes were part of the database state captured by the backup process.
2. Backup Timestamp: The full backup captures the database state as of the start of the backup process and includes all committed transactions up to that point in time. Since Transaction A was committed before the backup completed, its changes are included in the backup.

Therefore, in this scenario, the full backup taken at 1:20 AM would indeed contain the changes made on Transaction A at 1:15 AM because those changes were committed before the backup completed.