carloscastilla - Fotolia

Get started Bring yourself up to speed with our introductory content.

SQL Server database recovery tool retrieves by the table

Kroll Ontrack's new SQL Server database recovery tool was developed from customer feedback. It recovers selected tables instead of the whole database.

Michael Donnelly, president of the Minnesota SQL Server User Group, has worked as a database administrator (DBA)...

and a database architect during his career. As a DBA, time was always of the essence, but one thing he wouldn't delegate was restoring the database from backups. He considered SQL Server database recovery too technical a task to leave to junior DBAs or developers.

Had restoration been less intense, "it would be hugely beneficial to hand off tasks to some of the junior DBAs," said Donnelly, who today is a consultant at Digineer.

Donnelly described the process he had to go through to restore a 300 GB file. While he needed only the one table -- the file -- he still had to restore the entire database from backup and then move it to the development environment. Before that point, however, he had to clean the development environment so the database would fit. He believes it would have been much easier if he had to copy only the tables he needed.

After hearing about this challenge from Donnelly and other SQL Server users, Minneapolis-based data-recovery company Kroll Ontrack Inc. jumped at the business opportunity. The resulting product, a SQL Server version of the vendor's Ontrack PowerControls software, became generally available in November. It lets users select and restore just the tables they need using drag-and-drop functionality.

The new product is welcome news for Shannon Roderick, a database administrator at Gillette Children's Specialty Healthcare in St. Paul, Minn., who, along with Donnelly, participated in alpha testing for Ontrack PowerControls for SQL.

At his job prior to Gillette, Roderick frequently had to restore a database after a developer made an incorrect update. It would then take approximately 20 minutes to get the data back. Roderick used Dell's LiteSpeed software to perform the restoration, which wasn't as quick or efficient as he wanted.

"When you update production," he said, "and everything goes haywire, you want [the data] back as soon as possible."

Kroll Ontrack began the alpha test in July 2014. It found its testers through the Minnesota SQL Server User Group. Donnelly feels that Ontrack PowerControls allows DBAs to hand off database restoration responsibilities. "Even a developer who is a little bit SQL-Server-savvy can use this [product] to input their data," he said. "I've seen third-party tools with something similar to this, but never something with drag-and-drop functionality."

However, Donnelly has a few requests for later versions. Ontrack PowerControls is currently rated only for SQL Server 2012, and Donnelly uses SQL Server 2014. He also specifically asked for referential integrity in version two.

Roderick has been using Ontrack PowerControls for development, administration and recovery. However, he did not use it for recovery in the production server during the alpha test. "It's quick and easy to get into and doesn't have a lot of overhead," he said.

He noted that with Ontrack PowerControls, there isn't any need to go into the production server. All he has to do is go into the backup and pull the information. Compared to LiteSpeed and similar products, Roderick said, "It's more efficient for the one task it does well."

Roderick added that Ontrack PowerControls is also workflow-oriented. "It's a very, very targeted recovery," he added. Roderick wants to see more granular recovery in the next version.

"The best part about [the alpha test] is that Kroll has been so interested in getting feedback from the community," Roderick said. Roderick and Donnelly have met regularly with Kroll Ontrack representatives and made suggestions that in turn they saw incorporated into the product.

Kroll Ontrack's involvement with its alpha users has been far greater than in past tests, said Tom McCaffrey, product director for enterprise solutions. However, McCaffrey said, the process has been such a success that management at Kroll Ontrack is encouraging other teams to implement the same tactics.

McCaffrey intends to continue meeting with the Minnesota SQL Server User Group over the next year to get more feedback on the SQL Server database recovery technology. He said Kroll will be releasing monthly updates to Ontrack PowerControls for SQL, with the added features to be determined by user request. The top new feature right now is increasing the granularity of the restore -- Roderick's request. In the next release, according to McCaffrey, users can expect to see Donnelly's requests -- support for SQL Server 2014, 2008 and 2005, and enhanced referential integrity -- as well as compressed backups.

Jessica Sirkin is associate site editor of SearchSQLServer. Email her at [email protected] and follow us on Twitter: @SearchSQLServer.

Next Steps

Find out how and why to split your backup data into multiple files

Learn about SQL Server backup and restore best practices in this tutorial

Check out another third-party SQL Server recovery tool

Dig Deeper on SQL Server Backup and Recovery