![[Formales]] ![[docker_for_mssql_2024__670617475.png]] [Get Docker](https://docs.docker.com)<br> From: *Docker* # Set up SQL Server Express > [!TLDR] > This guide will walk you through setting up SQL Server Express on a Mac with an M1/M2 chip using Docker. Since SQL Server Express doesn't natively support ARM architecture, we'll use a workaround to run it using Docker with emulation. > > Don't worry if you're new to Docker—just follow these steps carefully, and you'll be good to go! ## Overview of Steps 1. Grant folder access to Docker 2. Pull the SQL Server Image 3. Create the SQL Server Container 4. Set correct permissions within the container # Install Docker ## Download Docker Go to [Docker's website](https://www.docker.com/products/docker-desktop) and download Docker Desktop for Mac. ## Install Docker Once downloaded, open the file and follow the instructions to install Docker. Make sure it's running (you’ll see the Docker icon in your status bar). ## Pull the SQL Server Image We need to download the correct version of SQL Server that works inside Docker. ### Open Terminal Use Spotlight Search (Command + Space) and type Terminal to open it. ### Pull the SQL Server Image In Terminal, type the following command and press Enter: ```bash docker pull mcr.microsoft.com/mssql/server ``` This downloads the SQL Server image, which is like a blueprint for creating your SQL Server environment. ==Keep in mind: From September 2025 on there isn't a specific arm-version of SQL Server anymore!== ## Run the SQL Server Express Container with ARM Emulation Now we will create and run SQL Server in a Docker container. Because your Mac uses an M1/M2 chip, we need to use a special flag to tell Docker to emulate a version that works on x86 (the architecture SQL Server normally supports). ### Run SQL Server in Docker Type the following command into Terminal and press Enter: ```bash docker run --network host -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=YourPassword123' \ -p 1433:1433 --name azure_sql_edge \ --platform linux/amd64 \ -d mcr.microsoft.com/azure-sql-edge ``` ### Rund SQL Server in Docker with a locally mounted Folder Type the following command into Terminal and press Enter: ```bash docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' \ -p 1433:1433 --name azure_sql_edge \ --platform linux/amd64 \ -v /Users/pk/Documents/A3/1Lect_SQLEdge/sqlserver_to_postgres:/var/opt/mssql/backup \ -d mcr.microsoft.com/azure-sql-edge ``` > [!NOTE] ACCEPT_EULA=Y > This tells SQL Server that you accept its license terms. > [!NOTE] SA_PASSWORD=YourPassword123 > Replace this with your own password for the SQL Server admin account. > [!NOTE] platform linux/amd64 > This enables emulation, allowing SQL Server to run on an ARM Mac. ### Verify it's Running To check if SQL Server is running, type: ```bash docker ps ``` If everything is working, you’ll see your container listed here. ## Connect to SQL Server, Settings Now you can connect to SQL Server from any SQL client like Azure Data Studio, SQL Server Management Studio, or DataGrip. > [!multi-column] > >> [!NOTE] Server >> localhost,1433 > >> [!NOTE] User >> sa > [!NOTE] Password > The password you set earlier (YourPassword123). # Copy Files in the Container ![[copy_files_in_the_container__637591824.png]] > [!TLDR] > If you want to move an existing database into SQL Server, you can copy .mdf (data) and .ldf (log) files into your container. ## List Your Running Containers First, find out the ID of the container you’re working with. In Terminal, type: ```bash docker ps ``` Look for the CONTAINER ID of your SQL Server instance. You’ll need this for the next step. ## Copy .mdf and .ldf Files into the Container Now, copy the database files into your SQL Server container. ### Copy the .mdf (Data) File Replace 7fea4ec46ed2 with your CONTAINER ID and type: ```bash docker cp /Users/pk/Documents/A3/1Lect_SQLEdge/Lecturer_BA2/Lecturer.mdf 7fea4ec46ed2:/var/opt/mssql/data/ ``` ### Copy the .ldf (Log) File Run a similar command for the log file: ```bash docker cp /Users/pk/Documents/A3/1Lect_SQLEdge/Lecturer_BA2/Lecturer.ldf 7fea4ec46ed2:/var/opt/mssql/data/ ``` # Permissions within the Container ![[permissions_within_the_container__638200189.png]] > [!TLDR] > Sometimes SQL Server can’t access the files because of permission issues. Let’s fix that! ## Check Permissions Inside the Container You need to check the permissions for the files you copied. ### Enter the Container Type the following command to access the container’s terminal: ```bash docker exec -it 7fea4ec46ed2 /bin/bash ``` ### Navigate to the Directory ```bash cd /var/opt/mssql/data/ ``` ### Check the File Permissions ```bash ls -l ``` ## Adjust Permissions If the mssql user doesn't have permission to access the files, you'll need to fix this. ### Exit the Container Type exit to leave the container’s terminal. ### Re-enter the Container as Root This gives you full control to modify permissions: ```bash docker exec -u root -it <ID SQL CONTAINER> /bin/bash ``` ### Change File Ownership Change ownership of the files so SQL Server can access them: ```bash chown mssql:root /var/opt/mssql/data/Lecturer.mdf chown mssql:root /var/opt/mssql/data/Lecturer.ldf ``` ### Verify the Change ```bash ls -l /var/opt/mssql/data/ ``` ### Exit the Root Session Type exit to return to your regular terminal. # Attach the Database in Server ![[attach_the_database_in_server__638970986.png]] Now that the files are inside the container, you can attach the database. ## Open Your SQL Client Use Azure Data Studio, SQL Server Management Studio, or another client. ## Run the SQL Query In your SQL client, run the following to attach the database: ```sql USE master; GO CREATE DATABASE LecturerNew ON (FILENAME = N'/var/opt/mssql/data/Lecturer.mdf'), (FILENAME = N'/var/opt/mssql/data/Lecturer.ldf') FOR ATTACH; GO ``` # Organize the Backup ![[organize_the_backup__639128466.png]] ## Create the User-Defined Function for Backup Let's create a stored procedure to back up your SQL Server database without worrying about managing the age of backups. This procedure will simply back up the LecturerNew database to the default SQL Server backup directory inside your Docker container. ```sql CREATE PROCEDURE [dbo].[usp_BackupLecturerNew] AS BEGIN DECLARE @backupFile NVARCHAR(255) DECLARE @backupDate NVARCHAR(20) DECLARE @backupPath NVARCHAR(255) -- Set the default SQL Server backup directory inside the Docker container SET @backupPath = N'/var/opt/mssql/backup/' -- Generate the backup file name with the current date SET @backupDate = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-', '') SET @backupFile = @backupPath + 'LecturerNew_' + @backupDate + '.bak' -- Perform the database backup BACKUP DATABASE LecturerNew TO DISK = @backupFile WITH NOFORMAT, INIT, NAME = 'LecturerNew_Full Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10; -- Print message to confirm the backup PRINT 'Backup completed for LecturerNew database.'; END; ``` This procedure will create a backup file in the /var/opt/mssql/backup/ directory inside the SQL Server container. You can manually run this procedure or schedule it with a cron job. Description for the cron job is downbelow. # Crontab Copy/Paste Backupfiles ![[crontab_copy_pasty_backup__640775633.png]] ## Create the Backup Script 1. Open the Terminal application on your Mac. 2. Navigate to the directory where you want to store the script: ```bash cd /Users/pk/Documents/A3/1Lect_SQLEdge/LecturerNew_BA ``` 1. Create a new script file named copy_backups.sh by typing: ```bash nano copy_backups.sh ``` This will open the nano text editor in the terminal. 4. Copy and paste the following script into nano: ```bash #!/bin/bash # Define the SQL Server container name and backup directory inside the container SQL_CONTAINER_NAME="<ID SQL SERVER CONTAINER>" SQL_BACKUP_DIR="/var/opt/mssql/backup/" # Define the local backup directory where backups will be copied LOCAL_BACKUP_DIR="/Users/pk/Documents/A3/1Lect_SQLEdge/LecturerNew_BA/" # Copy the backup files from the SQL Server container to the local backup directory docker cp ${SQL_CONTAINER_NAME}:${SQL_BACKUP_DIR} ${LOCAL_BACKUP_DIR} # Print a success message echo "Backups copied from ${SQL_CONTAINER_NAME} to ${LOCAL_BACKUP_DIR}." ``` 5. Save the file: - Press CTRL + O (hold the Control key and press the letter "O"). - Press ENTER to confirm the file name. 6. Exit the nano editor by pressing CTRL + X. 7. Make the script executable ```bash chmod +x copy_backups.sh ``` **Note:** You need to be in the same directory as copy_backups.sh to run this command. ## Add the Script to crontab ### Set nano as Your Default Editor for crontab By default, the terminal might use vi as the editor for crontab, but we want to use nano for ease of use. 1. Open your terminal. 2. Run the following commands to set nano as the default editor: ```bash export VISUAL=nano export EDITOR=nano ``` This will set nano as the editor for your current terminal session. ### Edit crontab using nano Run the **crontab -e **command to open the crontab editor ```bash crontab -e ``` This will open your crontab in nano (if you've set it as your default editor). ### Add the Cron Job In the nano editor, add the following line to create the cron job: ```bash 0 2 * * * /Users/pk/Documents/A3/1Lect_SQLEdge/LecturerNew_BA/copy_backups.sh ``` This line will run your backup script every day at 2 AM. ### Save and Exit nano 1. Press CTRL + O to save the file. 2. Press ENTER to confirm the file name. 3. Press CTRL + X to exit nano. ### Verify the Cron Job You can check that the cron job was successfully added ```bash crontab -l ``` This will list all your current cron jobs, and you should see the one you just added. # Make nano the Default Editor ![[make_nano_the_default_editor__640979848.png]] ## Open Your .bash_profile or .bashrc File These files are hidden in your home directory and contain commands that run each time you open a terminal. 1. Open the terminal. 2. To edit .bash_profile, run: ```bash nano ~/.bash_profile ``` If you prefer, you can edit .bashrc instead: ```bash nano ~/.bashrc ``` ## Add the nano Settings to the File Once the file is open in nano: 1. Add these two lines at the bottom of the file: ```bash export VISUAL=nano export EDITOR=nano ``` 2. Save the file by pressing CTRL + O, then press `ENTER` to confirm. 3. Exit by pressing CTRL + X. ## Apply the Changes To apply the changes, run: ```bash source ~/.bash_profile ``` or, if you edited .bashrc: ```bash source ~/.bashrc ``` ## Test Your Setup Now that nano is set as your default editor, try running crontab -e again: ```bash crontab -e ``` This time, it should open in nano instead of vi. ## Final Suggestions - When referencing \<ID SQL SERVER CONTAINER>, make sure to replace this placeholder with your actual container's name or ID. - Always test your cron job by running your backup script manually before scheduling it. # Install SQLCMD ![[install_sqlcmd__707657974.png]] > [!TLDR] > Complete, beginner-friendly guide on how to install sqlcmd, set it up on your Mac using the zsh shell, and trigger a SQL Server backup from your local machine. > > This guide assumes you're running SQL Server in a Docker container and want to trigger the backup using `sqlcmd`. ## Install Homebrew (If You Don’t Already Have It) Homebrew is a package manager for macOS. If you don’t have it installed, open your terminal and run this command: ```bash /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)" ``` Once Homebrew is installed, you’re ready to install `sqlcmd`. ## Add the Microsoft Homebrew Repository You need to add Microsoft's repository to Homebrew to install sqlcmd. In your terminal, run: ```bash brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release ``` ## Install sqlcmd Next, install `sqlcmd` and the required ODBC tools: ```bash brew update brew install msodbcsql17 mssql-tools ``` ## Add sqlcmd to Your PATH To make sure you can use sqlcmd from anywhere in your terminal, you need to add it to your system’s PATH. Since you’re using zsh, follow these steps: 1. Open your zsh configuration file: ```bash nano ~/.zshrc ``` 2. Add the following lines at the end of the file to include sqlcmd in your path: ```bash export PATH="/opt/homebrew/opt/msodbcsql17/bin:/opt/homebrew/opt/mssql-tools/bin:$PATH" ``` 3. Save the file by pressing CTRL + O, then ENTER. Exit the editor with CTRL + X. 4. Apply the changes to your current session: ```bash source ~/.zshrc ``` ## Verify Installation Check if sqlcmd is installed correctly ```bash sqlcmd -\? ``` If it’s installed, you should see a help message. # Trigger locally the Backup ![[trigger_locally_the_backup__736569776.png]] You will now create a script on your local machine to trigger the stored procedure that performs the backup. ## Create the Backup Script 1. Open your terminal and create a new script in your desired directory (e.g., /Users/pk/Documents/A3/1Lect_SQLEdge/LecturerNew_BA): ```bash cd /Users/pk/Documents/A3/1Lect_SQLEdge/LecturerNew_BA nano trigger_backup.sh ``` 2. Add the following content to the script: ```bash #!/bin/bash # Use sqlcmd to trigger the UDF in the SQL Server container /opt/homebrew/opt/mssql-tools/bin/sqlcmd -S localhost,1433 -U sa -P 'your_password' -d LecturerNew -Q "EXEC dbo.usp_BackupLecturerNew" # Print success message echo "Backup triggered successfully" ``` - Replace \<port> with the port your SQL Server container is exposed on (usually 1433). - Replace 'your_password' with the sa password you set when running the SQL Server container. - Replace LecturerNew with the name of your database if it’s different. - Replace dbo.usp_BackupLecturerNew with the actual name of your stored procedure if it’s different. 3. Save the file with CTRL + O and press ENTER. Exit with CTRL + X. 4. Make the script executable ```bash chmod +x trigger_backup.sh ``` ## Test the Backup Script You can now test the script by running it manually: ```bash ./trigger_backup.sh ``` If everything is set up correctly, it will trigger the backup procedure in your Docker container, and you’ll see the success message *Backup triggered successfully* ## Automate the Backup Using cron [[Crontab Cheatsheet]] You can automate the process by setting up a cron job that runs the backup script at a scheduled time (e.g., every day at 2 AM). ### Edit Your Crontab 1. Open your crontab file by running: ```bash crontab -e ``` This will open the crontab editor (likely in nano if you’ve set it as your default). 2. Add a new cron job to run the script at 2 AM every day: ```bash 0 2 * * * /Users/pk/Documents/A3/1Lect_SQLEdge/LecturerNew_BA/trigger_backup.sh ``` This means: - **0 2 * * ***: Run at 2:00 AM every day. - The path /Users/pk/Documents/A3/1Lect_SQLEdge/LecturerNew_BA/trigger_backup.sh points to your backup script. 3. Save and exit (CTRL + O, ENTER, CTRL + X). ### Verify the Cron Job To verify that the cron job was added successfully, you can run: ```bash crontab -l ``` This will list all your active cron jobs, and you should see your backup job listed. ### Recap - Install sqlcmd using Homebrew. - Create a stored procedure in SQL Server for backing up your database. - Set up a local script to trigger the stored procedure from your Mac. - Automate the backup by adding the script to your crontab.