MsSQL

MsSQL is fully supported by the Portabase agent. By default it uses Microsoft's sqlpackage tool. For applications that need a native backup, the bak format runs BACKUP DATABASE instead.

ConfigurationCopy Anchor Link

OptionsCopy Anchor Link

The following optional fields can be set under an options key in the database configuration. From the dashboard, they are the Backup format, Folder on SQL Server, Folder on the agent and Verify backup fields of the database form. The last three appear when you choose bak.

{
  "name": "Database - MsSQL",
  "database": "myappdb",
  "type": "mssql",
  "username": "sa",
  "password": "Password!Strong1",
  "port": 1433,
  "host": "db-mssql",
  "generated_id": "...",
  "options": {
    "backup_format": "bacpac"
  }
}

Backup formatsCopy Anchor Link

dacpac needs no setup and stays the default. Choose bak when the database references another database or master, when it is written to during backups, or when your application's vendor requires native backups. Use bacpac only when you need that file, for example to import it into Azure SQL Database from the Azure portal.

Native .bak backupsCopy Anchor Link

With backup_format set to bak, the agent asks SQL Server to write the backup into a shared folder:

BACKUP DATABASE [myappdb] TO DISK = '<bak_server_path>/<generated_id>.bak'
WITH COPY_ONLY, FORMAT, CHECKSUM, COMPRESSION

The agent then archives the file from that folder, sends it to your storages like any other backup, and removes it from the folder.

Shared folderCopy Anchor Link

SQL Server writes the file on its own machine, so the agent must be able to reach that folder. You provide the folder and set two paths, because each side sees it differently.

• The SQL Server service account must be able to write there.
• The agent must be able to read, write and delete there. On Linux, SQL Server creates the file as 0640 mssql:root. The agent image runs as root and can read it.

If the two paths do not point to the same folder, the job fails and the error names both. At most one file per database can stay behind if the agent cannot delete it, and the next backup overwrites it.

For a SQL Server on Windows, share the folder and mount it into the agent:

services:
  agent:
    image: portabase/agent:latest
    # ... agent configuration ...
    volumes:
      - sql-backups:/mnt/sql-backups

volumes:
  sql-backups:
    driver: local
    driver_opts:
      type: cifs
      device: //sqlhost/PortabaseBackups
      o: "username=portabase,password=${SHARE_PASSWORD},uid=0,gid=0"

{
  "options": {
    "backup_format": "bak",
    "bak_server_path": "D:\\PortabaseBackups",
    "bak_agent_path": "/mnt/sql-backups"
  }
}

With the CLI, -o bak_agent_path=/mnt/sql-backups mounts that host folder into the agent at the same path. Mount the share on the host first.

Related databasesCopy Anchor Link

Applications that spread their data across several databases need them backed up close together. Put them in the same project and set the schedule on the project: all of them start at the same time, unless MAX_CONCURRENT_BACKUPS makes them wait. To restore them together, select them on the project page and use Restore latest.

Each backup is consistent on its own, but the set is not one point in time across databases.

LimitsCopy Anchor Link

• Azure SQL Database and Managed Instance cannot back up to disk: the job fails with the server's error. Use dacpac or bacpac there.
• A .bak file does not restore onto an older SQL Server version. The restore is refused before anything changes.
• Server logins are not in the backup. Database users restored onto another server need matching logins.
• DB_CHAINING and TRUSTWORTHY are OFF after a restore. If your application relies on them, set them again.
• The transaction log is not truncated. In the FULL recovery model, log backups remain your responsibility.
• Size. A .bak keeps indexes and pages, so it is usually larger than a DACPAC of the same database.

RestoreCopy Anchor Link

A restore replaces the target database. The method follows the file extension, not the backup_format option, so a database can restore any of its older backups.

The restore is refused before anything changes when:

• the file is not a .dacpac, .bacpac or .bak;
• the target is a system database (master, tempdb, model, msdb);
• for .dacpac and .bacpac, the configured login cannot create databases or replace the target. It needs sysadmin or dbcreator, or CREATE ANY DATABASE together with db_owner of the target, or, on Azure SQL Database, the server admin or the dbmanager role;
• for .bak, the shared folder options are missing, or SQL Server cannot read the file.

If sqlpackage fails, the side database is removed and the target is left untouched. A side database left by an interrupted run is removed by the next restore of that database.

Importing a BackupCopy Anchor Link

Since Dashboard 1.32.0, the Import dialog accepts a raw .dacpac or .bacpac file, or a .tar.gz archive containing one. Since Dashboard 1.36.0, it also accepts .bak. The file keeps its extension, which tells the agent how to restore it. Any other file name is rejected.

Uploads are limited to 2 GB, which a .bak can exceed. Backups taken by the agent have no such limit. Restoring a .bak needs the shared folder options on the target database, whatever its backup_format.

Docker Compose ExampleCopy Anchor Link

Here is how to configure a MsSQL service alongside the agent.

services:
  db-mssql:
    container_name: db-mssql
    image: mcr.microsoft.com/azure-sql-edge:latest
    ports:
      - "1433:1433"
    environment:
      ACCEPT_EULA: "Y"
      MSSQL_SA_PASSWORD: "Password!Strong1"
    volumes:
      - mssql-data:/var/opt/mssql
    networks:
      - portabase
    healthcheck:
      test: ["CMD-SHELL", "cat /proc/net/tcp6 | grep -q '059901' || exit 1"]
      interval: 10s
      timeout: 5s
      retries: 20

  agent:
    image: portabase/agent:latest
    # ... agent configuration ...
    depends_on:
      - db-mssql
    networks:
      - portabase

networks:
  portabase:
    external: true

volumes:
  mssql-data: