How SQL Server Full Backup Works
How SQL Server Full Backup Works
By : Kasim Wirama, MCDBA, MCITP, MVP SQL Server
I would like to share with you about inner working of SQL Server full backup in this posting. As you probably know that full backup in SQL Server will backup data into device. Why don’t I say backup to file? Let’s read through the remainder of this article.
Device in terms of SQL Server backup is backup media. It might be tape or file. A device might contain one or more files inside. You can backup multiple times into same device or you want to correspond one to one relationship between backup file and device. Honestly speaking, it is easy to find out or track backup file with one to one relationship.
From SQL Server 2005, two backup activities could run parallel. It is good capability but it is necessary and also creates high I/O demand.
Now, what happened when full backup runs? Here is the steps SQL Server will be doing :
1. Checkpoint database means that SQL Server writes all dirty pages (pages that have changed since loaded into memory) to disk to ensure only committed transactions included in full backup.
2. Mark beginning of transaction log to validate backup file(s) whether it could be used in restore process.
3. Read data file and write into backup device.
4. Mark end of transaction log when full backup has finished.
At point 3 I mention data file, why don’t I mention entire database (including transaction log file)? Full backup doesn’t include transaction log file, meaning that transaction log file is neither truncated nor backed up. Full backup doesn’t backup server login and job as well.
When you have only full backup database, you just only restore last transaction which is included on the last full backup device.
Grasp this basic important understanding how full backup works as DBA basic task, an exceptional data guard.