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?
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:
- Install Ontrack PowerControls for SQL Server to conduct some testing.
- In a development or test environment, check out the most recent backup for one of your large core databases.
- Think about some recent data recovery issues then put PowerControls for SQL Server through the paces to retrieve the data and get back online.
- 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.
- 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.
- 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: 3/12/2015
About the author
View all my tips