Backup Process

Recently I had a question: What will happen to the transactions executed while a backup job is in progress? This question prompted me to learn more about backup process. Additionally I was asked to give training to a set of administrators so I wanted to be prepared with the answer before they ask the question.

There are a lot of articles in many sites about backup and restore and this article summarizes what I learned from those articles.


Backup job, when it starts it issues a checkpoint command forcing SQL Server to write all data pages to be written to disk. That includes both committed an uncommitted transactions. But committed transactions will have corresponding log entry/entries, while uncommitted entries will not have any entries in log file. In other words, at any given time, there is a good possibility of having both committed and uncommitted entries in the data files.
Then SQL Server will start reading all the relevant pages, and write them to disk. When a full backup is performed, all non empty data pages will be read and written to the backup file/files. If it is a differential backup, SQL server will read the differential changed map (DCM) page and identify the extends modified after the last full backup and write them.
Now the scenario is someone started a transaction and it changed some data pages. As expected, some of the changes may be already saved in data pages and copied to the backup process. Some more would have saved in data pages, but as they may not be in the backup file as by the time those entries were saved backup process had read the previous state of the pages.
When backup process reaches the end of the data pages it starts reading the log pages. And it will read all the log data available.
So now SQL Server has all the data and log pages it needed, which includes the particular transaction as well.

The sequence of events is shown below.


I hope this will be useful for all of you. In the next blog (or a subsequent blog), I’ll try to write about restore process