Backups

The platform makes restorable backups of your Data Lake’s DLO schema — the schema that holds everything you build here.

What a backup is. A copy of a database that contains only your DLO schema. It is made by copying the schema into a temporary database, turning that database into the backup file(s), and removing it again, so nothing outside DLO is ever in a file. Each backup has a manifest (what it holds, how many rows per table, which SQL Server each file needs, any warnings) and a SHA-256 checksum, and is stored in the platform’s backup storage.

One point in time. Before copying, the platform takes a database snapshot of your Data Lake’s database and copies the schema and every table’s rows from that snapshot, so the whole backup is your data as it was at one instant, even while syncs and users keep writing; the snapshot is removed as soon as the copy is done. If the server cannot make a snapshot, the backup still runs: the tables are copied from the live database one after another, so a table copied later can hold rows written after an earlier one was copied. The backup’s details say which of the two it is (point in time, with the snapshot’s time, or per table, with the reason), and the list marks per-table backups. To make the snapshot, the platform’s backup login is given connect-only access to your Data Lake’s database (no role and no other permission) for as long as the snapshot exists, and that access is removed again as soon as the snapshot is; platform operators who prefer it can instead grant that login CONNECT ANY DATABASE on the server, and then no temporary access is needed.

Formats: native, portable, or both. Settings → Backups → Format (or dlake backup settings --format) chooses the file:

• Native (the default) — a SQL Server backup file (.bak). It restores with RESTORE DATABASE on the same SQL Server version as the platform’s, or a later one.
• Portable — a BACPAC file (.bacpac). It imports on older SQL Server versions and on Azure SQL Database, as far back as the features your schema uses allow (see the table below).
• Both — one backup produces the two files from the same temporary copy, so they hold exactly the same objects and rows.

Which SQL Server can restore it. A native file needs the version that made it or a later one; the backup’s details name it. A portable file needs the oldest version that has every feature your schema uses; the backup’s details name that version and the features that set it:

Time Travel tables are stored as plain tables (see below), so they do not raise the minimum.

What is included. Every table in DLO with its data (connector-fed tables keep their schema only unless you include them, and you can choose per table — see below), columns exactly as declared (types and sizes, nullability, identity with its current value, defaults, computed columns), primary keys, unique, check and foreign key constraints, and indexes (including filtered indexes and included columns); views, functions, stored procedures and triggers, exactly as written; row-level-security policies and their predicates; sequences (resuming where yours left off); synonyms; and the platform’s own DLO tables — users, permissions and settings — as data. A Time Travel table is stored as a plain table next to its history table, also as a plain table, so every past version is in the file; re-enable Time Travel after a restore if you want it back. Anything that cannot be carried — for example a view that reads a table outside DLO — is listed in the backup’s warnings rather than stopping the backup. The platform’s own log tables — the audit logs, notifications, run history and event queues — are always backed up as schema only: their rows are the platform’s record of what happened, not your data, and they start empty after a restore (the backup’s details list them separately, with the rows each held).

Connector-fed tables: schema only, by default. Tables that a connector sync writes — the tables your import/sync setup targets, and tables in a connector’s name prefix (such as hs_, sf_, st_, odbc_ or sql_) — are rebuilt from their source systems by the sync, and they are usually most of a Data Lake’s volume. So by default a backup keeps their schema (columns, keys, indexes, constraints and triggers) but not their rows. Your own tables, views, procedures and functions are backed up in full, including views and procedures that read connector-fed tables. The backup’s details list every schema-only table with the number of rows it held, so you know what to re-sync after a restore. If you need a self-contained copy — one that holds the connector data too — turn on Include connector-fed tables in Settings → Backups (or dlake backup settings --include-sync-tables on); the backups, and the free space they need on the server, grow accordingly. After restoring a backup made without them, run the sync to refill those tables.

Choosing what to back up. Settings → Backups → Choose what to back up lists every DLO table with its state in the next backup and lets you set each one to Data (structure and rows), Schema only (structure, no rows) or Excluded (not in the backup at all), and switch views, procedures, functions and triggers on or off. A table you do not set follows the standard rules above. Some platform tables are protected, and the list shows why: the sign-in, permission and settings tables and the migration history always keep their data (a restore without them is not a usable Data Lake), the log tables are always schema only, and the other platform tables may drop their rows but cannot be excluded. Excluding a table also leaves out its triggers and any view that reads it; leaving functions out also leaves out the row-level security policies and computed columns that call them. From the CLI: dlake backup scope-tables shows the list, and dlake backup settings --tables "Orders=data,Archive=excluded", --views on|off, --procedures on|off, --functions on|off and --triggers on|off change it (=default puts a table back on the standard rules; --reset-scope puts every table back).

Foreign keys and portable files. Importing a portable file checks every foreign key, so when a backup includes a portable file, a table kept with its data also keeps the tables its foreign keys point at with their data — even a connector-fed table or one you excluded. These adjustments are listed in Choose what to back up before the backup runs and in the backup’s details afterwards. A native-only backup keeps your choices as they are: a foreign key into a table without rows is restored unchecked (the backup’s warnings give the statement to check it again once the table is refilled), and one into an excluded table is left out.

