SQL 2022 TSQL snapshot backups!

 

SQL 2022 now actually supports snapshot backups!  More specifically, T-SQL snapshot backups.

Of course this is hardware-dependent.

Here are my key take-aways:

No longer needs VSS service to get it done.

It will write out a meta data file that does include info that will be helpful with restoration.  It has the snapshot details to allow SQL Server to bring the database back up like it was a full backup restore.

It means that if the appliance is handling the backup, they will be orders of magnitude faster and SQL Server will acknowledge the backup.  SQL will remove the incremental flags on the chain.

Backup groups will allow us to backup several databases at once or entire server (all databases).  Snapshot all the databases at a point in time.

Should not use differential backups with this feature.  Should take more frequent full backups.  The snapshots replace the diff backups.  

Can be used with transaction log backups.  So for instance, the snapshot runs, then 2 trans log backups run, then corruption.  Can restore the snapshot, then both log backups plus tail log.

Prior versions of SQL Server + snapshots would not really get you to point in time recovery from snapshots.   There were a variety of circumstances that could lead to non-recoverable databases.  The additional of the bkm file in this version is the key.

The following command

SUSPEND_FOR_SNAPSHOT_BACKUP=ON

writes out to bkm file with metadata only. (backup meta data)

It is not writing a bak file. Compression and encryption is handled by the appliance.

It also works for snapshots on SQL on Linux.  Containerized SQL solutions get this too.

You have to have the right hardware to make this work such as dell's solution