{"id":3383,"date":"2025-04-16T19:27:05","date_gmt":"2025-07-20T04:49:42","guid":{"rendered":""},"modified":"2025-02-02T00:46:20","modified_gmt":"2025-02-01T23:46:20","slug":"microsoft-sql-server-on-linux-installation-configuration-and-backup","status":"publish","type":"post","link":"https:\/\/netcloud24.com\/knowledgebase\/microsoft-sql-server-on-linux-installation-configuration-and-backup\/","title":{"rendered":"Microsoft SQL Server on Linux: Installation, Configuration, and Backup"},"content":{"rendered":"

\u00a0<\/p>\n

\u00a0<\/p>\n

\u00a0<\/h1>\n

Introduction<\/h2>\n

Microsoft SQL Server is a powerful relational database management system (RDBMS) that has traditionally been associated with Windows. However, Microsoft has expanded its support to Linux, allowing users to run SQL Server on a variety of platforms. This guide will walk you through the process of installing Microsoft SQL Server on Linux, creating a database, opening ports for remote connections, setting up backups, and creating scripts for automation.<\/p>\n

Step 1: Install Microsoft SQL Server on Linux<\/h2>\n

To install Microsoft SQL Server on a Linux system, follow these steps:<\/p>\n

    \n
  1. Update your system:<\/strong>\n
    sudo apt-get update<\/code><\/pre>\n<\/li>\n
  2. Install the SQL Server package:<\/strong>\n
    sudo apt-get install -y mssql-server<\/code><\/pre>\n<\/li>\n
  3. Run the setup:<\/strong>\n
    sudo \/opt\/mssql\/bin\/mssql-conf setup<\/code><\/pre>\n
    During the setup, you will be prompted to accept the license terms and set the SA (System Administrator) password.<\/p>\n<\/li>\n
  4. Verify the installation:<\/strong>\n
    systemctl status mssql-server<\/code><\/pre>\n
    This command will show the status of the SQL Server service. Ensure that it is running.<\/p>\n<\/li>\n<\/ol>\n
    Step 2: Create a Database<\/h2>\n
    Once SQL Server is installed, you can create a database using the following steps:<\/p>\n
      \n
    1. Connect to SQL Server:<\/strong>\n
      sqlcmd -S localhost -U SA -P 'YourPassword'<\/code><\/pre>\n<\/li>\n
    2. Create a new database:<\/strong>\n
      CREATE DATABASE TestDB;<\/code><\/pre>\n<\/li>\n
    3. Verify the database creation:<\/strong>\n
      SELECT Name FROM sys.databases;<\/code><\/pre>\n<\/li>\n<\/ol>\n
      Step 3: Open Ports for Remote Connections<\/h2>\n
      To allow remote connections to your SQL Server instance, you need to open the appropriate port (default is 1433) on your Linux firewall.<\/p>\n
        \n
      1. Open the port:<\/strong>\n
        sudo ufw allow 1433\/tcp<\/code><\/pre>\n<\/li>\n
      2. Enable the firewall:<\/strong>\n
        sudo ufw enable<\/code><\/pre>\n<\/li>\n
      3. Verify the firewall status:<\/strong>\n
        sudo ufw status<\/code><\/pre>\n<\/li>\n<\/ol>\n
        Step 4: Set Up Backups<\/h2>\n
        Regular backups are essential for data protection. Here\u2019s how to set up automated backups:<\/p>\n
          \n
        1. Create a backup directory:<\/strong>\n
          sudo mkdir \/var\/opt\/mssql\/backup<\/code><\/pre>\n<\/li>\n
        2. Set permissions:<\/strong>\n
          sudo chown mssql:mssql \/var\/opt\/mssql\/backup<\/code><\/pre>\n<\/li>\n
        3. Create a backup script:<\/strong>\n
          sudo nano \/usr\/local\/bin\/backup_db.sh<\/code><\/pre>\n
          Add the following content to the script:<\/p>\n
          #!\/bin\/bash\r\nsqlcmd -S localhost -U SA -P 'YourPassword' -Q \"BACKUP DATABASE TestDB TO DISK = '\/var\/opt\/mssql\/backup\/TestDB.bak'\"\r\n            <\/code><\/pre>\n<\/li>\n
        4. Make the script executable:<\/strong>\n
          sudo chmod +x \/usr\/local\/bin\/backup_db.sh<\/code><\/pre>\n<\/li>\n
        5. Schedule the backup using cron:<\/strong>\n
          crontab -e<\/code><\/pre>\n
          Add the following line to schedule a daily backup at 2 AM:<\/p>\n
          0 2 * * * \/usr\/local\/bin\/backup_db.sh<\/code><\/pre>\n<\/li>\n<\/ol>\n
          Step 5: Automate Tasks with Scripts<\/h2>\n
          You can automate various tasks using scripts. For example, to automate database maintenance tasks, you can create a script and schedule it using cron.<\/p>\n
            \n
          1. Create a maintenance script:<\/strong>\n
            sudo nano \/usr\/local\/bin\/db_maintenance.sh<\/code><\/pre>\n
            Add the following content to the script:<\/p>\n
            #!\/bin\/bash\r\nsqlcmd -S localhost -U SA -P 'YourPassword' -Q \"DBCC CHECKDB('TestDB') WITH NO_INFOMSGS, ALL_ERRORMSGS\"\r\n            <\/code><\/pre>\n<\/li>\n
          2. Make the script executable:<\/strong>\n
            sudo chmod +x \/usr\/local\/bin\/db_maintenance.sh<\/code><\/pre>\n<\/li>\n
          3. Schedule the script using cron:<\/strong>\n
            crontab -e<\/code><\/pre>\n
            Add the following line to schedule the script to run weekly:<\/p>\n
            0 3 * * 1 \/usr\/local\/bin\/db_maintenance.sh<\/code><\/pre>\n<\/li>\n<\/ol>\n
            Conclusion<\/h2>\n
            By following this guide, you have successfully installed Microsoft SQL Server on a Linux system, created a database, opened ports for remote connections, set up automated backups, and created scripts for automation. These steps will help you manage your SQL Server instance efficiently and ensure that your data is secure and accessible.<\/p>\n
            For more advanced configurations and optimizations, refer to the official Microsoft SQL Server on Linux documentation<\/a>.<\/p>\n
            \n
            Author:<\/strong> \u0141ukasz Bodziony<\/p>\n
            Website:<\/strong> Windows VPS<\/a><\/p>\n
            \u0141ukasz Bodziony is the CEO and founder of NETCLOUD24<\/a>, a global VPS hosting brand proudly originating from Poland. With extensive experience in cloud computing, virtualization, and server management, he delivers high-performance Windows VPS<\/strong> and Remote Desktop Services (RDS)<\/strong> solutions to clients across Europe, North America, and beyond.<\/em><\/p>\n
            His expertise covers a wide range of technologies, including Microsoft Azure<\/strong>, Proxmox VE<\/strong>, Amazon Web Services (AWS)<\/strong>, and numerous other virtualization and cloud platforms.<\/em><\/p>\n
            Beyond running his hosting business, \u0141ukasz also provides professional paid server configuration and optimization services<\/strong> for companies and individuals. Outside of work, he is dedicated to caring for his children and building a secure future for them.<\/em><\/p>\n
            If you are interested in working with him or need expert assistance with your hosting, cloud environment, or server setup, feel free to reach out via Windows VPS<\/a>.<\/em><\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
            \u00a0 \u00a0 \u00a0 Introduction Microsoft SQL Server is a powerful relational database management system (RDBMS) that has traditionally been associated with Windows. However, Microsoft has expanded its support\u2026<\/p>\n","protected":false},"author":1,"featured_media":3421,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_seopress_robots_primary_cat":"","_seopress_titles_title":"","_seopress_titles_desc":"","_seopress_robots_index":"","footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[],"tags":[14,12,11,23,20,21,22,17,7,8,6,10,18,19,15,24,16,5,13,9],"class_list":["post-3383","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","tag-cheapvps","tag-cloudvps","tag-hostingvps","tag-rds","tag-rdscal","tag-remotedesktop","tag-remotedesktopvps","tag-servervps","tag-ukvps","tag-virtualserver","tag-vpshosting","tag-vpsserver","tag-vpssolutions","tag-vpswindows","tag-vpswithwindows","tag-windowsrds","tag-windowsserver","tag-windowsvps","tag-windowsvpshosting","tag-windowsvpsuk"],"jetpack_publicize_connections":[],"_links":{"self":[{"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/posts\/3383","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/comments?post=3383"}],"version-history":[{"count":0,"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/posts\/3383\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/media\/3421"}],"wp:attachment":[{"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/media?parent=3383"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/categories?post=3383"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/netcloud24.com\/knowledgebase\/wp-json\/wp\/v2\/tags?post=3383"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}