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]
    
    GO
    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

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. In the Project Management dialog, click New to create a new project
  3. Connect to the live database in which the table has been dropped
  4. Open the Transaction logs tab. If there are any transaction log backups created after the table was dropped, add them. Please note that the transaction logs have to form a full chain
  5. Open the Filter tab
  6. 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

  7. Click Open
  8. All drop table operations that exist in the online transaction log and added transaction log backups will be shown in the main grid
  9. Select the table you want to recover, right-click on it and select Create undo script in the context menu

    Create undo script selection in the context menu

  10. 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

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

ApexSQL Recover is a SQL Server recovery tool that recovers deleted, truncated, corrupted or damaged 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. Depending on what is being recovered, select one of the options below

    Data and Object recovery options selection

    To only re-create the table structure, select the Recover lost tables due to a DROP operation option

    To also insert the table data, select the Recover lost data due to a DROP TABLE operation option

  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, select Several sources are available for the recovery

    Checking if there are additional data sources available for the recovery

    1. In the next step, add the transaction log backups and the detached transaction logs using the Add backup and Add detached buttons, respectively

      Adding the transaction log backups and the detached transaction logs

      Otherwise, select No data source is available except for the database currently connected to

  6. 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
  7. If needed, modify the file path and name for the recovery script, and click Recover
  8. 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

Author: Milena Petrovic