12 Snapshots
Have you ever pressed F5 to execute a perfectly crafted update statement, only to discover you didn’t highlight the where clause? Or perhaps you ran a whole script when you meant to run only a select portion? If only SQL Server had a built-in undo option. Well, good news—database snapshots can help here!
In the previous two chapters, we talked about backups and restores. The first thing to note about database snapshots is that they do not take the place of a solid backup strategy. However, they can be really useful for rapid rollbacks in certain situations.
We actually debated on whether to include this chapter because snapshots aren’t the most popular SQL Server feature, likely due to their inaccessibility within SQL Server Management Studio (SSMS). We hope that you’ll give snapshots a chance if you don’t have experience with them because they can be incredibly useful. We have relied on snapshots during our careers both for fast rollback scenarios when things have gone wrong and for reporting on certain points in time.
12.1 Snapshots and SSMS
Snapshots are simply a read-only copy of a database at the point in time that the snapshot was created. As time moves on and data in your source database changes, the original pages are stored in a sparse file. This means that, initially, snapshots use very little space at all. However, if you have an active database, it is vital to ensure you have ample disk space to support the snapshot. A snapshot running out of space is one of the biggest pitfalls when using this technology.
We mentioned earlier that snapshots aren’t a very popular SQL Server feature. Because they aren’t built into SSMS, the only way to create a snapshot without dbatools is to manually write T-SQL, as shown in figure 12.1, and while writing this T-SQL, you have to explicitly specify all the database files that will be part of the snapshot. If you have a large database, it’s possible you will have quite a few files to include in this statement.
Figure 12.1 The only option (aside from using dbatools) to create a snapshot is to use T-SQL and specify all of the data files.
As an aside: you may notice that, in figure 12.1, we’re logged in as sa. Although this is considered a bad practice in production, in this case, we’re working with a Docker container, and using the sa account within throwaway Docker containers is a common practice.
12.2 Application upgrade
Let’s tell a story about how much easier dbatools can make snapshot creation. It’s a Friday afternoon, and you’re drinking a cup of tea, coffee, or any beverage of choice, while finishing up a few tasks before the weekend. Because it’s Friday afternoon, every DBA knows to perform only low-risk tasks! But then, you get an email asking you to urgently run a script as part of an application upgrade that you knew nothing about. Obviously this scenario is just fiction—it’s more than likely you had plenty of time to prepare and knew for weeks in advance that there would be this requirement.
You look at the script, and it’s reasonably simple—a few updates to stored procedures, a dropped table here or there. You also know you have a good backup strategy in place, and if things go poorly, you can easily revert to a previous backup, and no harm is done. Except, in this case, the database is huge—the amount of time to restore the whole database does not fit in the amount of time left before you plan to leave for the weekend.
How long will that restore take? Hopefully, after reading chapter 10, you’re regularly testing your backups with Test-DbaLastBackup and storing that data in a table. That way, you can quickly look up the last restore to see exactly how long that took.
This is a perfect scenario for database snapshots to save the day.
12.3 When to use snapshots
As previously mentioned, database snapshots start off small because no data has changed. As changes are made, the original pages are stored, in case we need them. The more data that is changed, the bigger the snapshot will grow, and the longer it will take to revert back to that snapshot if and when the time comes.
One point to consider when deciding whether snapshots are a good fit for your use case is to think about how much data will be changed. If you are running an upgrade script, like in our example, this is going to result in a small amount of changed data, compared to the total database size. This means snapshots would be a great tool for this scenario.
On the flip side, if you are truncating all data and rebuilding a database, the size of the snapshot and the additional IO to maintain both the source database and the snapshot will probably not be worth the benefits gained from using a snapshot. Instead, in that case, you might use a full database backup.
12.4 Creating a snapshot
Creating a snapshot with dbatools is as easy as running the command in the next listing.
Listing 12.1 Creating a database snapshot
This is the quickest and easiest way to create a snapshot. The snapshot will be named with the database name and a date suffix by default. The files for the snapshot will be placed in the same folder as the source database data files.
12.5 Upgrading
Now that you have a snapshot in place, the application upgrade feels a little less risky. The application team has sent you the scripts to run for the upgrade process. Part of the code, shown in figure 12.2, runs an ALTER PROCEDURE statement, which tweaks a stored procedure to allow updates to the VacationHours column of the Employee table.
As you’ll notice in figure 12.2, while reviewing the code, not only was an additional property added to the SET command, but the WHERE clause is now commented out. This is a very simple example of an application upgrade. In the real world, we’ve seen upgrades that consists of thousands of lines of code to execute. You might guess what happens next.
You run the script and confirm the updates have been made. The application team carries on with the rest of their work, and you go back to your easy Friday afternoon. However, it’s not long before your phone rings: “Something doesn’t look right!” See figure 12.3.
Figure 12.3 All of the employees have the same values for NationalIDNumber, birthdate, and VacationHours.
After some investigation, you discover the troublesome procedure and then create a plan of action to resume service. At this point, to resolve this situation, you need to restore the data to how it was before the upgrade started.
12.6 Rolling back the entire database from a snapshot
We already mentioned this is a large database, and you need to restore only one table and redeploy the stored procedure code as it was before the upgrade. This is where using database snapshots can save you a lot of time and effort. You also have two options on how to recover in this situation: you can roll back the whole database, or you can just pick out the parts you need.
First, we’ll look at how to roll back the whole database to the exact state when the snapshot was taken. This is the safest option because you know the data will be in a consistent state across the whole database. dbatools makes this process easy, too!
Warning Reverting to a database snapshot does break the LSN chain for your database, so make sure to take that into consideration, and perhaps run a new full database backup once you’re back in a good state.
It’s important to make sure no sessions are connected to the database before you run the restore. In the next code listing, you can see an example of using Get-DbaProcess and Stop-DbaProcess to kill any connected sessions before then using Restore-DbaDbSnapshot to actually roll back the database.
Listing 12.2 Rolling back the database to the point the snapshot was taken
PS> Get-DbaProcess -SqlInstance mssql1 -Database AdventureWorks | Stop-DbaProcess PS> $splatRestoreSnapshot = @{ SqlInstance = "mssql1" Snapshot = "AdventureWorks_20210530_071605" ① } PS> Restore-DbaDbSnapshot @splatRestoreSnapshot
① The name of the snapshot to restore. This is displayed when the snapshot is created, or you can find it using Get-DbaDbSnapshot.
At this point, because you rolled back the entire database, you have recovered both the stored procedure and the table data from before the upgrade.
Warning You can create multiple snapshots on a single database. However, to restore the entire database from a snapshot, you can have only one snapshot, so clean up any you don’t need before attempting the restore. You can quickly view and select multiple snapshots to clean up by using the -Passthru parameter on Out-GridView to create a GUI pop-up, as shown next:
12.7 Restoring certain objects or data from a snapshot
The second option we have when restoring from snapshots is to just pick certain objects from the earlier version of the database. Because database snapshots are read-only copies of the database from the past, you can browse the objects through SSMS or run T-SQL against them as if they were a regular database, as shown in figure 12.4. You can now restore just the affected data and code, instead of the full database.
In the scenario that an application upgrade has gone wrong, it is easier to roll back the whole database to the snapshot. As we’ve mentioned, this ensures our data is consistent across all tables. However, in certain cases, where just a few rows are affected and new data is still being written to the database, selecting only what you need from a recent snapshot can be a huge time-saver. You can even use Copy-DbaDbTableData to easily copy data from the snapshot back into the source database.
12.8 Cleaning up
As we mentioned, a database snapshot starts off small and grows over time as data within the database is changed, meaning that the snapshot must keep track of the original page. You’ll experience a slight performance hit on write activity to the database to keep the snapshot up to date. Therefore, it’s important to remove any snapshots you no longer need to both free up the space and reduce the overhead.
You shouldn’t be surprised by this, but dbatools has simplified cleaning up snapshots as well. As you might remember, you can have more than one snapshot on a database at one point, but you can clear all snapshots with just one line of code, as shown in figure 12.3.
Listing 12.3 Removing all snapshots on a database
12.9 Reporting
As we’ve seen, snapshots can be really useful to quickly roll back changes made to our databases, but they offer other uses as well. Because database snapshots are a read-only, static view of the source database, they are also great for reporting.
One use case would be if the business needed to report on the state of the database at the end of each day. If certain batch jobs run each night, you could add a step to those to create a snapshot when the jobs complete. Using the -Name parameter of the New-DbaDbSnapshot command, you can specify a certain naming convention, perhaps like in figure 12.5, where each snapshot has the date appended to its filename.
Having snapshots from subsequent nights will also allow you to compare certain data over time as it was when each snapshot was taken.
12.10 Hands-on lab
-
Create snapshots for multiple databases at once by using the
-NameSuffixparameter. -
Create a snapshot on a different drive from your database data files by using the
-Pathparameter. -
Alter some data, and then roll it back by using your snapshot.
-
Clean up all the snapshots you created to prepare for the next chapter.