Consider a following scenario:
“The server user has deleted a table (accidentally) from his database and now he wants to retrieve that specific table from backup database file, which he has created and stored on his machine. Instead of restoring whole database files, he only wants to restore specific SQL Server table from backup file.”
Therefore, there is a need for a solution by which users can restore specific table from database backup file. In this blog, we are going to discuss about SQL Server table level restore (in brief) with all its requirements required for solving the problem.
Important Points To Be Verified Before Performing Table Level Restore
- It is mandatory to have both transaction log and full database backup file because if you are having only the full database backups, then you will be able to restore data, which exist at backup file creation time.
- If you are not having any database backup file or you have never performed full database backup, then you will not be able to perform SQL Server table level restore. Therefore, create a backup file of current transaction log with NO_TRUNCATE option.
NOTE: While restoring data from backup file, your referential integrity may get disconnected because the data that you are restoring is inconsistent with the current state of the database. Therefore, you may encounter constraints and key errors while copying data back into original database. It is recommended to fix the error (at instant) while procedure is taking place.
Restoring Specific SQL Server Table From Backup File
Restoration Tricks On Basis of The Server Version
- If you are using SQL server 2000/2005 or using backup strategy that comprises of filegroup backups, then you could perform a partial database restoration to different path. This will retrieve the specific part of database, which you want to restore.
- If you are not using filegroup backups, then you will have to restore database from full backup file (which is to be named differently) and don’t recover the database. This will generate a duplicate copy of data, which comprises of data that was on creation time and keeps the database in a ready state for restoring transaction log backup file. This approach for restoring database is also applicable for SQL Server 7.0 users.
NOTE: You could restore database copy on the same server or different server
Restoring Transaction Log Backups With Help of STOPAT Option
To perform SQL Server table level restore in transaction log backups (full or partial) and to terminate the procedure (before the point when the rows or tables were deleted), you can use a STOPAT option. With help of this option, you can stop the procedure before the time when the tables were deleted. However, this command does not provide an option to selectively bypass the time when data from database was deleted.
Retrieve Data: You can keep a table or row copy back to original database with help of INSERT/BCP (Bulk Copy Utility), or Select INTO. Below mentioned points are the detailed information about all these commands or utilities for learning the usage of them:
- INSERT: This will retrieve either a row subsets or a small table and requires a large amount of space for transaction log for inserting rows back to original database. If you are having indexes on the original table, then it will increase the logging for INSERT statement. Therefore, it is advised to drop indexes before performing insertion, and then re-create indexes.
- BCP Utility: If table is of large size and you are retrieving a complete table, then this can be performed using BCP utility.
- If in case the table does not exist, then generate a table script, and re-create the table
- In order to restore database in a text file format, copy the table data to text file with help of BCP utility. This will restore database to a text file and then from text file to primary database
- SELECT INTO: With help of this command, you can select a deleted table and perform SQL Server table level restore. This will create table on its own and copies the table data into it.
Re-create indexes, triggers, and constraints
In order to recover lost table, you must re-create any triggers, indexes, full-text indexes, and constraints.
Using DBCC CHECKTABLE Transact-SQL
Execute the DBCC CHECKTABLE Transact-SQL reference for verifying the data integrity.
Overall Conclusion
One can conclude up with the fact that there are various measures to perform SQL Server table level restore. It is not impossible to recover specific table from a backup file. Users can make use of different commands and utilities to retrieve deleted table or rows only & hence, accomplish their tasks.
EmoticonEmoticon