Recover the missing table while keeping the rest of the live database available. Recovering one dropped table starts by restoring the backup under a separate database name and separate file paths. Validate that copy, recover the table deliberately, and leave the live database's unrelated contents intact.
Select a Backup That Still Holds the One Dropped Table
Choose a full backup from before the table was dropped. If it is old, review the available differential and log backups to determine how close the restore can reach to the incident. A full backup taken after the drop does not contain the missing table simply because it is newer.
I establish the intended recovery point before restoring anything. The table's data after the selected backup is recoverable only when the retained sequence covers those changes. Without suitable logs, later rows are absent from the restored table. Do not describe them as recovered because the restore completed.
Confirm that the backup files exist and that the recovery host has enough disk space for the entire restored database. Recovering one table still requires the space needed by the separate copy. The backup is a database package, not a drawer that opens directly to one object. That distinction matters when storage is already tight.
Recovering one dropped table still requires a database restore that reaches the appropriate point before that DROP TABLE statement.
Inspect Logical File Names and Restore Separately
RESTORE FILELISTONLY lists the logical file names stored in the backup. Use those actual names in WITH MOVE, and provide a distinct destination path for every data and log file. The sample names below are placeholders for a prepared test backup, not invented output from your server.
Use a new database name that does not already contain valuable work. Do not add WITH REPLACE. The separate-name strategy keeps the live database out of the restore target. The folder must exist on the SQL Server host and be writable by its service account.
I leave the copy in NORECOVERY when additional backups will be applied. That permits the rest of the valid sequence. If the full backup alone is the chosen point, complete recovery afterward instead. Verify all logical files, because a backup containing more files needs more MOVE clauses than the two-file example. Incomplete file mapping is not a reason to overwrite an existing path.
RESTORE FILELISTONLY FROM DISK='C:\SqlBackups\AppDB-full.bak';
RESTORE DATABASE TableRecoveryCopy
FROM DISK='C:\SqlBackups\AppDB-full.bak'
WITH MOVE N'AppDB_Data' TO N'C:\SqlRecovery\TableRecoveryCopy.mdf',
MOVE N'AppDB_Log' TO N'C:\SqlRecovery\TableRecoveryCopy_log.ldf',NORECOVERY;
Apply the Log Sequence to Before the Drop
Apply any selected differential backup and all required preceding log backups in the supported sequence, keeping NORECOVERY. For the log containing the intended stopping point, use STOPAT with the approved incident time. Choose a time before the drop, not a guessed time copied from this example.
The next block shows the final log step and completion of recovery. Its timestamp and filename are synthetic placeholders. They work only when the actual retained backup sequence covers that time. If no log sequence is available, recover the full-only copy and accept the documented data gap.
What rows should exist at the chosen point? Validate that through the incident evidence and the restored table. A successful recovery state proves SQL Server opened the database, not that the selected time is the business point you intended. Check the table before copying anything back, and retain the source backup and restore history until the recovery is accepted.
RESTORE LOG TableRecoveryCopy
FROM DISK='C:\SqlBackups\AppDB-final.trn'
WITH STOPAT='2026-09-24T09:59:00',NORECOVERY;
RESTORE DATABASE TableRecoveryCopy WITH RECOVERY;Copy One Dropped Table Back With a Fitting Method
SELECT INTO is convenient when the live table is absent and you need a new table containing the restored rows. It does not recreate the complete original design: indexes, constraints, triggers, defaults, permissions, and other properties need separate scripting and review. A directly copied identity column does keep its identity property.
The first copy option below uses fully qualified database names. Verify the live destination and ensure the table does not already exist. Do not use this option as a substitute for a reviewed original definition when exact schema restoration matters.
For an already recreated empty identity table, use explicit columns and SET IDENTITY_INSERT ON to retain identity values. The second option assumes the original Orders definition has already been restored. Choose one option rather than running both blindly. Keep IDENTITY_INSERT cleanup visible on the error path, and validate the identity seed afterward through the supported schema and data checks.
SELECT * INTO ApplicationDB.dbo.Orders
FROM TableRecoveryCopy.dbo.Orders;BEGIN TRY
SET IDENTITY_INSERT ApplicationDB.dbo.Orders ON;
INSERT ApplicationDB.dbo.Orders(OrderID,OrderDate,Amount)
SELECT OrderID,OrderDate,Amount FROM TableRecoveryCopy.dbo.Orders;
SET IDENTITY_INSERT ApplicationDB.dbo.Orders OFF;
END TRY
BEGIN CATCH
SET IDENTITY_INSERT ApplicationDB.dbo.Orders OFF;
THROW;
END CATCH;Rebuild the Rules Around the Recovered Rows
Script the original table design from the restored copy or the approved schema authority. Recreate indexes and constraints in a planned order, validate the loaded rows, and restore permissions and trigger behavior. Foreign keys can involve other live tables whose current contents differ from the recovery point.
Do not disable those rules and call the recovery complete when the data does not fit. Investigate conflicts between restored rows and current related data. The incident response needs a business decision about reconciliation, not silent deletion of rows that make validation inconvenient.
Compare counts, keys, and important business aggregates between the restored source and chosen target. Use exact queries from your own execution rather than claiming a recovered row count in advance. Then test the application's normal reads and writes under its actual permissions. A table visible to an administrator is not automatically a working application object.
Remove the Copy Only After Acceptance
Keep the separate restore until the recovered table's data, definition, and application behavior have been accepted. It is useful evidence if another missing object or reconciliation question appears. Confirm no process still needs the copy before dropping it.
The cleanup below names only the dedicated recovery database. It does not force active sessions away or touch the live database. If cleanup is blocked, inspect the remaining use and resolve it deliberately rather than adding an unexplained forced rollback.
Recovering one dropped table preserves the rest of the live database when restoration happens elsewhere and copying is narrow. Choose the recovery point, map files safely, restore the complete table rules, and verify the result. The data gap and temporary disk requirement remain explicit throughout the work.
USE master;
DROP DATABASE TableRecoveryCopy;Related reading on this blog: SQL SERVER 2022: Last Valid Restore Time: Improved Backup Metadata and A Quick Script for Point in Time Recovery: Back Up and Restore.
A table recovery is not a live-database overwrite, it is a controlled extraction from a validated restore.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
