Backing Up and Recovering a SQL Server on Linux in Azure

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

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

Backing Up and Recovering a SQL Server on Linux in Azure

About this Hands-on Lab

Learning Objectives

Additional Resources

What are Hands-on Labs

Newsletter