How to Recover A Dropped Table?

In my previous blog, I showed you how to find a user who dropped a table and I recommend reading my previous blog before this. With this blog, I  am going to show you some simple but powerful steps to recover a dropped table. Someone accidentally dropped a production table which caused chaos to your business unit. The application crashed with an error. The client escalated the issue to your directors and the conference bridge is opened for status & updates.

The missing table is not a simple look up table or some small table that can be re-built within few minutes. This is a huge table with 5 million records that stored client billing information. This incident is causing the billing system to go down and is impacting the client's revenue therefore it is critical to restore the table as soon as possible.

How you can recover it? I have been in a similar situations before and I have recovered tables from Oracle and SQL Server database. I am going to discuss the recovering strategies and the steps required to recover dropped table from both the database technologies as a bonus.

Let’s start with Oracle and then to move on to SQL Server. Are you ready to see explore the DBA magic?

ORACLE

Recycle Bin: Oracle has a recycle bin that is similar to that of your window’s Operating Systems. The deleted  objects from your machine are stored inside the bin. Oracle uses similar technologies like that of Windows OS. The dropped objects in Oracle are renamed and moved to recycle bin. You can restore the dropped objects anytime or empty it forever to re-claim drive space. The PURGE command along with drop statement will drop the object permanently from the server therefore it is a good practice not to use purge even if the request says they do not need the table at all. Trust me, they will come back to you in few days and ask you to recover it.

 Oracle has dba_recyclebin & recyclebin views. Only DBA has access to dba view, the other one is a user view. User view is where you see all your dropped objects. DBA has access to all the dropped objects happening inside a database instance.

Here, we will create a test table, insert some records, drop the table and we will recover it from recycle bin.

Create a table.
CREATE TABLE test_table (id NUMBER, desc VARCHAR2(100));
Insert records
Insert into test_table (id, desc) values ( 101, ‘Intro to CS’);
Insert into test_table (id, desc) values ( 205, ‘Intermediate CS’);
commit;
Drop table
drop table test_table; 
The drop objects can be recovered only if you can see it inside a recycle bin. When Oracle moves to recycle bin it renames the object to something that starts with BIN$_your_object_name.
Check if the object exist
select * from recycle bin;
select * from dba_recyclebin;
Recover table from recycle bin.
Flashback table test_table to before drop;

This is a great feature to have and every great feature comes with some pitfalls. The constraint name of the retrieved objects get changed to something system generated name. I do not see this impacting an application unless you have forced query with HINT.  To comply with  your company naming standard, you must change the constraints name.

Export & Import: The table got  purged therefore it is not in a recycle bin. Recycle bin isn’t an option for us. The next best option is to check if there is an export dump file for this schema or objects. Most companies I worked for runs a script to export all the tables along with its data every night. Check the location of your export, run date, and the objects name. If you have an export dump, run the import to your test database instance. Have your requester verify the structure and data and then move to production db.

If your company does have an  export dump, make a note and talk to your manager on how you could have imported the table in a short period of time. Make a plan to implement an export script and schedule it to run daily.

Other Resource: What if the table was purged and the company doesn't  have an export dump. I think we still have few other options before working with a tape backup. This option is sometime hard to think especially when you are operating under a stressful situation. If you know the application, database design, and the database architecture. There are chances that the company is storing this table in other  databases. Find out  if there is a Materialized View off of a table. If there is one, you can have developer point to a materialized view while you are recovering from the view.

Applications data are moved to data warehouse or other db instance  for reporting or archiving purpose. Check if the table is stored somewhere in a data-warehouse or other in archiving schema. If you don't know that answer of these questions, you can start by asking to a  developer or a DBA who primarily supports the account.

Backup: Know how the backup is done in your organization. Get yourself familiar with backup and restore strategies. This helps you save your time and your job. Most company stores their backup in tapes which takes several days to recover. While other stores in a fast recovering media. Knowing your backing up media also help you decide when to recover from backup vs other recovering strategies. This should be your last options if you are to recover from tape backup.

MS SQL Server
SQL Server has different architecture than Oracle therefore it does not have a recycle bin. You are out of this recovering strategy. The only way I know is to recover from a backup.  Use third party tools or restore the database from the backup to your test database and then copy table along data to your production db instance. If  anyone knows a better way, please share it with everyone.

Best practices around dropping a DB Object.

• Rename the object that you are asked to drop.
• Make an export dump or a backup copy of  a table structure, data, privileges etc.. to your schema.
• Wait for few weeks or till no one screams at you.
• Drop the table from a database.
• Again wait few more weeks, if no one screams, purge it from recycle bin If you are on Oracle.

I  follow these simple practices while I get a request to drop objects and I recommend you all to follow these as well. Some might call me crazy but I am sure you will thank me later as why asked you to follow these best practices.