Backing Up and Recovering a SQL Server on Linux in Azure

30 minutes
  • 8 Learning Objectives

About this Hands-on Lab

A proper plan for both backups and restores is essential to ensuring continued business operations in the event of data loss, corruption, or system failure.

In this hands-on lab, we perform the primary backup and restore operations for SQL Server. This helps build an understanding of how the types differ from one another and how those differences affect restore operations.

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

Log In to the Azure Portal

Log in to the Azure Portal using the provided credentials.

Create a SQL Server on Linux VM from the Azure Marketplace
  • On the Home page, click Create a resource.
  • Search for "SQL Server 2017" and hit enter.
  • Filter the search by the criteria Operating system -> Redhat and Publisher -> Microsoft.
  • Click on Free SQL Server License: SQL Server 2017 Developer on Red Hat Enterprise Linux 7.4 (RHEL).
  • Click Create.
  • Select the resource group created by the lab.
  • Provide a Virtual machine name.
  • Click Change size under Size.
  • Select B2s and click Select.
  • Choose Password for the Authentication type, then provide a Username and Password.
  • Click Allow selected ports.
  • Select SSH (22).
  • Click Review + create.
  • Verify that everything looks good and click Create.
  • Once the deployment is complete, click Go to resource.

If we’d like outside applications to connect to your database, we should go through the following steps. We recommend this for the purposes of connecting a client applicaiton such as Azure Data Studio. While the tasks can be performed in SQLCMD, it is more cumbersome that way.

  • On our resource page, click Networking in the left pane.
  • Click Add inbound port rule.
  • Leave all of the defaults and change Destination port ranges to 1433.
  • Change Name to "Port_1433".
  • Click Add.
Connect to the SQL Server VM
  • On the resource overview page, click Connect at the top.
  • Use the provided information to to the server via SSH.

If we’re using an external client, such as Azure Data Studio, we must perform the following tasks to open the firewall port on the VM.

sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload
Change the SA Password
  • Stop the mssql-server service.

    sudo systemctl stop mssql-server
  • Change the SA password.

    sudo /opt/mssql/bin/mssql-conf set-sa-password
  • Start the mssql-server service.

    sudo systemctl start mssql-server
Restore the Database from a Full Backup
  • Obtain the Adventure Works sample database Full Backup online. Be aware that the link below is for example purposes only. Microsoft could change the actual link any time at their discretion.

    sudo wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2017.bak -P /var/opt/mssql/data/
  • Connect to SQL Server locally with SQLCMD or externally with a client such as Azure Data Studio.

    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'AwesomePassword!'
  • Restore the Adventure Works database using the Full Backup. Note that this type of restore recreates the entire database from a specific time and allows us to choose where to place its files on this new server.

    Restore database AdventureWorks2017
    FROM DISK = '/var/opt/mssql/data/AdventureWorks2017.bak'
    with RECOVERY,
    MOVE 'AdventureWorks2017' to '/var/opt/mssql/data/AdventureWorks2017.mdf',
    MOVE 'AdventureWorks2017_log' to '/var/opt/mssql/data/AdventureWorks2017_log.ldf';
    GO
  • Set the recovery model to full.

    ALTER DATABASE AdventureWorks2017 SET RECOVERY FULL;
    GO
Create a Maintenance Table and Perform a Differential Backup
  • Create a Maintenance table to track our backup and restore tasks.

    USE AdventureWorks2017;
    GO
    CREATE TABLE dbo.Maintenance (
    JobID INT IDENTITY (1,1),
    JobName VARCHAR(20),
    JobType VARCHAR(20)
    );
  • Perform a differential backup to capture the changes and log it to the Maintenance table.

    INSERT INTO dbo.Maintenance (JobName,JobType)
    VALUES (N'Diff Backup', N'Differential');
    GO
    BACKUP DATABASE AdventureWorks2017
    TO DISK = '/var/opt/mssql/data/AdventureWorks2017_Diff.bak'
    WITH DIFFERENTIAL;
    GO
