Microsoft SQL Server

The MSSQL integration backs up the Program Files folder used by Microsoft SQL Server while the database engine keeps running. It is built on top of the VSS integration and shares its parameters and behaviour. Plakar connects over SSH, requests a Volume Shadow Copy of the volume holding the SQL Server installation directory, then reads the files from that read-only copy over SFTP.

Restoring requires SQL Server to be shut down first, since its services hold the destination files open while they run.

The integration includes two connectors:

Requirements

• A Windows host running Microsoft SQL Server, with OpenSSH Server installed, running, and reachable.
• An administrative user account on the Windows host to run the backup as.
• An SSH key authorized for that account.

Typical use cases

• Backing up a SQL Server installation without taking the database engine offline.
• Recovering an installation onto the original host or a replacement machine.
• Backing up the data directory of a single instance rather than the whole installation.

Installation

The MSSQL integration is distributed as a pre-built package only. Unlike most Community integrations, it has no public source repository, so plakar pkg build cannot produce it. It remains free to install with a Plakar account. See source availability for how this differs from the other integrations.

Install the MSSQL package:

$ plakar pkg add mssql

Verify installation:

$ plakar pkg show

To list, upgrade, or remove the package, see managing packages guide.

Setting up SSH access on the Windows host

The connectors reach the Windows host over SSH, so OpenSSH Server must be running with a dedicated key authorized for the administrative account Plakar connects as. See Setting Up OpenSSH Server on Windows for the full procedure.

Configuration options

The same options apply to the source and destination connectors.

Source connector

The source connector requests a shadow copy of the volume holding the SQL Server installation, then reads the files from that snapshot over SFTP and stores them in a Kloset store with encryption and deduplication.

flowchart LR

subgraph Source["Windows Host"]
  VSS["VSS Snapshot"]
  FS["Microsoft SQL Server
Program Files"]
end

Plakar["Plakar"]

Via["Request snapshot over SSH
and read files over SFTP"]

Store["Kloset Store"]

FS --> VSS --> Via --> Plakar --> Store

Configure

# Configure a SQL Server installation as a backup source
$ plakar source add sqlserver location="mssql://Administrator@192.168.1.42/C:/Program Files/Microsoft SQL Server"

# Back up the source to a Kloset store
$ plakar at /var/backups backup "@sqlserver"

To back up the data directory of a specific instance, point the location at the corresponding subdirectory:

$ plakar source add sqlserver location="mssql://Administrator@192.168.1.42/C:/Program Files/Microsoft SQL Server/MSSQL17.MSSQLSERVER/"

Destination connector

The destination connector reconstructs the installation directory from a snapshot and writes it back to the Windows host over SFTP.

flowchart LR

Store["Kloset Store"]

Plakar["Plakar"]

Via["Push files over
SFTP"]

subgraph Destination["Windows Host"]
  FS["Microsoft SQL Server
Program Files"]
end

Store --> Plakar --> Via --> FS

Stop every SQL Server service on the Windows host before restoring. Any service left running keeps file handles open and the restore fails with an access denied error. See destination configuration for the full list of services and how to confirm they are stopped.

Configure

# Configure a destination corresponding to the backup source
$ plakar destination add sqlserver location="mssql://Administrator@192.168.1.42/C:/Program Files/Microsoft SQL Server"

# Restore a snapshot to the Windows host
$ plakar at /var/backups restore -to "@sqlserver" <snapshot_id>

See also

• Windows VSS integration
• Setting Up OpenSSH Server on Windows
• Microsoft SQL Server in Plakar Control Plane
• Managing packages