SQL Server File Snapshot Backups

By: Mohammed Moinudheen   |   Updated: 2021-08-06   |   Comments (1)   |   Related: > Backup

Problem

I want to be able to create file SQL Server file snapshot backups of my database to both Azure blob storage or on-premises storage.  What are the steps to be able to do this?

Solution

This tutorial assumes that you already have resources in your Azure environment and you are familiar with the Azure portal. If you are new to Azure, you can subscribe to a trial account here, which will give you access to Azure services for a year and a credit of USD $200.

In this tip, we will perform a demo by creating a SQL Server database and ensure that the database files are in Azure blob storage. Then we will perform a few file snapshot backups using sample scripts. You can refer to this tip which describes the several backup options that are available for backing up SQL Server databases. Option 4 in the tip, describes in high level the method to perform file snapshot backups.

Create a test SQL Server database locally on an on-premises server

As a first step, create a SQL Server database using this sample script with all the database files created locally on your server.

use master
go
create database motest;  

This will create a sample database with the database files created locally on your server.

As you can see the data and log files both reside locally. In order to create a file snapshot database backup, we need to make use of the FILE_SNAPSHOT option. We can make use of this backup command to perform a file snapshot backup with the following syntax:

BACKUP DATABASE motest   
TO URL = 'https://sqldbcontainermo.blob.core.windows.net/moh/motestdb.bak' -- provide correct path   
WITH FILE_SNAPSHOT;  

You can see below we get the error "The option WITH FILE_SNAPSHOT is only permitted if all database files are in Azure Storage".

As you can see, there is an error message on performing the file snapshot backup as all the database files are not on Azure storage. This is already described in this tip, but it was included in this demo for the purpose of continuity. Now you know the requirement for performing a successful file snapshot backup. In the next example, we will create a sample database with the database files in Azure storage.

Create a test SQL Server database with its database files on Azure storage

Before making use of the Azure storage for the SQL database files, we need to create a policy on a container and generate a shared access signature key also known as SAS. Make sure the policy has the required rights on the container. For creating a storage account and container, you can refer to this tip. For learning more about SAS, you can refer to this tip.

You can use this sample script to create a database with the database files on Azure storage. Make sure, you create a container first if you don't have one, generate a SAS token and create a credential using the SAS token.

drop database motest
go

USE master;
GO

