How to retrieve a specific table or rows from database backups or transaction log backups in SQL Server

This article describes how to retrieve a specific table or rows from the database and transaction log backups if you do not want to restore your original database. This can be helpful if someone accidentally deletes a table or rows, or to retrieve specific data that existed at an earlier date.

You must have a sequence of database and transaction log backups for the procedures in this article to work:

• If you do not have transaction log backups but only full database backups, you can only retrieve data that existed at the time that you took the last full database backup.
• If you have never performed a full database backup, you cannot retrieve earlier data.

Note Because you are retrieving data that is not consistent with the current state of the database, your referential integrity might break. You may experience constraint and key errors when you try to copy the data back in to the original database. You must take appropriate steps to address any referential integrity issues.

Back up the current transaction log

Back up your current transaction log with the NO_TRUNCATE option. For more information, see the "BACKUP" topic in SQL Server Books Online.

Restore a partial or full database backup

1. SQL Server 2000 or SQL Server 2005 If your backup strategy includes filegroup backups, you can perform a partial database restore to a different location and restore only the part of the database that contains the table that you want to retrieve. For more information about this method, see the "Partial Database Restore Operations" topic in SQL Server Books Online, and then see the "Restore Transaction Log Backups" section in this article.
   
   Note If you are using SQL Server 2005, for more information about the partial database restore operations, visit the following Microsoft Developer Network (MSDN) Web site:
   http://msdn2.microsoft.com/en-us/library/ms191468.aspx
   Note If your strategy does not include filegroup backups, or if you are on SQL Server 7.0, go to step 2.
2. If you do not have filegroup backups or are on SQL Server 7.0, restore the database from the full database backup that you want to a different database name and do not recover the database. This creates a copy of the database that contains data from the time when you took the backup and leaves the database in a state ready for transaction log backup restores. You can restore the copy to the same server or to a different server.
3. See the following references for more information about how to restore a database to a different name and location:
   SQL Server Books Online
   "RESTORE"
   "How to restore files to a new location (Transact-SQL)"
   For more information, click the following article number to view the article in the Microsoft Knowledge Base:
   
   
   221465 INF: Using the WITH MOVE option with the RESTORE statement
   
   

Restore transaction log backups by using the STOPAT option

Restore the transaction log backups to the partial or full database restore and stop before the point in time when the table or rows were deleted. Use the STOPAT option of RESTORE LOG command to stop the transaction log restore and to recover the database before the time when the table was deleted.

Note You do not have the option to selectively skip the time when the data was deleted. You can only recover up to the time before the data was deleted and no later.

See the following reference for more information about how to restore to a point in time:

Retrieve data

After you restore the database, you can copy the table or rows back to your original database by using either INSERT, Bcp (Bulk Copy Utility) or SELECT INTO. (This article discusses INSERT, bcp and SELECT INTO. You can also use DTS, but this article does not recommend the use of DTS.

INSERT

Use INSERT to retrieve a subset of rows or only a small table. INSERT requires the most transaction log space when you insert the rows back in to the original database. If you have many indexes on the original table this increases the logging for the INSERT statement. You may want to drop the indexes before you perform the insert, and then re-create the indexes.

BCP

Consider using the Bcp utility when you must retrieve a whole table and if the table size is large.

1. If the table does not already exist, generate a script of the table if you do not already have one, and then re-create the table.
2. Use the Bcp utility to copy the table data (rows) from the restored database to a text file, and then from the text file to the original database. For more information about how to use the Bcp utility, see the "bcp Utility" topic in SQL Server Books Online.
   
   Note If you perform a "minimally logged" or "nonlogged" bulk copy, perform a full database backup of your original database after you complete the bcp. For more information, see the "Logged and Minimally Logged Bulk Copy Operations" topic in SQL Server 2000 Books Online, and see the "Logged and Nonlogged Bulk Copy Operations" topic in SQL Server 7.0 Books Online.

SELECT INTO

If the table was deleted, you can select the table from the restored database to your original database by using SELECT INTO. You do not have to create the table first with this method. SELECT INTO creates the table and copies the table data.

Notes

• SQL Server 7.0 You must have the select into/bulkcopy option turned on in your original database to select into a permanent table. Also, you must perform a full database backup after you complete the SELECT INTO. For more information about SELECT INTO, see the "SELECT (Transact-SQL)" topic in SQL Server Books Online topic, and then see the "INTO Clause" subtopic.
• SQL Server 2000 or SQL Server 2005 With SQL Server 2000 or SQL Server 2005, you do not use the select into/bulkcopy option if you do not need it to select into a permanent table. The amount of logging and whether you must take a full database backup of your original database after the select into depends on the recovery model of your original database. See the "SELECT Clause" topic in SQL Server Books Online and the subtopics "INTO Clause" and "Using Recovery Models".

Note Because you are retrieving data that is not consistent with the current state of the database, your referential integrity may be broken. You may experience constraint and key errors when you try to insert the data back in to the database. You must take appropriate steps to address any referential integrity issues.

Re-create indexes, triggers, and constraints

You must re-create any indexes, full-text indexes, triggers, and constraints if your original table was lost.

Important Because you have retrieved data that is from a different point in time than the current database, you may have broken the referential integrity of the database. Take the appropriate steps to resolve any referential integrity issues.

DBCC CHECKTABLE

Run the DBCC CHECKTABLE Transact-SQL reference on the new table to verify the integrity of the data. For more information, see the "DBCC CHECKTABLE" topic in SQL Server Books Online.