SQL Server with Mr. Denny

Jul 3 2013   7:00AM GMT

Backups & the Buffer Pool



Posted by: Denny Cherry
Tags:
Back To Basics
Backup & recovery
BACKUP DATABASE
SQL Server
Storage

    As we know with Microsoft SQL Server everything is processed from disk and loaded into the buffer pool for processing by the query engine.  So what happens to the buffer pool when backups are taken?

    The answer is that nothing happens to the buffer pool.

    When SQL Server is backing up data from the disk, SQL Server simply takes the data from the data files and writes it to the backup file.  During the backup process the dirty pages are written to the disk by the checkpoint process being triggered by the backup database process.

    Because the backup process simply reads the data files and writes them to the backup location there’s no need to cache the data in the buffer pool as this data isn’t being queried by a normal SQL query.

    Denny

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: