Restoring One Table: Restore a Copy and Move the Rows Back

A damaged table does not automatically justify replacing the entire live database. The practical route for restoring one table is to recover a separate copy and move only the accepted rows back.

Restoring One Table Starts With a Database Copy

A normal database backup is not a table-level package that RESTORE can unpack directly into an existing table. Recover the database under a different name, inspect the desired table there, and then write a narrowly scoped transfer. The live database keeps its independent state while you investigate the older version.

Restoring one table also needs a recovery objective: all historical rows, only accidentally deleted rows, or particular corrupted values. Define that population before the restore. A precise objective keeps the later transfer from expanding into a full-table overwrite simply because the older copy happens to be available.

I separate the recovery operation from the data correction. That separation protects unrelated changes made after the backup and gives reviewers somewhere to inspect candidate rows. Choose a test instance when production lacks the storage or resource capacity for the restored copy. Account for backup encryption keys and any dependent recovery files before starting.

The examples use a live database named Sales and a recovery copy named SalesCopy. Those names describe the workflow, not existing databases you should assume are present. Replace the example paths and identifiers only after checking the actual backup and approved destination. Never add WITH REPLACE simply to bypass a collision.

Inspect Files and Choose New Paths

Read the backup metadata before issuing the restore. FILELISTONLY identifies every logical file name that needs a destination. A backup with multiple data files needs a MOVE clause for each relevant file, rather than just the two names shown in this compact example. Inspect the backup-set position as well.

RESTORE HEADERONLY
FROM DISK = N'C:\SqlBackups\Sales_full.bak';
RESTORE FILELISTONLY
FROM DISK = N'C:\SqlBackups\Sales_full.bak';

Choose new physical paths that do not belong to the live database or another database. Confirm free space and write permissions for the SQL Server service identity. Logical file names come from the backup; physical names come from your destination plan. A similar-looking file name is not evidence that it is safe to reuse.

RESTORE DATABASE SalesCopy
FROM DISK = N'C:\SqlBackups\Sales_full.bak'
WITH FILE = 1,
     MOVE N'Sales_Data' TO N'C:\SqlData\SalesCopy.mdf',
     MOVE N'Sales_Log' TO N'C:\SqlData\SalesCopy_log.ldf',
     NORECOVERY, CHECKSUM;

The logical names here are placeholders to replace with FILELISTONLY results. Keep the copy inaccessible for normal queries while applying additional backups. NORECOVERY preserves the restore sequence; RECOVERY finishes it. These options are not interchangeable labels for the same intermediate state.

Choose the Desired Recovery Point

For recovery to the full backup's state, complete the preceding restore with RESTORE DATABASE SalesCopy WITH RECOVERY. For a later point, apply a compatible differential if useful and then the required log backups in order. The full recovery model and an intact log chain enable a specific time between log backups.

RESTORE LOG SalesCopy
FROM DISK = N'C:\SqlBackups\Sales_log_01.trn'
WITH FILE = 1, NORECOVERY,
     STOPAT = '2026-09-20T10:14:59';
RESTORE LOG SalesCopy
FROM DISK = N'C:\SqlBackups\Sales_log_02.trn'
WITH FILE = 1, RECOVERY,
     STOPAT = '2026-09-20T10:14:59';

This is the alternative path after restoring the full backup with NORECOVERY. Do not first recover the copy and then attempt to append these logs. Substitute a reviewed target time and an actual chain whose final backup includes it. If the available backups stop earlier, collect the missing log coverage instead of treating the target as reached.

Compare the Candidate Rows

Inspect the recovered rows before writing anything to the live table. Verify the table definition, required columns, identity properties, and keys on both sides. The example assumes an Orders table with OrderID, CustomerID, and Amount columns. Your transfer needs an explicit column list matching the actual accepted schema.

SELECT r.OrderID, r.CustomerID, r.Amount
FROM SalesCopy.dbo.Orders AS r
WHERE NOT EXISTS
(
    SELECT 1 FROM Sales.dbo.Orders AS l
    WHERE l.OrderID = r.OrderID
);

Missing keys are only one category. A row that still exists with changed values needs a separate decision. Compare those values and their business history before updating them. Copying an entire older table over its newer version can erase valid orders added or corrected since the recovery point. A table-shaped problem still requires row-level judgment.

Restoring One Table by Inserting Reviewed Rows

This illustrative transfer inserts missing identities and leaves existing rows alone. Run it only after the candidate population is approved and the application is controlled for the correction. A serializable transaction prevents the demonstrated missing-key check from being treated as an unprotected application-side precheck. Rehearse its locking behavior on a copy.

USE Sales;
GO
SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRY
    BEGIN TRANSACTION;
    SET IDENTITY_INSERT dbo.Orders ON;
    INSERT dbo.Orders (OrderID, CustomerID, Amount)
    SELECT r.OrderID, r.CustomerID, r.Amount
    FROM SalesCopy.dbo.Orders AS r
    WHERE NOT EXISTS
    (
        SELECT 1 FROM dbo.Orders AS l WITH (UPDLOCK, HOLDLOCK)
        WHERE l.OrderID = r.OrderID
    );
    SET IDENTITY_INSERT dbo.Orders OFF;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    SET IDENTITY_INSERT dbo.Orders OFF;
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    THROW;
END CATCH;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Use an independent session without an ambient transaction for this example. An approved subset needs an additional reviewed predicate or staging list; the demonstration currently selects all missing keys. Capture inserted keys and retain correction evidence when implementing the production procedure. Do not equate missing with automatically authorized.

Respect Related Tables and Triggers

Foreign keys can reject recovered orders whose customers no longer exist. Restore and review the related parent population rather than disabling the constraint to force success. Trigger code can change other tables, send work downstream, or enforce business rules when the INSERT runs. Inspect that behavior before choosing the transfer window.

I check identity state after copying explicit values and test the next normal insert on a rehearsal copy. Also review computed columns, rowversion columns, generated values, and unique constraints. They are not ordinary fields to paste back blindly. Preserve current rules unless the correction plan explicitly addresses a required exception.

Finish Restoring One Table and Remove the Copy

Which rows must exist after the correction, and which newer rows must remain unchanged? Validate both populations. Compare accepted keys and values, reconcile related records, and have the application owner verify the intended business outcome. A successful INSERT message does not answer those questions.

Keep the restored copy until recovery validation and evidence retention are complete. Then close your inspection sessions and remove the specifically named temporary database through the approved cleanup step. An archive is useful; an undocumented second production-looking database is a future puzzle.

USE master;
GO
DROP DATABASE SalesCopy;

Run that final statement only for the disposable copy after verifying its identity and clearing its users. Restoring one table is complete when accepted rows are back, newer work is preserved, and the temporary recovery environment has been deliberately retired.

Related reading on this blog: Undo Human Errors in SQL Server: SQL in Sixty Seconds #109: Point in Time Restore and Full, Differential and Log Backups: A Practical Guide.

Table recovery is not a blind replacement, it is a reviewed transfer from a separately restored database.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.