This post was originally published on Pure Storage
This blog on snapshot backups originally appeared on www.nocentino.com. It has been republished with permission from the author.
Traditional SQL Server backups can struggle with large databases, resulting in longer backup times and resource contention. T-SQL snapshot backup, a new feature in SQL Server 2022, addresses these challenges by allowing storage-based snapshots to be coordinated through T-SQL. This feature delivers faster, more efficient backups, especially for large-scale environments with the most aggressive of recovery objectives.
Anatomy of a Full Backup
Before we start learning about T-SQL snapshot backup, let’s establish what a backup in SQL Server is. When a backup is initiated in SQL Server, several things occur. Follow along in Figure 1 below; here, you see a database with database files on Disk A and a transaction log file on Disk B.
Let’s go through the process:
A checkpoint runs, stabilizing the database on disk by writing any dirty pages and marking a starting recovery point in the transaction log. The database engine marks the start of the backup in the transaction log. A backup file(s) is created with a backup header that describes the file’s contents, the database’s file layout, and the database’s status at the time
— Read the rest of this post, which was originally published on Pure Storage.
