Microsoft SQL Server

The MSSQL integration backs up the Program Files folder used by Microsoft SQL Server while the database engine keeps running, and restores it to a Windows host. It is built on top of the VSS integration and shares its parameters and behaviour.

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 Microsoft SQL Server 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.

Host key verification

The MSSQL integration verifies the Windows host against the known_hosts file of the account running plakar. The host key must be verified and recorded in that file before running a backup. See SSH host key verification documentation for more information.

How a backup works

Plakar connects over SSH and requests a Volume Shadow Copy of the volume holding the SQL Server installation directory. It then reads the files from that read-only copy over SFTP and stores them in a Kloset store with encryption and deduplication. SQL Server keeps running during the backup.

How a restore works

A restore reconstructs the installation directory from a snapshot and writes it back to the Windows host over SFTP.

SQL Server must be shut down before restoring, since its services hold the destination files open while they run. Stop every SQL Server service on the Windows host first. 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.

Configuration

Both connectors use the mssql protocol. The location points at a Windows host, drive, and path. The default path covers the whole installation, and a subdirectory targets a single instance.

Backup flow

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

Restore flow

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

Shared configuration

The same options apply to the source and destination connectors.

Example

Back up a SQL Server installation:

# 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/"

Restore a snapshot, after stopping the SQL Server services:

# 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