.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.

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.

Creating .bak Files from SQL Server Express

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.