Home > Storage > PowerStore > Databases and Data Analytics > Dell PowerStore: Microsoft SQL Server Best Practices > T-SQL snapshot backup and restore for Kubernetes
The T-SQL snapshot backup and restore process is streamlined on Kubernetes. All snapshot and volume orchestration is handled by K8s and the Dell PowerStore CSI driver. All operations can therefore be performed through the K8s control plane. This eliminates the need for interaction with PowerStore or the host operating system.
The following examples illustrate key points or differences for T-SQL snapshot backup. See the section Kubernetes SQL Server Deployment for complete examples of SQL Server deployment with PowerStore.
apiVersion: snapshot.storage.k8s.io/v1
kind: VolumeSnapshot
metadata:
name: mssql-data-snap
spec:
volumeSnapshotClassName: powerstore-snapshot
source:
persistentVolumeClaimName: mssql-data
Note: It is currently possible to snapshot a volume group with the PowerStore CSI driver, but not recover the volume group. Recovery of a volume group is done at the volume level. Therefore, this example operates at the volume level, not the volume group level for workflow consistency.
Due to the nature of how storage volumes work in Kubernetes, the concept of removing or altering storage volumes from running containers (in this case a SQL Server Instance) is not supported. Therefore, it is recommended to run a single database per containerized instance of SQL Server. This allows for the entire container/SQL Server instance to be dropped and re-created to accommodate storage addition and removal. The following examples assume a single database per SQL Server instance.
For more information about K8s Persistent Volumes, refer to the Kubernetes documentation about Persistent Volumes.
Note: To avoid an error, make sure that the storage size matches the size of the source volume.
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: mssql-data-copy
labels:
volume-group: db
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 5500Gi
storageClassName: powerstore-xfs-retain
dataSource:
name: mssql-data-snap
kind: VolumeSnapshot
apiGroup: snapshot.storage.k8s.io
RESTORE DATABASE SnapTest FROM DISK = /var/opt/mssql/backup/SnapTest_SQLBackupFull.bkm' WITH FILE=1, MOVE 'SnapTest1' to '/var/opt/mssql/data1/snaptest1.mdf',MOVE 'SnapTest2' to '/var/opt/mssql/data2/snaptest2.ndf',MOVE 'SnapTest_log' to '/var/opt/mssql/log/snaptest.log', METADATA_ONLY,RECOVERY
RESTORE DATABASE SnapTest FROM DISK = ' /var/opt/mssql/backup/SnapTest_SQLBackupFull.bkm' WITH FILE=1, , MOVE 'SnapTest1' to '/var/opt/mssql/data1/snaptest1.mdf',MOVE 'SnapTest2' to '/var/opt/mssql/data2/snaptest2.ndf',MOVE 'SnapTest_log' to '/var/opt/mssql/log/snaptest.log', METADATA_ONLY,NORECOVERY