One of my favourite features of SQL Server 2022 was the T-SQL Snapshot Backups. The ability to leverage the snapshot capabilities of modern storage arrays to take application consistent snapshots of our databases is a game changer for anyone dealing with very large databases and struggling to hit their RPO within their RTO.

However, application consistent snapshots require the write IO of the database to be quiesced/frozen/stunned…and (rightly so) this can make DBAs nervous.

SQL Server 2025 introduced the ability to take full and differential backups of secondary replica databases in availability groups…but what about the T-SQL Snapshot Backups? Wouldn’t it be great if we could take those on the secondary and not have to stun IO on the primary database?

Alas, if we try to take a “normal” snapshot backup of a secondary replica: –

ALTER DATABASE [tpcc] SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON

We’ll get the following error: –

Msg 1468, Level 16, State 3, Line 5
The operation cannot be performed on database “tpcc” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.

OK, I guess that makes sense…

However, I had a conversation with two awesome people (Sean Gallardy and Allan Hirt) at Microsoft and it turns out that you CAN take a snapshot of a secondary replica database!

There are a couple of conditions though…database in the secondary replica has to be read only, COPY_ONLY must be specified, and we have do to it a the SERVER CONFIGURATION level.

So if we try: –

ALTER SERVER CONFIGURATION SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (tpcc), MODE = COPY_ONLY);

Ah ha! IO (write IO) is frozen on the database!

Database ‘tpcc’ acquired suspend locks in session 75.
I/O is frozen on database tpcc. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
Database ‘tpcc’ successfully suspended for snapshot backup in session 75.

We can now take a snapshot of the volumes on our storage array and then take a METADATA_ONLY backup: –

BACKUP DATABASE [tpcc] TO DISK = 'E:\SQLBackup1\tpcc.bkm' WITH METADATA_ONLY;

This releases the stun and we have our application consistent snapshot!

I/O was resumed on database tpcc. No user action is required.
Database ‘tpcc’ released suspend locks in session 76.
Database ‘tpcc’ originally suspended for snapshot backup in session 76 successfully resumed in session 76.
Processed 0 pages for database ‘tpcc’, file ‘tpcc’ on file 3.
BACKUP DATABASE successfully processed 0 pages in 0.003 seconds (0.000 MB/sec).

Of course, if anything goes wrong and we need to abort, we can run: –

ALTER SERVER CONFIGURATION SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF
(GROUP = (tpcc));

Which will resume IO!

I/O was resumed on database tpcc. No user action is required.
Database ‘tpcc’ released suspend locks in session 76.
Database ‘tpcc’ originally suspended for snapshot backup in session 76 successfully resumed in session 76.

But there we have it, a way to take application consistent snapshots of databases without having to worry about the effects of a stun on our applications.

Thanks for reading!