.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 only: Mosaic recommends including only the tables required 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

📘

Note: the steps below perform a full-database backup.

To send only specific tables in your .bak, you must first build a “subset” database containing just those tables, then back up that database.
Possible strategies on Express Edition include:

• T-SQL scripting: use SELECT … INTO or INSERT … SELECT to copy needed tables into a new DB, then back it up.
• SSMS Generate Scripts wizard: script selected tables (schema + data) into a fresh DB, then back it up.
• BCP utility: export/import table data between the original and a subset DB.
• SQLPackage (.bacpac): export only chosen tables into a bacpac, import as a new DB, then back up.

To create .bak files from SQL Server Express:

Manual Backup

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

1. Create Backup Script:

   • Write a T-SQL script to automate the backup process:
     SQL

     BACKUP DATABASE [YourDatabaseName]
     TO DISK = 'C:\Backup\YourDatabaseName.bak'
     WITH INIT, STATS = 10;
     

2. Schedule Backup Using Task Scheduler:

   • Save your T-SQL script as a .sql file.
   • Create a batch file to run the script using sqlcmd:
     bat

     sqlcmd -S .\SQLEXPRESS -i C:\Path\To\YourScript.sql
     

   • Open Task Scheduler, create a new task, and set the batch file to run at your desired schedule.