Add Data to the Maintenance Table and Back Up the Transaction Log
  • Create some example job entries in the Maintenance table.

    USE AdventureWorks2017;
    GO
    INSERT INTO dbo.Maintenance (JobName,JobType)
    VALUES (N'Index Rebuild', N'ALTER INDEX')
    INSERT INTO dbo.Maintenance (JobName,JobType)
    VALUES (N'Index Reorg', N'ALTER INDEX')
    INSERT INTO dbo.Maintenance (JobName,JobType)
    VALUES (N'System Log Clean', N'sp_cycle_errorlog')
    INSERT INTO dbo.Maintenance (JobName,JobType)
    VALUES (N'Integrity Check', N'DBCC CHECKDB');
    GO
  • Back up the transaction log.

    INSERT INTO dbo.Maintenance (JobName,JobType)
    VALUES (N'Log Backup', N'T-Log');
    GO
    BACKUP LOG AdventureWorks2017 
    TO DISK = '/var/opt/mssql/data/AdventureWorks2017_Log.trn';  
    GO
Delete and Recover the Database Using Our Backups
  • Kill current connections and drop the database.

    USE master;
    GO
    ALTER DATABASE AdventureWorks2017 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE AdventureWorks2017;
    GO
  • Restore the Full Backup that we obtained with wget.

    Restore database AdventureWorks2017
    FROM DISK = '/var/opt/mssql/data/AdventureWorks2017.bak'
    with RECOVERY,
    MOVE 'AdventureWorks2017' to '/var/opt/mssql/data/AdventureWorks2017.mdf',
    MOVE 'AdventureWorks2017_log' to '/var/opt/mssql/data/AdventureWorks2017_log.ldf';
    GO
  • Select from the Maintenance table and note that it is not present.

    USE AdventureWorks2017
    GO
    SELECT * FROM dbo.Maintenance;
    GO
  • Restore the differential backup. This requires the full backup as well.

    USE master;
    GO
    ALTER DATABASE AdventureWorks2017 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE AdventureWorks2017;
    GO
    Restore database AdventureWorks2017
    FROM DISK = '/var/opt/mssql/data/AdventureWorks2017.bak'
    with NORECOVERY,
    MOVE 'AdventureWorks2017' to '/var/opt/mssql/data/AdventureWorks2017.mdf',
    MOVE 'AdventureWorks2017_log' to '/var/opt/mssql/data/AdventureWorks2017_log.ldf';
    GO
    RESTORE DATABASE AdventureWorks2017
    FROM DISK = '/var/opt/mssql/data/AdventureWorks2017_Diff.bak'
    WITH RECOVERY;
    GO
  • Select from the Maintenance table and note that it is now present but only contains the records of our differential backup.

    USE AdventureWorks2017
    GO
    SELECT * FROM dbo.Maintenance;
    GO
  • Restore the entire backup set, including the transaction Log.

    USE master;
    GO
    ALTER DATABASE AdventureWorks2017 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE AdventureWorks2017;
    GO
    Restore database AdventureWorks2017
    FROM DISK = '/var/opt/mssql/data/AdventureWorks2017.bak'
    with NORECOVERY,
    MOVE 'AdventureWorks2017' to '/var/opt/mssql/data/AdventureWorks2017.mdf',
    MOVE 'AdventureWorks2017_log' to '/var/opt/mssql/data/AdventureWorks2017_log.ldf';
    GO
    RESTORE DATABASE AdventureWorks2017
    FROM DISK = '/var/opt/mssql/data/AdventureWorks2017_Diff.bak'
    WITH NORECOVERY;
    GO
    RESTORE LOG AdventureWorks2017
    FROM DISK = '/var/opt/mssql/data/AdventureWorks2017_Log.trn'
    WITH RECOVERY;
    GO
  • Select from the Maintenance table and note that it now contains all of the previous information.

    USE AdventureWorks2017
    GO
    SELECT * FROM dbo.Maintenance;
    GO

Additional Resources

In this hands-on lab scenario, we become familiar with backup and restore processes for SQL Server by putting them into action. To start with, we'll provision a SQL Server on a Linux VM utlizing the Azure Marketplace. Then, we'll obtain a sample Adventure Works database for practice. After that, we perform the following tasks:

  • Restore the full backup.
  • Change the Recovery Mode to full.
  • Create a new table and perform a differential backup.
  • Add data to the table and perform a log backup.
  • Drop the database and practice restoring each backup type.

Performing these exercises helps with becoming familiar with the differences between full, differential, and log backups.

Step-by-step instructions are included in the task list. Feel free to follow along there or just jump in!

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Get Started
Who’s going to be learning?

How many seats do you need?

  • $499 USD per seat per year
  • Billed Annually
  • Renews in 12 months

Ready to accelerate learning?

For over 25 licenses, a member of our sales team will walk you through a custom tailored solution for your business.


$2,495.00

Checkout
Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!