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.
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:
- Connect to SQL Server Management Studio.
- Connect the SQL Server 2016 instance of the database engine in your Azure Virtual Machine (VM).
- 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.
- 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;
- Once this is completed successfully, execute the code mentioned mentioned in step 3 to check whether a file-snapshot backup is available.
- Connect to Azure storage in the object explorer and expand the container mentioned in the URL: <our storage account container name>.
- 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.’
- 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.
- Create a database, School, and a table, Student, with ID, name and Dateofjoin fields.
- 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;
- 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
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;
- 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.
- 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:
- Open SQL Server Management Studio and connect the SQL Server 2016 instance of the database engine in Azure VM.
- 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;
- 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