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:
- Restore the database on a test server
In SQL Server Management Studio, right-click the database, select Tasks and then Generate Scripts
In the object list, select the dropped table
In the Set Scripting Options dialog, specify the file path and name
- 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
- 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
- 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.
- Start ApexSQL Diff
- In the Project Management dialog, click New to create a new project
- In the Source pane, select Backup as the Data source type
- Click Add files, add a full database backup, or a full database backup and a chain of differential database backups, and check them
- In the Destination pane, select Database as the Data source type
Select the live database and authentication type
- Click Show Advanced tabs
In Object filter, check Use filter for the tables
To have all objects that depend on the dropped table correctly scripted – triggers, tables, etc. leave all object types selected.
In the Tables filter pane, check only the dropped table
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:
- Select the table in the main grid
- Click Synchronize in the menu
- To script the objects the dropped table depends on, select them from the dependent objects list
- Go through the Synchronization wizard
In the Output options step, select Create a synchronization script as the output action
- Click Done
The generated script will be shown in the ApexSQL Diff internal editor. Review it, modify if needed, and execute
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.
- Start ApexSQL Log
- Connect to the live database in which the table has been dropped
- 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
- Click Next
In the Operations tab, uncheck all data (DML) and schema (DDL) operations. Check only DROP Table in the Schema operations list
- Click Next and select Open results in grid option
- All drop table operations that exist in the online transaction log and added transaction log backups will be shown in the main grid
Select the table you want to recover, select Create undo script option from the menu
Once the undo script has been created, review it , and execute
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.
- Start ApexSQL Recover
Select data recovery From DROP TABLE operation option
- Connect to the live database
- Click Next
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
In the next step, Add the transaction log backups and the detached transaction logs
- 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
- 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
- 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
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