create credential [https://sqldbcontainermo.blob.core.windows.net/moh]  
with identity='SHARED ACCESS SIGNATURE',  
secret = 'sp=rcwd&st=2021-06-30T23:07:49Z&se=2021-07-08T07:07:49Z&spr=https&sv=2020-02-10&sr=c&sig=zXNy5fQHmFTDIpvVVXzSPziBAvLf6fAzrv%3D'

create database motest
on
( name = motest_data, filename = 'https://sqldbcontainermo.blob.core.windows.net/moh/motest_data.mdf'
   --Use correct container path
    )
log on
( name = motest_log, filename = 'https://sqldbcontainermo.blob.core.windows.net/moh/motest_log.ldf'
   --Use correct container path
   ) ;
GO 

Once the SQL database is created with the database files in Azure blob storage, we can perform a backup using the file snapshot option.

Perform full database file snapshot backup of the sample SQL database whose database files reside on Azure blob storage

As you can see, the database files reside on the Azure blob storage for the newly created test database.

For performing the file snapshot backup, use the WITH FILE_SNAPSHOT option. We have the option to use disk, tape or URL for the backup file location. You can use the below script for this purpose.

--- Backup to URL
BACKUP DATABASE motest   
TO URL = 'https://sqldbcontainermo.blob.core.windows.net/moh/motestdb.bak' -- provide correct path   
WITH FILE_SNAPSHOT;  
GO  
 
---- Backup to Disk
BACKUP DATABASE motest   
TO DISK = 'C:\Temp\motestdb.bak' -- provide correct path   
WITH FILE_SNAPSHOT;  
GO  

Once the backup is performed, you can check the snapshots that were generated for the database files. You can go to the data file location in the container and click on the 'Snapshots' option as shown. Here, you can view 3 snapshots that were performed when I ran the backup database commands. Basically, I ran the backup database command three times.

Similarly, you can view the snapshots that were generated for the transaction log files.

As you can see, the full database backup consists of a snapshot of all the database files and a backup file which has pointers to these file snapshots. A full database backup also helps to establish the transaction log backup chain. You can get more information regarding the backup file by making use of the restore filelistonly command.

---- Replace correct URL
restore filelistonly from URL='https://sqldbcontainermo.blob.core.windows.net/moh/motestdb1.bak'

In the output, if you scroll to the right, you will see a column 'SnapshotURL' where you can view the URL of the snapshot of the database file that is present in the backup.

You can see the snapshot URL for all the database files. This column will be NULL if this backup was not performed using the file_snapshot option.

Perform transaction log file snapshot backup of the sample SQL database whose database files reside on Azure blob storage

In the previous section, we performed a full database backup of the database using the WITH FILE_SNAPSHOT option. In this section, we will perform only a transaction log backup using the below commands.

--- Backup log to URL
BACKUP LOG motest   
TO URL = 'https://sqldbcontainermo.blob.core.windows.net/moh/motestdb.trn' -- provide correct path   
WITH FILE_SNAPSHOT;  
GO  
 
---- Backup log to Disk
BACKUP LOG motest   
TO DISK = 'C:\Temp\motestdb.trn' -- provide correct path   
WITH FILE_SNAPSHOT;  
GO  

The commands are similar to that of performing a full database backup. Once the transaction log file snapshot backups are completed, you can validate the snapshots of all the database files on Azure storage similar to how we did after the full database file snapshot backup was completed. Below is a screenshot of the snapshots performed on the SQL data file after the transaction log file snapshot backup completed.

As you can see, there is a snapshot performed for the SQL Server data file too. You will also see snapshots taken of the transaction log file too as shown below.

With this, you can see a file snapshot is created for all the SQL Server database files when we perform the transaction log backup using the file snapshot option. So basically, we only need to perform a full database for the first time using the file snapshot option and from then on, we only need to perform transaction log backups using the file snapshot option. Additionally, with the transaction log backup using the file snapshot option, it also creates a backup file with information of the file snapshots. And a truncation of the transaction log occurs too after the transaction log backup using file snapshot completes.

Similar to what we did after performing the full database backup using the file_snapshot option, we can check the details of the transaction log backup file using the restore filelistonly option.

As you can see from the screenshot, in the SnashotURL column you can see the details of the snapshot files in the transaction log backup which includes all the database files.

Backups performed using traditional streaming method

When you compare backups performed using traditional streaming method with that of the backup performed using the file_snapshot option, you will see that the traditional method is much more time consuming and requires us to keep track of all the backup files that are part of the backup regime. If restoration of databases is required for any reason, then all of the backup files will need to be restored in sequence to return to the restore point. However, for restoring databases of backups performed using the file_snapshot option, we can easily perform point in time restores in a few simple steps as managing the backup file is much easier and we do not need to restore the full database backup first and then the rest of the transaction log backups like how we do with the traditional streaming backup files. We will discuss the various restore options in more detail in another tip with a detailed demo.

Next Steps

• In this tip, as a first step you created a sample SQL Server database with its database files residing locally on your server instead of Azure and noticed that the file_snapshot option cannot be used.
• Next, you created a sample SQL Server database with all its database files in Azure blob storage.
• Refer the scripts used in this tip to create a sample SQL Server database with its database files on Azure storage.
• You can refer the other tips mentioned to learn more about storage accounts, containers and Shared Access Signatures (SAS).
• You can refer the scripts used in this tip for performing a full database backup using the file_snapshot option.
• You can refer the scripts used in this tip for performing a transaction log backup using the file_snapshot option.
• You can review the backup files that you created and validate them using the verify filelistonly option as shown in the tip.

About the author

Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips