tl;dr SQL Server Backups are a useful feature to reduce the pain of breaking changes when switching branches or when debugging scenarios that require complex data setups.

Introduction

A while ago I wrote an article Keep your dev loop as short as possible with SQL Server Snapshots about how SQL Server Snapshots can help in certain debugging scenarios.

To recap, snapshots are essentially a read-only copy of a database that can be created very quickly. You can then restore your database from the snapshop back to that known point-in-time. This is useful when switching branches to test a PR that might contain db schema changes for example, or when debugging a scenario that might take a lot of time to setup the data and you don’t want to have to do each time you run through the scenario.

Snapshots is feature available in SQL Server Enterprise and Developer editions. They are not available in Azure SQL Edge, for example, which is popular with developers using M1 Macs. For more information take a look at my original article.

Backups

What I didn’t mention in the article, is that Backups can serve the same purpose and have the added benefit that they are available in all versions of SQL Server, including Azure SQL Edge. Backups are typically more of an administrative feature but we can also use them to improve our debugging experience in our local environment.

Caveat: Creating a backup is generally slower than creating a snapshot. Backups involve copying the actual data to a backup file, which can be time-consuming depending on the size of the database. In contrast, a snapshot is created almost instantly as it records only the state at a point in time and then uses copy-on-write for changed data. I haven’t found it to be a significant difference for my scenarios, although it would depend on the size of the db etc.

Commands

Create the backup of our point-in-time that we’ll want to return to.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

USE [master];

ALTER DATABASE [WideWorldImporters] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

BACKUP DATABASE [WideWorldImporters] 
TO DISK = '/var/opt/mssql/data/WideWorldImporters_Backup.bak' 
WITH FORMAT,
    MEDIANAME = 'WideWorldImporters_Backup',
    NAME = 'WideWorldImporters_Backup';

ALTER DATABASE [WideWorldImporters] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

Note: we use SET SINGLE_USER WITH ROLLBACK IMMEDIATE to prevent other users/processes from attempting to use the database while the backup is in progress. Once it’s finished, we then use SET MULTI_USER WITH ROLLBACK IMMEDIATE to allow the db to accept connections from other users. I’ve found that this just helps to prevent contention that can happen from time to time.

Restore from the backup to our desired point-in-time.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

USE [master];

ALTER DATABASE [WideWorldImporters] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE [WideWorldImporters] 
FROM DISK = '/var/opt/mssql/data/WideWorldImporters_Backup.bak' 
WITH REPLACE, RECOVERY;

ALTER DATABASE [WideWorldImporters] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

Conclusion

SQL Server backups are useful to keep your dev loop as short as possible by allowing you to return your local database back to a known state very quickly.

Thanks for reading.