If restoration of an original database to a previous state is not an option, in the case of accidentally updated, dropped, deleted, damaged data, it is still possible to restore these tables if a database backup was created prior to the accident.
This can be achieved by restoring the latest backup prior to the accident into a different database, and then copying tables back to the original database. Since the retrieved data is not consistent with the current state of the database, referential integrity might be broken. Also, constraint and key errors may occur when the data is copied back in to the original database.
To forestall this, appropriate steps to address any referential integrity issues must be taken and all indexes, full-text indexes, triggers, and constraints must be recreated if the original table was lost.
To achieve this, the following steps need to be executed:
USE original_database GO SET IDENTITY_INSERT table_1 ON INSERT INTO table_1 (column_name) SELECT * FROM restored_database.table_1 SET IDENTITY_INSERT table_1 OFF
USE original_database GO SELECT * INTO table_1 FROM restored_database.table_1 GO
DBCC CHECKTABLE ("table_1")
The main problem with this solution is the fact that database backup has to be fully restored in order to extract only specific table(s). This process can take a lot of time or demand substantial amount of free space on the SQL Server, which are not always available.
There is an alternative to full backup restoration by utilizing ApexSQL Recover, a SQL Server recovery tool which uses information inside MDF and LDF files (and transaction log backups) to recover data lost due to delete, truncate or drop table operations as well as allows extraction of table structure and data directly from database backups without having to perform backup restore jobs.
To restore only specific tables/rows, following steps need to be executed:
After the process is complete, the extracted data/structure can then be copied to the original database, as described in the first part of the article.
Restoring a backup in order to extract only single or couple of tables can be time and space consuming process which may interfere with process of getting the database back to the healthy state. ApexSQL Recover can greatly enhance the process of extraction and cut the time/space needed for this job significantly.
"© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Conditions d’utilisation Confidentialité Cookie Preference Center