June 30, 2005 at 4:37 am
I am 99% sure I know this answer but I've been getting in a few "heated" arguments as of late with some stubborn people. I'd appreciate if anyone can help settle the score:
The question is, what happens to incoming transactions when a full backup is occuring. Do transactions get committed into the data file as the full backup occurs or do they wait untill the backup is completed. I know that you cannot backup the transaction log during the full backup but the question is do the data files (mdf, ndf) get updated while a full backup is occuring.
I appreciate the help.
June 30, 2005 at 6:09 am
During a backup the transactions must get commited else a large database would quickly grind to a halt, but they don't. I believe the backup process notes the current state of the log when it starts and then does a cleanup phase at the end to pick up changes which occured during the main backup phase.
Rick.
June 30, 2005 at 7:08 am
A commit has nothing to do with writing to data files. A commit is only written to the transaction log. Then when a checkpoint occurs, the transactions older than the oldest active transaction are written to the data files and the log space can be reused. So there is of course no problem for a transaction to commit during backup.
June 30, 2005 at 10:29 am
If I may... During a full backup all data pages (both "data" and "log") are fully backed up. Unlike a Backup Log operation, the transaction log is NOT truncated even though it is backed up. This is why a transaction log backup cannot occur at the same time.
Even though BOL doesn't state this, the backup operation must force a CHECKPOINT. To make a minor correction to Chris' comment...CHECKPOINT forces ALL dirty pages (pages that have been changed) in memory to be committed to disk (again, this is both data and log pages). Maybe I shouldn't use the word committed here since this is very different from a COMMIT operation and in fact is completely independent from any transactions.
Full backups are not concerned with getting only committed transactions and as a result if there were uncommitted transactions in the database at the time the backup completes they will be rolled back as part of the recovery process upon restoration of the backup; that is unless you specify the norecovery option and apply subsequent log backups.
HTH
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 1, 2005 at 2:46 am
Is it like so then?
During a full backup, all new entries into the transaction log file are not written to the data file until the full backup completes. Which implies that if you have a large and active database to backup, the transaction log will grow significantly during the backup?
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
July 1, 2005 at 9:13 am
That was really the point of the question. Say you have a 5 hour long full backup and 4 hours in, the disk containing your log file goes down. Have you lost 4 hours worth of transactions or are some of those transactions now contained in the data file? Can checkpoints occur while a full backup is going on?
July 1, 2005 at 9:13 am
I don't know if there is a specific order in which the data or transaction log pages are written by default. However, since the backup is an online operation if there are pages that are locked or changed after they have been written, the backup will loop back around and get them before the operation completes. Remember that the backup operation doesn't really know or care about transactions, it just copies the data and log pages.
I suppose that if you had a large database that was also very busy the log could grow significantly if the backup took hours and was run during a busy time. In cases like this you would probably be smart to do filegroup backups or differential backups and be very smart about the scheduling of your full backups.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
July 1, 2005 at 9:23 am
Not only can checkpoints occur, logic would dictate that they would HAVE to. The full backup has to be consistent when it finishes and it couldn't be consistent if there were a bunch of dirty pages in memory that were never written to disk and by extension to the backup.
In the situation you describe though, the backup that is in process would be useless. It wouldn't be able to complete its operation and would not be consistent. You would need to restore your last full backup and apply the log backups since. If you totally lost the data on your disks and could not recover it, yes you would loose all work in the log file that had not been backed up. So best case you would loose 4 hours of work assuming that you did a log backup right before the full backup kicked off. This underscores the importance of having a solid IO system for your transaction logs.
Don't confuse checkpoints with backups though. Checkpoints don't have much to do with backup and restore operations. They do affect recovery but that is different. Every time SQL Server starts a database it goes through a recovery process. Checkpoints merely ensure that all pages in memory are written to disk. It does not truncate the transaction log and it does not backup anything.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply