simplifying snapshots

I remember the first time I saw database snapshots, I was so excited. Then I right-clicked in SSMS and..

No way to easily create a snapshot. So then I researched how to create one using T-SQL and I had to know the exact path. UGH.

intro to snapshots

Basically, database snapshots are similar to VM snapshots but for databases. If you update an application and its database, you can take a snapshot and if the upgrade goes poorly, you can super quickly restore the snapshot and even a multi-TB database can be restored in no time. Here’s a more technical definition from Microsoft.

A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot’s creation. A database snapshot always resides on the same server instance as its source database. As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space.

I’ve never done this but my BFF and DBA Brandon swears by it. He noted that up until SQL Server 2016 SP1, snapshots were solely available in Enterprise Edition. With SQL Server 2016 SP1 and up, they are available in every edition, even Express!

benefits

Benefits, as listed on Microsoft’s Snapshot page on docs are listed as follows:

  • Snapshots can be used for reporting purposes
  • Maintaining historical data for report generation
  • Using a mirror database that you are maintaining for availability purposes to offload reporting
  • Safeguarding data against administrative error
  • Safeguarding data against user error
  • Managing a test database

That last example is pretty cool and relatable. In a testing environment, snapshots can be useful when repeatedly running a test for the database to contain identical data at the start of each round of testing.

One thing Microsoft didn’t emphasize as a bullet-point is how useful snapshots can be when upgrading an application. As mentioned above the upgrade goes poorly, it’s fast and easy to revert.

snapshot commands

One of our awesome devs, Simone Bizzoto, created a series of snapshot commands which make them way easier to work with. Like most other dbatools commands, these commands are flexible but require as little information as possible by default.

Super important note: these commands were recently renamed to align with our 1.0 convention. Please update to the latest version (0.9.359) or none of this will work as expected.

New-DbaDbSnapshot

To create a new snapshot, you no longer need to know the path of the snapshot location (though we do support custom paths). You don’t even need to specify a name! But you can, of course.

Get-DbaDbSnapshot

Now that you have a couple new snapshots, let’s take a look at them.

Restore-DbaDbSnapshot

Something very important to note about snapshot restores: according to Microsoft, restoring/reverting a snapshot does not work in an offline or corrupted database. Ultimately, this means that snapshots are not a replacement for a solid backup/restore plan.

If you’re wondering how long a snapshot will take to restore, the answer is “it depends.” If you have very few page changes, it’ll be very fast even on a multi-TB database. If you had GBs of changes since you took the snapshot, it’ll take longer.

Remove-DbaDbSnapshot

When you’re finished with a snapshot, you should remove it. Why? All changes to your database will take up hdd space both in the database and the snapshot. This means you could potentially run out of disk space and if you do, your snapshots will become invalid.

Note that these commands will not remove your base database! Also, if you try to pass in a regular database object, the command will tell you it’s a real database and skip the drop šŸ‘

snapshots are šŸ”„

Hopefully, there will be a greater adoption of SQL Server snapshots both because their use has been simplified with PowerShell and because Microsoft has made this feature available in all editions of SQL Server 2016 SP1 and above.

Remember, though, that while snapshots are super useful, they are not a replacement for a solid backup/restore strategy.

- Chrissy

2 thoughts on “simplifying snapshots

  1. Pingback: Database Snapshots In dbatools – Curated SQL

  2. Bill Hughes Reply

    Wow! This is great. Such a great article about easy snapshot use. I knew about Snapshots but never used them because, well, there is no easy way to do so until I discovered dbatools. The team behind dbatools rocks! My administrative life has been made easier thanks you this team.

Leave a Reply

Your email address will not be published. Required fields are marked *