Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Rapid SQL Server Table Recovery with Ontrack PowerControls


By:   |   Read Comments (3)   |   Related Tips: More > Database Administration

Problem

We need to recover a SQL Server table immediately.  Someone has dropped one of the main tables from one of our core databases.  This means our production system is down, users are telling all of our customers that our systems are down, our web site is in maintenance mode and every member of our upper management team is camped out at my boss's desk.  What's the fastest way to recover a SQL Server table?

Solution

Good people make mistakes.  But the cost of downtime becomes readily apparent when core business systems reliant on SQL Server are unable to process user requests.  Regardless of whether a table is dropped, an UPDATE or DELETE statement has the wrong WHERE clause or a deployment goes awry, you need to be able to recover your data immediately.  So how do you recover this SQL Server table as quickly as possible and minimize the downtime?  Here are some options:

  • Restore the database to production then migrate the data
  • Overwrite the existing database
  • Restore the database to a Development or Test server then migrate the data to production
  • Retrieve the data from a Reporting, Development or Test environment
  • Rebuild the data from scripts
  • Access the data from a High Availability or Disaster Recovery instance

Although these are all viable options here is the reality for many environments:

  • Insufficient Storage - It is difficult to tell the business that we cannot restore the databases because we do not have sufficient storage even though we have an expensive SAN
  • Extended Restore Time - Even if I could restore the database, to move the files around and restore the database will take more time than the business finds acceptable
  • Data Out of Date - Our Development and Test environment data is really old and Reporting is older than our last backup
  • No Data High Availability - Clustering and other High Availability solutions will not help when a table is dropped because I just cannot push a button to get the data back

When you are faced with one of these situations, what do you do?  I would like to introduce Ontrack® PowerControls for SQL Server from Kroll Ontrack.  This tool gives you the ability to access SQL Server tables directly from a full backup and immediately preview and recover the data to one of your databases.  This eliminates the time needed to restore a database and to find additional storage for a new database.  The data preview and recovery is lightning fast to get your SQL Server back online when the unexpected occurs.  This is all possible from Kroll Ontrack with more than 30 years of data recovery experience.  They have recently introduced Ontrack® PowerControls™ for SQL Server as a do it yourself option for the SQL Server community.  This technology is built upon the success Kroll Ontrack has had with Exchange and SharePoint.  Let's see how this all works.

How does Ontrack PowerControls for SQL Server work?

The process to preview and recover a SQL Server table with Ontrack® PowerControls™ for SQL Server cannot be simpler.  The tool has a straightforward wizard to identify your source backup (*.bak) or database file (*.mdf, *.ldf, *.ndf) then define the destination database for the data.  The tables in the backup load rapidly in the tool and the data preview loads as quickly as you click on a table.  Recovering a table is as simple as dragging and dropping the table from the top source pane to the bottom target pane.  At this point the data is copied from the read-only backup to the SQL Server database selected.  As the data is being recovered a summary screen is shown outlining the progress.  Here is how it is done.

Step 1 - Ontrack PowerControls for SQL Server Wizard

 

Step 2 - Preview the SQL Server Backup Data in Ontrack PowerControls

But what about referential integrity?  How is that handled?  Ontrack PowerControls for SQL Server has it covered.  The tool will detect the foreign key relationships with the tables you are interested in recovering and notify you of the relationships.  Since you know your database the best, you have the control to determine how the corresponding tables are recovered.

 

Step 3 - Fast Table Recovery via Drag and Drop of the Table from the Source to Target Pane

 

Step 4 - Review the Source and Recovered Data

 

At this point, as a SQL Server Professional you can take the next steps to bring your SQL Server online including:

  • Joining between the recovered data and your online system to update the production table
  • Append the deleted data to your production system
  • Completely overwrite the entire production table

With a clean version of the data, you have the freedom to use your expertise to take the next steps to bring your SQL Server database online.

 

How else can Ontrack PowerControls for SQL Server help me?

From a business and technology perspective, let's see how Ontrack PowerControls for SQL Server can help you with the following:

  • Dropped table recovery
  • Return to a known good state after deployment issues
  • Recover from an incorrect WHERE clause for an UPDATE or DELETE statement
  • eDiscovery and Compliance
  • DBA Research
  • Industry Audits
  • Point in Time Reporting on Specific Tables

 

How do I get started?

The best way to get started with Ontrack PowerControls for SQL Server is to download a free trial and test it out for yourself.

Here are the next steps:

  1. Install Ontrack PowerControls for SQL Server to conduct some testing.
  2. In a development or test environment, check out the most recent backup for one of your large core databases.
  3. Think about some recent data recovery issues then put PowerControls for SQL Server through the paces to retrieve the data and get back online.
  4. Think about the number of times over the last year there has been a need to recover data then try to estimate the time, manual processes and business impacts associated with the data recovery.
  5. Calculate your costs related to these issues and share your findings with your team to decide if Ontrack PowerControls for SQL Server can help you.

 

Next Steps
  • Having the need to recover your SQL Server data is inevitable.  Although there are a number of options to complete this task, time is generally of the essence and the associated cost of downtime is high.  Rather than being caught off guard, be sure to have Ontrack® PowerControls™ for SQL Server in your tool chest to get your business online immediately.
  • Check out these additional resources

 

MSSQLTips.com Product Editorial sponsored by Kroll Ontrack Inc. makers of Ontrack® PowerControls™ for SQL Server.

Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Thursday, March 12, 2015 - 4:18:19 PM - JR Back To Top

Whenever it is a Product Sales Pich can we have it on the top above the title as 'PRODUCT EDITORIAL' so that users are aware of it instead of on the bottom as a fine print?


Thursday, March 12, 2015 - 2:01:11 PM - Jeremy Kadlec Back To Top

Aldo,

Good afternoon and great question.  At this point in time, Ontrack PowerControls for SQL Server would only be able to read from the full backup (*.bak), snapshot or a file copy version of the transaction log file (*.ldf).  In the future the team at Kroll plans to add functionality to support the scenario you have mentioned to recover from a full backup and transaction log backups.

Thank you,
Jeremy Kadlec
MSSQLTips.com Community Co-Leader


Thursday, March 12, 2015 - 10:28:08 AM - Aldo Gonzalez Back To Top

Hi Jeremy,

 

Please explain how this tool would assist in recovering a table with significant DML activity since last backup.  Specifically, could you comment on whether the tool can read from the tlog backups that followed the full backup?

Going back to previous backup data (several hours after) might not be an option if transaction log backups cannot be used.

 

Thanks in advance,

 

Aldo


Learn more about SQL Server tools