A New Recovery Tool for Your Toolbelt This editorial was originally published on 15 Nov, 2016. It is being republished as Steve is traveling today. Those of us that administer databases and are responsible for ensuring the integrity and availability of data know that being able to restore systems is a core skill. In the SQL Server world, this should mean more than just being able to run a RESTORE DATABASE Sales FROM DISK = 'Sales.bak'. Many of us would easily recognize that we should be able to use the WITH MOVE option. We should also understand when to use (and not use) NORECOVERY and STANDBY. Perhaps more importantly, we need to know the default option is RECOVERY. If we delve further, perhaps we could debate and discuss when a tail log backup is needed. Many might mention how to programmatically use FILELISTONLY and HEADERONLY to generate scripts. A few of you would note that restoring databases means restoring logs as well and an understanding of the STOPAT option is important. Perhaps learning the ins and outs of restoring a filegroup is important in your environment. There are many nuances and options with restoring a database that a system administrator could learn. Apart from performing a restore, a careful DBA might think that we should try to avoid restoring databases if possible. Perhaps a DR system using Log Shipping can be used to recover data quicker than a database restore. Maybe periodic snapshots are valuable in recovering from those "whoops" mistakes. As our data sizes grow larger, we may need to develop more creative ways of recovering from user mistakes. Recently I saw the SQLCAT team had written a post on using Temporal Tables to recover from an "oops" mistake. If you haven't looked at these structures, they are new in SQL Server 2016. Temporal tables, or system versioned tables, allow a user to view a row as of a certain timestamp, which can be especially useful for DML mistakes. Have you ever updated every row of a table to the same value because of a poorly written query? I have. A temporal table would have been handy in this case. Perhaps even more useful are the cases where a table is receiving regular inserts and updates, which can be very difficult to recover from with backup files. Certainly the majority of us don't yet have temporal tables, and we might not even set up the option on many tables even if we were running SQL Server 2016. Over time, however, some of us will use these system-versioned objects, and perhaps we will run into a situation where recovery using temporal queries is more efficient than restoring an entire database. If that were to happen, wouldn't you want to have some skills in this area? As SQL Server continues to evolve, many of the techniques and skills we've built across time will continue to work. There will be new ways of accomplishing work, based on features and enhancements that we ought to consider, especially when they might be more efficient than our past techniques. As you work with SQL Server, I hope you keep learning about the "new" ways that you might accomplish the tasks for which you've developed solutions in the past. Sometimes the old way might prove to be a better choice, but you won't know that unless you learn, practice, and measure the effects of a new method. Steve Jones - SSC Editor Join the debate, and respond to today's editorial on the forums | 
