With all of SQL Server 2012’s new features, such as AlwaysOn HA/DR and ColumnStore, one of the lesser known features is something DBA’s should be aware of – the Database Recovery Advisor. With the addition of this new feature, manageability has been enhanced for SSMS.
A good backup strategy is essential in every SQL Server environment, and a good recovery strategy is even more critical There are different types of backups in SQL Server, such as the more commonly known Full, Differential and Transaction Log backups as well as the lesser known File/Filegroup, Partial, and Copy-Only backups.The recovery model of a database – FULL, SIMPLE, BULK-LOGGED – is key to determining the backup and restore requirements.
As databases grew exponentially, and terabyte sizes became more common, backups became more cumbersome to manage and store. SQL Server 2008 introduced native database compression, although 3rd-party compression software existed for previous SQL versions. These features and concepts can be reviewed in this MSDN article on Backup Overview.
One persistent feature throughout the SQL Server product evolution has the ability to Restore a SQL Server Database to a Point in Time. You can get a good topic description from the referenced MSDN article on how to restore a database to a point in time, using both the SSMS GUI as well as T-SQL. Of course, in order to recover this way, you have to ensure that the recovery model of a database is set to FULL, and you are taking regular Transaction Log backups throughout the day. How frequent these transaction log backups should occur depends on your company’s business needs and what the minimal acceptable amount of data loss is.
Under one common scenario, end-users would want to recover to a particular point-in-time due to user error, where perhaps several rows of data were accidentally deleted. For example, users may realize at 1:00pm, that at 12:15PM a large number of records were accidentally deleted from a database. The ideal case for recovery is to restore to a point in time before 12:15PM. In our scenario, the transaction logs are being regularly backed up every 30 minutes on the hour. The DBA then must restore the latest FULL backup from 10:00PM and then apply every single transaction log backup in sequence up and until 12:00PM. There are about 2 dozen log backups in this case. (see figure 1 below) But even before you can recover a SQL Server database to its latest point in time, you must often times back up the tail of its transaction log, to ensure minimal data-loss. This is known as the Tail Log.
Figure 1
A skilled DBA would need to know when the tail log backup is necessary, as well as the correct recovery sequence. Even with SSMS in previous versions, SQL Server wants to restore from the latest backup/log to the most recent possible by default. This process can get a bit confusing with all the different types of backups and multiple log backups.
When you right-click on a database, and select Tasks >> Restore >> Database, you can click on the ellipsis and a window will appear asking to input the desired date and time as shown in figures 2 and 3 below:
Figure 2
Figure 3
With the introduction of the Database Recovery Advisor, otherwise known as TimeLine Recovery, restoring databases to a point in time will be made much easier. The advisor provides a visual timeline which allows you to select a recovery time down to the second, for data you wish to restore.
Based on your selection, the advisor will create and perform the necessary commands and tasks to restore the database to the specified time. When you select a point in time, a restore plan that includes setting relevant restore operations is automatically generated. It will also let your know if you need the additional tail log backup, or to set the database to single user mode, and do this for you!
The Database Recovery Advisor is available with the SQL Server Studio Management tools v. 2012, under the Restore Database dialog box. Below is an illustration.
Figure 4
I hope this information is helpful, and becomes part of your knowledge base and a neat addition to your DBA restore toolkit.
========================================================
Robert Pearl is a SQL Server MVP and creator of the award-winning database and monitoring alert system, SQLCentric. He’s the founder of Pearl Knowledge Solutions, and recently joined Linchpin People, LLC, as a partner.
As a SQL Server community evangelist, he covers local SQL Server events in the New York City area and maintains the PearlKnows blog.
You can email him at rsp05@pearlknows.com, and follow him on Twitter @Pearlknows
