Restore a single table from a SQL Server backup

Somebody deleted the wrong rows, or a table has to be compared with how it was last Tuesday. You have the backup and you need one table, not the database. SQL Server has no RESTORE TABLE: a restore always brings back whole files. This article shows the standard way and a way that skips the restore.

The T-SQL way: restore under another name, copy the table

1. Find the logical file names of the backup:

   RESTORE FILELISTONLY FROM DISK = N'D:\Backups\Sales_FULL.bak';

2. Restore it as a new database, with its files somewhere that has room (the Size column of the previous query is what the files need):

   RESTORE DATABASE Sales_Temp
   FROM DISK = N'D:\Backups\Sales_FULL.bak'
   WITH MOVE N'Sales' TO N'E:\Temp\Sales_Temp.mdf',
        MOVE N'Sales_log' TO N'E:\Temp\Sales_Temp_log.ldf',
        STATS = 10;

3. Copy the table out of the temporary copy. SELECT ... INTO creates a new table in the live database:

   SELECT * INTO Sales.dbo.Orders_from_backup
   FROM Sales_Temp.dbo.Orders;

   To put rows back in the table that exists, use INSERT ... SELECT with a column list, and SET IDENTITY_INSERT ... ON when the table has an identity column.

4. Drop the temporary copy:

   DROP DATABASE Sales_Temp;

Things to know:

• SELECT ... INTO copies the columns and the rows, not the primary key, indexes, constraints or triggers.
• When the backup has several filegroups and the table sits in its own, a partial restore (RESTORE DATABASE ... FILEGROUP = ... WITH PARTIAL) brings back only the primary filegroup and the ones you name. It only helps when the table is in a filegroup of its own.
• A table from a backup is as it was at that moment. Tables that refer to it may have changed since; check foreign keys before you put rows back.
• Without room for the whole database, step 2 cannot be done. That is the usual reason to look at another way.

Without the restore: SQLTreeo Restore

SQLTreeo Restore reads the backup file directly, so there is no temporary database and no waiting for a restore of the whole backup.

1. Open the .bak file. The tables are listed with their rows and sizes; select one to see its columns and first rows and check it is the right one.
2. Tick the tables you need.
3. Choose Export to CSV… for files that open in Excel, or Copy to SQL Server… to copy them into a database on a server of your choice.

When you copy, a table that does not exist in the target is created with the same columns, types and collations, its primary key, unique constraints and clustered index, and its computed and sparse columns. Other indexes, foreign keys, check constraints, defaults and triggers are not created. When the table exists already, you choose once: add the rows, replace the rows, or cancel. Each table is copied completely or not at all, so a failure leaves the table as it was.

Without a license key, export and copy write the first 1,000 rows of each table, marked as an unlicensed preview (dbo.Orders (first 1000 rows - unlicensed).csv, or a table named dbo.Orders_preview). That is enough to check you have the right table. A license key writes every row.

Copy into a new database or a table with another name first when you want to compare before you change anything. Getting started has the steps, and How to open a .bak file without SQL Server compares all the ways to look inside a backup.

To see what changed between the restored table and the live one, compare them with SQLTreeo Compare: its data compare shows the rows that differ, key by key.