File-Snapshot Backups to Microsoft Azure Blob Storage

By: Admin

Azure’s SQL Server data files allow us to create a database in an on-premises SQL Server or an available server in the Azure Virtual Machine. This database is tied to our application using a specific location in Azure blob storage.  The File-Snapshot backup, introduced in SQL Server 2016, is capable of quickly performing a backup and restore.

Snapshot Backup to MS Azure Blob

Before using the File-Snapshot backup, you should have some background information about Azure blob snapshot.

An Azure blob snapshot is a read-only version of a blob taken at a point in time. These snapshots help us create backups. Once you create a blob, you can’t modify it – but you can read, copy or delete a blob snapshot.

The file-snapshot backup contains a set of Azure blob storage and a backup file. The backup file contains the pointers to the snapshots.

A File-Snapshot backup is taken in two ways:

  • Full Database backups
  • Transaction log backups

Full Database backups:

  • Creates a snapshot of each data and log file of the SQL database
  • Generates the transaction log backup chain

A File-Snapshot backup doesn’t copy all the data from the database like our normal backup process. Instead, it relies on the Azure storage service to create a snapshot of the files. This makes the backup process very quick.

Transaction Log Backups:

  • Creates a snapshot of each database file
  • Creates a backup file with the location of the snapshots
  • Truncates the transactions log file

Instead of taking a transaction log backup, only transaction log files are captured.

Take Full Database File-Snapshot backup

Let’s assume you have a database in our Azure Virtual machine and you need to take a File-snapshot backup of it.

To back up the database, here’s what to do:

  1. Connect to SQL Server Management Studio.
  2. Connect the SQL Server 2016 instance of the database engine in your Azure Virtual Machine (VM).
  3. Check whether a file snapshot backup exists for the database. To do this, run the code below in a new query window that is connected to your SQL Server instance of the database engine in the Azure VM.

 

  1. Take a File-Snapshot backup of the database.
BACKUP DATABASE <our database name>

TO URL = 'https://<our storage account name> .blob.core.windows.net/<our storage account container name>/<our backupfilename.bak'

WITH FILE_SNAPSHOT;

 

  1. Once this is completed successfully, execute the code mentioned mentioned in step 3 to check whether a file-snapshot backup is available.
  2. Connect to Azure storage in the object explorer and expand the container mentioned in the URL: <our storage account container name>.
  3. Check that the backup is available in the container. Refresh if necessary to view the backup files.

Take a Transaction Log Backup

 
Here are the steps to creating an activity in the database and periodically create transaction log backups using File-snapshot backups. In this example, I created a database named ‘School’ and a table named ‘Student.’

  1. Connect SQL server management studio and open two new query windows connected to a SQL Server 2016 instance of a database engine in the Azure VM.
  2. Create a database, School, and a table, Student, with ID, name and Dateofjoin fields.
  3. In one of the two query windows, insert records into the table with some delay:
DECLARE @count INT=1, @inner INT;

WHILE @count < 400

BEGIN

BEGIN TRAN;

SET @inner =1;

WHILE @inner <= 75

BEGIN;

INSERT INTO School.Student

(ID, Name, DateofJoin)

VALUES (NEWID(), ‘Chekcing’, GETDATE());

SET @inner = @inner + 1;

END;

COMMIT;

WAITFOR DELAY '00:00:01';

SET @count = @count + 1;

END;

SELECT COUNT (*) from School.Student;

 

  1. In the other query window, execute the code below to generate a transaction log backup with File-Snapshot backup.

DECLARE @count INT=1, @device NVARCHAR(120), @numrows INT;

WHILE @count <= 8

BEGIN

SET @numrows = (SELECT COUNT (*) FROM School.Student);

SET @device = ‘https://<our storage account name> .blob.core.windows.net<our storage account container name>/school-‘ + CONVERT (varchar(10),@numrows) + ‘-‘ + FORMAT(GETDATE(), ‘yyyyMMddHHmmss’) + ‘.bak’;

BACKUP LOG School TO URL = @device WITH FILE_SNAPSHOT;

SELECT * from sys.fn_db_backup_file_snapshots (‘School’);

WAITFOR DELAY ’00:1:00′;

SET @count = @count + 1;

END;

 

  1. Two new files will be created in the second query window each time the BACKUP LOG statement is executed.
    • log file snapshot
    • data file snapshot.
  2. Connect to Azure storage and check the container <our storage account container name>. You can see the new backup files displayed under the container.

 

Restore a database using File-Snapshot backup at a specific point in time:

You need two adjacent log backup files to restore a database using File-snapshot backups. This will give you the goal posts time that you need to restore.

Here are the steps to restore a database:

  1. Open SQL Server Management Studio and connect the SQL Server 2016 instance of the database engine in Azure VM.
  2. Run the code below to restore the database.

 

ALTER DATABASE <our database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE <our database name>

FROM URL = 'https://<our storage account name>.blob.core.windows.net/<our storage account container name>/<firstbackupfile>.bak'

WITH NORECOVERY,REPLACE;

RESTORE LOG AdventureWorks2014

FROM URL = 'https://<our storage account name>.blob.core.windows.net/<our storage account container name>/<secondbackupfile>.bak'

WITH RECOVERY, STOPAT = 'May 03, 2016 01:40 PM';

ALTER DATABASE <our database name> set multi_user;

 

  1. Check the data in the tables to confirm the restore process.

Restore log backup as a new database:

 

A transaction log backup with File-snapshot is a relatively quick process, when compared to our normal restore process. For a normal restore process, you need a full database backup and one or more transaction log backups. In File-snapshot transaction log backups, you need only the most recent log backup.

Here is a code snippet that can restore the database from a File-snapshot transaction log backup.

RESTORE DATABASE <our database backup name>

FROM URL = 'https://<our storage account name>.blob.core.windows.net/<our storage account container name>/<logbackupfile.bak'

WITH MOVE '<our database name>_data' to 'https://<our storage account name>.blob.core.windows.net/<our storage account container name>/<our databse backup name>_Data.mdf'

, MOVE '<our database name>_log' to 'https://<our storage account name>.blob.core.windows.net/<our storage account container name>/<our database backup name>_Log.ldf'

, RECOVERY

 

What are you looking for?

Join our newsletter list of over 5,000 innovators and receive an email update every other month
Copyright© 2018 alligatortek. All rights reserved.