MSSQL

Overview

Storware Backup and Recovery supports full backup and restore operations for Microsoft SQL Server (MSSQL). Two restore modes are available:

  1. File-level restore – restores the .bak file to a specified directory on the source host.

  2. Direct database restore – restores the backup directly into a database instance on a remote MSSQL host.

All backup and restore operations are executed via PowerShell commands using Windows Remote Management (WinRM). WinRM must be enabled and properly configured on the target machine.

Adding an MSSQL Application

  1. Log in to the Storware Backup and Recovery WebUI.

  2. Navigate to: Applications → Instances

  3. Click the Create button to open the Create Application Definition window.

Fill Required Fields

General Tab

  • Name Provide a unique name for the configuration.

  • Choose Node Configuration Select the node configuration that will execute the operation.

  • Backup Policy Select the policy to assign to this application.

  • Command Execution Configuration Choose MSSQL configuration.

OS Credentials Select or create credentials for the Windows host.

SSH Access Not required.

4. Save and Configure Environment Variables

After saving the application configuration, define the following environment variables:

Environment Variables

Variable
Value
Description

VP_MSSQL_USER

string

MSSQL login username

VP_MSSQL_DBPASSWORD

string

MSSQL login password

VP_MSSQL_SCRIPTPATH

string

Path to the PowerShell script used for backup/restore operations (do not modify)

VP_MSSQL_DBNAME

string

Name(s) of databases to back up (comma-separated if multiple)

VP_MSSQL_SERVERINSTANCE

string

MSSQL Server instance name (e.g., MSSQLSERVER, .\SQLEXPRESS)

VP_MSSQL_STOREPATH

string

Path on the MSSQL host where the temporary backup file will be stored. Ensure this directory is used exclusively by Storware, as it is cleaned after every backup.

VP_MSSQL_COMPRESSION

yes / no

Indicates whether the backup should be compressed on the MSSQL server

VP_MSSQL_LOGFILE

string

Path to a log file for MSSQL backup and restore operations

Optional Restore-Specific Variables

Note: If variables are not displayed by default, you can also add them manually.

Variable
Value
Description

VP_MSSQL_DB_RESTORE

true / false

If true, restores the backup directly to a database instance. If false, restores the .bak file to the specified path only.

VP_MSSQL_OVERWRITE

true / false

If true, the database will be dropped (if it exists) and recreated from the backup file.

VP_MSSQL_KILL_CONN

true / false

If true, the script will attempt to terminate all active sessions before restore. Required when restoring a database that is currently in use.

Minimal Role-Based SQL Server Permissions (Recommended)

The following roles and permissions are recommended for the MSSQL user used in backup/restore operations:

Permission
Reason

dbcreator

Allows creation, deletion, and alteration of databases. Required for full restores and overwriting existing databases.

processadmin

Required to terminate user sessions (KILL) when VP_MSSQL_KILL_CONN=true.

VIEW SERVER STATE

Grants access to system views like sys.dm_exec_sessions, needed to list and terminate active sessions.

Restore

  1. Log in to the Storware Backup & Recovery WebUI.

  2. Navigate to: Applications → Instances.

  3. Locate the MSSQL application instance you wish to restore.

  4. From the Action menu, select Restore, or click the application name to open its details and choose Restore from the top menu of the detailed view.

  5. Select Restore to application

In the Restore Wizard, configure the following:

  • Select baclup location Select backup to restore

Click "Restore".

Minimal Role-Based SQL Server Permissions (Recommended)

The following roles and permissions are recommended for the MSSQL user used in backup/restore operations:

Permission
Reason

dbcreator

Allows creation, deletion, and alteration of databases. Required for full restores and overwriting existing databases.

processadmin

Required to terminate user sessions (KILL) when VP_MSSQL_KILL_CONN=true.

VIEW SERVER STATE

Grants access to system views like sys.dm_exec_sessions, needed to list and terminate active sessions.

Windows Remote Management (WinRM)

Note: It is required to open port 5986 and 15702 in the firewall on the MSSQL host side. Start and enable use of WinRM, enable Basic authorization and add Storware Backup & Recovery Node to the list of trusted hosts.

For Storware Backup & Recovery Node it is required in the firewall to open port 15702.

You can always use prepared scripts that perform the above-mentioned actions:

  • scripts/winrm_firewall_node.sh - execute the file on Storware Backup & Recovery Node

  • scripts/winrm_firewall_windows.ps1 - copy from node to target host from MSSQL instance and execute

  • scripts/winrm.ps1 - copy from node to target host from MSSQL instance and execute

PreviousApplicationsNextMySQL/MariaDB