Press "Enter" to skip to content

App-Consistent and Crash-Consistent Snapshots in SQL Server

Andrew Pruski talks snapshots:

When we talk about snapshots of SQL Server there are two types, application consistent snapshots and crash consistent snapshots.

Application consistent snapshots require freezing IO on a database allowing for a checkpoint to be performed in order to write all dirty pages to disk.

In the past, application consistent snapshots relied on the third party software to call the SQL Writer service but now with SQL Server 2022 T-SQL snapshot backups we have the ability to use T-SQL commands to freeze IO on a database in order for us to take an application consistent snapshot of the database.

Read on for more detail on the topic. With SQL Server 2022, I’ve softened a bit on using snapshots for data retention—prior to that, I was not a fan of the idea.