The old trick of playing with SQL Server — Database Snapshot

I’m pretty sure many of you used or are still using SQL Server. Let’s be honest, most of the apps are using RDBMS databases and the majority use SQL Server or Azure SQL Server (which is almost the same thing).

The Problem

If the database is big (or is a bacpac), usually takes a long time for it to be restored. Let’s imagine you are doing a bug fix, and you want to test it against some test data (from the database from the test environment). You will need to create a backup from there, restore it on your machine and test the fix. What if, the fix doesn’t work from the first try? You’ll need to restore the database again, and if it won’t work the second time, you’ll need to restore it again, and so on. If your database takes forever to restore, you’ll get frustrated, and no one wants a frustrated developer around them!

Solution

Use snapshots. I think they are in SQL Server since forever, it’s nothing fancy or new, but they are still not really used.

Now let’s play a little bit in this direction. To get the information from your database you need to execute the next

EXEC SP_HELPDB Test

The response with the database information

To create the snapshot you need to execute the next command

CREATE DATABASE TestSnapshot
ON
(NAME =Test,
FILENAME = 'C:\PoCs\TestSnapshot.dbsnapshot')
AS SNAPSHOT OF Test

Now you can manipulate the data of your database as you like; delete it, update it, anything, and to check if the data from the snapshot was not changed you need just to use the snapshot to query

use [TestSnapshot]
...your query...

To check all the snapshots you have you need to execute

SELECT name,database_id,source_database_id FROM sys.databases
WHERE source_database_id=5

As you can see you have a databaseId with value 5 (in my case). You can see the id, in my first image, in the query used to get database information.

To restore a database from a snapshot you need to use

RESTORE DATABASE Test 
FROM DATABASE_SNAPSHOT='TestSnapshot'

And to delete a snapshot is like dropping a database

DROP DATABASE TestSnapshot

Hope it will help save your time.