# 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_WINAUTH` | `true/false` | Use Windows authentication instead of a username and password. | | `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 | | `VP_MSSQL_TRUNCATE` | `true/flase` | Is set to true database logs will be truncated after the backup process. | *** #### 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 set to `true`, restores the backup directly to a database instance. If `false`, restores the `.bak` file to the specified path only. | | `VP_MSSQL_RENAME_DB` | `true/false` | If set to true, the database will be restored under a new name specified in the `VP_MSSQL_DBNEWNAME` parameter. | | `VP_MSSQL_DBNEWNAME` | `string` | A new name for the restored database | | `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