1

When taking a backup, does SQL Server first read the data into memory before writing it to the backup file, or does it read the data directly from the data files and write it directly to the backup file? I can't find any information about this, but I suppose it reads directly from the data file and writes to the backup file. Since from resource monitor, I saw this:

enter image description here

As you can see, it SQL Server reads from the mdf file and writes to the backup file. But since I can't find any information about it, I just want to confirm.

2
  • Just an FYI, if SQL Server was reading from the MDF into Memory, and writing from Memory to the backup, I believe Resource Monitor would look just the same, since the same files are being read from and written to, regardless if Memory is acting as the intermediary to get there.
    – J.D.
    Commented Jan 29, 2023 at 14:07
  • 1
    @J.D. Yes you are right! I haven't thought of it. Commented Jan 29, 2023 at 14:36

2 Answers 2

1

When taking a backup, does SQL Server first read the data into memory before writing it to the backup file, or does it read the data directly from the data files and write it directly to the backup file?

Let me tell you how that happens. See this SE Thread, not directly related to your question but has something which could interest you. Below happens when backup operation starts.

When a backup starts it creates a series of buffers, allocated from the memory outside the buffer pool. The target is commonly 4MB for each buffer resulting in approximately 4 to 8 buffers. Details about the calculation are located in:

The buffers are transitioned between the free and data queues. The reader pulls a free buffer, fills it with data and places it on the data queue. The writer(s) pull filled data buffers from the data queue, process the buffer and return it to the free list.

You get a writer per backup device, each retrieving from the data queue. So a a backup command with four(4) to disk specifications will have four writers and a reader. The reader uses async I/O so it can keep up with the writers.

You can enable trace flags 3213 and 3605, both are undocumented so please use it on test environment, and see what interesting message is dumped in SQL Server errorlog. Something Like below would appear.

Please refer to below 2 blogs from Bob Dorr

7

If you're asking whether the database is read into the database page cache and then written to the backup file, the answer is no. The data in the files is copied to the backup, but since the files are possibly behind the state of the database in memory, the log records sufficient to recover the database to the point-in-time at which the backup completes are also copied into the backup.

Then during restore, the database goes through redo and (optionally) recovery using these log records.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.