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

12.2 Application upgrade

12.3 When to use snapshots

12.4 Creating a snapshot

12.5 Upgrading

12.6 Rolling back the entire database from a snapshot

12.7 Restoring certain objects or data from a snapshot

12.8 Cleaning up

12.9 Reporting

12.10 Hands-on lab