When a portable file cannot be made. A portable file must be self-contained. If an object in your DLO schema — typically a procedure — refers to a table outside DLO or in another database, a portable-only backup stops with the status portable check failed, names the objects, and keeps no file. With Both, a portable file that cannot be made (or exported, or uploaded) does not cost you the native file: the backup succeeds with the .bak alone, and the list and the backup’s details show portable check failed with the step that failed and why. Leave those objects out of the backup (for example switch procedures off), keep the tables they read in the backup, or choose the native format.

Space check. Before a backup starts, the platform checks that the SQL server’s data volume has room for the temporary database (the size of the data being copied), the .bak (about a third of that where the SQL Server edition compresses backups, as large again where it does not; none for a portable-only backup) and 10% headroom. If it has not, the backup is refused with those figures, so you can see what the room is needed for. The check follows Choose what to back up: tables you exclude or keep as schema only are not counted, and connector-fed tables you keep with their data are. The free space checked is the free space on the SQL server’s data volume, not the unused space inside a database file: the refusal and the backup’s details name the volume by its mount point and show its free space, its size and what the backup needed there.

Restoring a native file. Download the .bak, then on your own SQL Server:

RESTORE DATABASE [MyRestoredLake]
  FROM DISK = N'C:\Backups\202609200300-mylake-dlo.bak'
  WITH MOVE N'<logical data file>' TO N'C:\Data\MyRestoredLake.mdf',
       MOVE N'<logical log file>'  TO N'C:\Data\MyRestoredLake_log.ldf',
       RECOVERY;

The two logical file names, and this statement already filled in, are in the backup’s details (Settings → Backups, or dlake backup show <id>). After the restore the objects are in schema DLO of the restored database.

Importing a portable file. Download the .bacpac, then either use SQL Server Management Studio (right-click Databases → Import Data-tier Application, and choose the file) or run:

SqlPackage /Action:Import /SourceFile:"C:\Backups\202609200300-mylake-dlo.bacpac" /TargetServerName:<server> /TargetDatabaseName:MyRestoredLake

The import creates a new database; the objects are then in schema DLO. Use a recent SqlPackage or SSMS: the file format is read by the tool, and the target server only has to support the features listed above.

Schedule and retention. Backups are made weekly by default, early on Sunday morning (UTC). Settings → Backups lets you choose daily, weekly or off (no scheduled backups — you can still make one whenever you like), or turn backups off entirely. Backups are kept for 30 days and then deleted automatically, both files of a backup together.

Making and downloading one. Settings → Backups → Back up now starts a backup; it runs in the background and appears in the list when it finishes. Download gives you a file through a link that is valid for 15 minutes (a backup in both formats has one download per file); only an admin with the same permission as the other tenant settings may download, because the file is a complete copy of your data, and each download is recorded in the audit trail. The details button shows each file, the SQL Server it needs, the scope and the warnings. From the CLI: dlake backup run, dlake backup list, dlake backup show <id>, dlake backup download <id> [--format native|portable] --out <folder> (which checks the SHA-256 for you), dlake backup scope-tables and dlake backup settings (--enabled, --schedule, --include-sync-tables, --format and the scope flags). Viewing backups and the table list needs the same permission as viewing the audit trail; starting a backup, downloading one or changing the settings needs the same permission as the other tenant settings.

Who creates the temporary database. On shared SQL servers the platform uses a separate backup creator login for the part of a backup that needs server rights: it creates the temporary database, lets your Data Lake’s own login into it, turns it into the backup file(s) and removes it. It may create databases and nothing else — it has no access to your Data Lake’s database; the copy of your DLO schema is always made by your Data Lake’s own login. It also creates and removes the snapshot the copy reads from, which needs no access to your database either. On a dedicated (express) engine your own login does all of it.

Where the backup files go. A portable file is exported from the temporary database by the platform, held briefly on the platform’s own host, uploaded to the backup storage with its SHA-256, and removed from the host — so a portable-only backup needs no file route from your SQL server at all. For a native file: on a dedicated (express) engine the backup file is written inside your engine and then uploaded to the platform’s backup storage by the host that runs the engine. On a shared SQL server running SQL Server 2022 or later, the server writes the backup file directly to the platform’s backup storage: no copy is kept on the server’s own disk, so the space check counts only the temporary database. These native files are checked by SQL Server itself (page checksums while writing, then a full read-back of the stored file), so they show no SHA-256 and dlake backup download skips that check. On a shared server running an older version of SQL Server, native backups are not available until the server is upgraded to SQL Server 2022 or later; portable backups are.

If a backup says “insufficient rights”. One of the platform’s database logins on your server is missing a right the backup needs, and the message says which: the backup creator login, or your Data Lake’s own login. Nothing is wrong with your data; ask your platform administrator to grant it.

If a backup says “s3 credential missing”. Your server can write backups directly to the platform’s backup storage, but the one-time setup that lets it do so has not been done on that server yet (the platform finds this out either before the copy starts or when the backup file is written). Nothing is wrong with your data; ask your platform administrator to complete it, and the next backup runs normally.

If a backup says “portable check failed”. See When a portable file cannot be made above: the message names the objects that refer outside your DLO schema.