.bak Integration Overview

When to Use a .bak Integration

A .bak integration should be considered in the following scenarios:

1. If you are using SQL Server Express Edition, which does not support SQL Server Replication, and you do not plan to upgrade to Standard or Enterprise Edition.
2. If you have large amounts of data, limited disk space, and limited resources on your server machine, a .bak integration might be more suitable than SQL Server Replication (typical On-Premise integration method).

Process Overview

1. Daily .bak file creation: A backup file with a .bak extension is saved to a target directory every day, ensuring that a recent copy of the file is always available in case of data loss or corruption.
2. AWS CLI for S3 syncing: The AWS Command Line Interface (CLI) is a tool used to manage AWS services through a command-line interface. In this case, the AWS CLI is used to sync the target directory containing the .bak file to an Amazon S3 bucket daily, providing a secure and redundant storage solution for the backup.
3. Mosaic updates: Mosaic updates and refreshes the integration at 3:00 AM Eastern Standard Time (EST) daily.

❗️

Tip: keep your .bak lean—only back up the tables you need. Files over 15 GB (after adding any extra tables) exceed our supported upload size and may slow or skip your daily refresh.

Integration Requirements

Before proceeding with the .bak integration, please ensure the following requirements are met:

1. Scheduled tasks permission: Mosaic must be granted permission to add scheduled tasks to run on a server machine without a user being logged in, and the server machine must maintain internet access to sync the .bak file to Amazon S3.
2. Task scheduling alignment: Schedule tasks in line with Mosaic's daily refresh schedule at 3:00 AM EST to ensure the most up-to-date data is available for integration and prevent disruptions to the integration process.
3. Curated backups recommended: Mosaic recommends including only the tables needed for your integration.
4. Size limit: Non-curated backups (backups that include extra tables) over 15 GB aren’t supported by Mosaic’s .bak service; large, un-curated .bak files may be ingested less frequently, leading to stale data.

Creating .bak Files from SQL Server Express

📘

The steps below perform a full-database backup.

SQL Server has no built-in way to back up only specific tables — to send a curated subset (recommended for Mosaic), you need to build a small "subset" database containing just those tables and back that up.

For a concrete, ready-to-run recipe (T-SQL script, batch file, and Windows Task Scheduler walkthrough), see Creating a Subset .bak.

To create .bak files from SQL Server Express, you have two options:

• Manual Backup — a one-off full-database backup via the SSMS UI. Useful for testing or initial setup.
• Automated Backup — a daily, curated subset backup of just the tables Mosaic needs. Recommended for ongoing use, since curated backups stay well under the 15 GB upload limit and avoid exposing unrelated company data.

Manual Backup (Full Database)

1. Open SSMS and Connect:

   • Launch SQL Server Management Studio (SSMS) and connect to your SQL Server Express instance.

2. Select Database:

   • In Object Explorer, expand the server and databases.
   • Right-click the target database, select Tasks > Back Up.

3. Configure Backup:

   • Ensure the correct database is selected.
   • Choose Full under Backup type.
   • Click Add in the Destination section, specify the .bak file location, and click OK.

4. Start Backup:

   • Click OK to initiate the backup process.
   • A confirmation message will appear upon completion.

Automated Backup (Curated Subset — Recommended)

For day-to-day use, run a daily curated-subset backup containing only the tables Mosaic needs. See Creating a Subset .bak for the full walkthrough — T-SQL rebuild script, sqlcmd batch wrapper, Windows Task Scheduler setup, and AWS CLI upload.