Home > Storage > PowerFlex > White Papers > Microsoft SQL Server Data Protection using Dell PowerFlex Snapshots > SQL Server 2022 T-SQL snapshot backup feature
Before Microsoft SQL Server 2022, taking consistent storage backup was a combination of SQL Server and windows volume shadow copy service (VSS). VSS works on the block level of the file system. After SQL Server commits to the disk, it also notifies to the VSS writer service. VSS writer stores the change in the temporary location and guarantees a consistent backup in the form of shadow copy. These copies can be taken to another disk or tape as required. A few of the limitations of this method are:
Microsoft SQL Server 2022 provides an alternative to the challenges above by providing an integrated T-SQL (Transact-SQL) snapshot backup feature. This method does not require VDI or VSS APIs and can also work with Linux and containers in contrast with the older SQL version that does not support it.
Following are the high-level steps of the new T-SQL snapshot feature of Microsoft SQL Server 2022:
ALTER DATABASE sample1 SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
ALTER SERVER CONFIGURATION SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (sample1, sample2));
Group clause accepts multiple databases.
BACKUP DATABASE Sample1 TO DISK = 'path to store the metadata backup’' WITH METADATA_ONLY;
Details on the syntax and options for the SQL Server T-SQL snapshot are available here.