4 techniques for recovering lost tables due to DROP Table operation

An accidentally dropped table may be recovered in several ways. The choice of technique depends on the resources available for the recovery. The first choice is usually a database backup. But, even if you don’t have one, the dropped table may still be recovered (in some cases even quicker than if you had a backup).

Let’s see how this can be done:

Use SQL Server Management Studio

If there is a full database backup or a chain of differential backups containing the dropped table:

  1. Restore the database on a test server
  2. In SQL Server Management Studio, right-click the database, select Tasks and then Generate Scripts

    SSMS Object Explorer - Generate Scripts command

  3. In the object list, select the dropped table

    Selecting specific database objects to script

  4. In the Set Scripting Options dialog, specify the file path and name

    Specifying the file path and name in the Set Scripting Options dialog

  5. Click Advanced and check:
    • Generate script for Dependent objects – to script the objects that depend on the dropped table. Note that you cannot drop a table without removing the referencing constraint or the table first
    • Types of data to script – select Schema and data if you want to recover the lost records, too
  6. Once the script has been generated, you can open, review, and modify it. The script creates the dropped table first, and then inserts the dropped records:
    CREATE TABLE [dbo].[Orders](
    	[Id] [int] NOT NULL,
    	[OrderNo] [int] NOT NULL,
    	[CustomerId] [int] NULL,
    	[OrderDate] [date] NULL
    ) ON [PRIMARY]
    INSERT [dbo].[Orders] ([Id], [OrderNo], [CustomerId], [OrderDate]) 
    VALUES (1, 1000, 35, CAST(0x2A370B00 AS Date)) ALTER TABLE [dbo].[Orders] ADD DEFAULT (getdate()) FOR [OrderDate] GO
  7. Run the script against the original database

This method is simple and straightforward, the only setback being that it is not applicable if there’s no database backup. Also, if the database is large, backup restoration can be time-consuming.

Use ApexSQL Diff

This is another method where a database backup is necessary, but it doesn’t have to be restored to recover lost tables due to DROP Table operation.

ApexSQL Diff is a SQL server comparison and synchronization tool for SQL Server databases. It detects differences between database objects, generates comprehensive reports and synchronizes live and versioned databases, database backups, snapshots and script folders.

  1. Start ApexSQL Diff
  2. In the Project Management dialog, click New to create a new project
  3. In the Source pane, select Backup as the Data source type
  4. Click Add files, add a full database backup, or a full database backup and a chain of differential database backups, and check them
  5. In the Destination pane, select Database as the Data source type
  6. Select the live database and authentication type

    Selecting the live database and authentication type in ApexSQL Diff

  7. Click Show Advanced tabs
  8. In Object filter, check Use filter for the tables

    Checking the Use filter for the tables in Object filter

    To have all objects that depend on the dropped table correctly scripted – triggers, tables, etc. leave all object types selected.

  9. In the Tables filter pane, check only the dropped table

    Selecting the dropped table in the Tables filter pane

  10. Click Compare

    ApexSQL Diff will show the comparison results in the main grid. The dropped table and the objects that depend on it, if any, will be shown in the grid:

    The comparison results shown in the main grid of ApexSQL Diff

  11. Select the table in the main grid
  12. Click Synchronize in the menu
  13. To script the objects the dropped table depends on, select them from the dependent objects list
  14. Go through the Synchronization wizard
  15. In the Output options step, select Create a synchronization script as the output action

    Choosing the Synchronization output options

  16. Click Done
  17. The generated script will be shown in the ApexSQL Diff internal editor. Review it, modify if needed, and execute

    Batch synchronizing - ApexSQL Diff internal editor

The table and the dependent objects selected will be re-created

As ApexSQL Diff can read SQL Server database backups, the table recovery is faster as the backups are not being restored first. The objects depending on the dropped table are seamlessly recovered. The main grid shows the script and differences for every object, which provides granularity and helps in deciding which objects to select for synchronization.

If you don’t have a SQL database backup, this method is not applicable. Also, only the table structure can be recovered.

Note: ApexSQL Diff will only recover structure of the dropped table (and the dependent objects). In order to recover data, ApexSQL Data Diff needs to be used in the similar way to compare and synchronize data.

Use ApexSQL Log

Even if there’s no SQL database backup containing the dropped table, recover of lost tables due to DROP Table operation can be done.

ApexSQL Log is a SQL server transaction log viewer that audits, reverts or replays data and object changes. It restores deleted and modified data and objects, capturing information on the user, application and host used to make each change.

  1. Start ApexSQL Log
  2. Connect to the live database in which the table has been dropped
  3. In next step Add transaction log backups created after the table was dropped. Please note that the transaction logs have to form a full chain
  4. Click Next
  5. In the Operations tab, uncheck all data (DML) and schema (DDL) operations. Check only DROP Table in the Schema operations list

    Selecting only DROP Table in the Schema operations list

  6. Click Next and select Open results in grid option
  7. All drop table operations that exist in the online transaction log and added transaction log backups will be shown in the main grid
  8. Select the table you want to recover, select Create undo script option from the menu

    Create undo script selection in the context menu

  9. Once the undo script has been created, review it , and execute

    The undo script has been created

The advantages of this method are that no database backup is needed. As a DROP TABLE statement doesn’t create a log for each deleted table row in the online transaction log, this method can recover only table structure.

Note that if the database is in the Simple recovery model, the transactions might be overwritten. In this case, the sooner you run ApexSQL Log after the table was dropped, the more chances for successful recovery you have.

Note: Performing a table recovery with ApexSQL Log will only recover table structure, the data is not recovered.

Use ApexSQL Recover

As ApexSQL Recover reads both data (MDF) and transaction log (LDF) files, it can recover a dropped table even when the transaction doesn’t exist in the LDF file anymore. Furthermore, ApexSQL Recover recovers both structure and data lost due to DROP table operation.

ApexSQL Recover is a SQL Server recovery tool that recovers deleted, dropped or truncated data. It recovers objects and data lost due to drop operations and restores both deleted and online BLOBs as files.

  1. Start ApexSQL Recover
  2. Select data recovery From DROP TABLE operation option

    Data and Object recovery options selection

  3. Connect to the live database
  4. Click Next
  5. If there are transaction log backups created after the table was dropped, or a detached transaction log containing the very transaction responsible for dropping, click Add transaction logs

    Checking if there are additional data sources available for the recovery

  6. In the next step, Add the transaction log backups and the detached transaction logs

    Adding the transaction log backups and the detached transaction logs

  7. Select the time period when the table was dropped. If you’re not sure when this happened, leave the Date and time are unknown option selected
  8. In the Select a recovery action step of the wizard, choose Save a script to file option and provide output file location and click Next
  9. In the final step of the wizard, choose to recover data only, structure only, or both table structure and data, depending on the particular recovery process and click on the Recover button to finalize recovery
  10. Once the recovery script has been generated, review it, find the tables you want to recover, and execute

Thanks to reading both MDF and LDF files, ApexSQL Recover has more chance of successful recovery. However, if the information about the dropped table is overwritten in both MDF and LDF files, ApexSQL Recover cannot help.

Having database backups is a good starting point, but it might not be the quickest one. With backups, there are different methods – the ones that require a backup to be restored first, and thus require more time, and the others that can read backups.

If you don’t have a database backup, you can recover a dropped table from SQL database data and transaction log files. When it comes to recovery from a database in the Simple recovery model, it might fail as the drop table transaction might have been overwritten. Even then, recovery is possible from the MDF file.

August 15, 2013