SQLServer restore table-level data

In recent days, the company's technical maintenance staff frequently asked me to restore the database, because they always have fewer where conditions, resulting in unrecoverable consequences for the update and delete operations, the libraries are dozens of GB. It takes less than 10 minutes to recover. For this reason, I have found some materials and work summaries and provided several methods to quickly restore tables, instead of databases.

In recent days, the company's technical maintenance staff frequently asked me to restore the database, because they always have fewer where conditions, resulting in unrecoverable consequences for the update and delete operations, the libraries are dozens of GB. It takes less than 10 minutes to recover. For this reason, I have found some materials and work summaries and provided several methods to quickly restore tables, instead of databases.

In recent days, the company's technical maintenance staff frequently asked me to restore the database, because they always have fewer where conditions, resulting in unrecoverable consequences for the update and delete operations, the libraries are dozens of GB. It takes less than 10 minutes to recover. For this reason, I have found some materials and work summaries and provided several methods to quickly restore tables, rather than databases. However, it is better to guard against such attacks.

In the production environment or development environment, there are usually some very important tables. These tables store core data. When the data in these tables is corrupted, You need to restore them as soon as possible. However, databases in the formal environment are often very large. Statistics show that the restoration time of a 1 TB database is nearly 24 hours. Therefore, restoring a database because of a table is not just a space, even time is a big challenge. This article describes how to restore a single table without the need to restore the entire database.

Assume a table: TEST_TABLE. We need to restore the table as soon as possible and minimize the impact on other tables and users during the restoration process.

SQLServer (especially after 2008) has many backup and recovery functions: complete, partial, file, differential, and transaction backup. The selection of recovery mode seriously affects the backup policy and backup type.

The following are a few solutions for reference, but remember that each has its own good or bad, you should choose according to your actual needs:

Solution 1: Restore to a different database: This is a good way for small databases to restore a new database with backup and synchronize the table data in the new database. You can perform full recovery or point-in-time recovery. However, large databases consume a lot of time and disk space. This method is only used to restore data. When restoring data (that is, synchronizing data), you must consider triggers, foreign keys, and other factors.

Solution 2: Use STOPAT to restore logs:

You may want to recover the latest database backup and roll back to a certain point in time, that is, a time before an accident occurs. In this case, you can use the STOPAT clause, provided that the log recovery mode is complete or large-capacity. The following is an example:

From database backup with file = 3, NORECOVERY; from database backup with file = 4, NORECOVERY, STOPAT = 'oct 22,201 2 AM '; restore database to be restored with recovery;

Note: The main disadvantage of this method is that it overwrites all data modified after the specified time point of stopat. Therefore, we need to measure the gains and losses.

Solution 3: Create a database snapshot. In the event of an accident, you can directly obtain the original data from the snapshot. However, the snapshot must be created before an accident occurs. This is useful when the core table is not updated frequently, especially when regular updates exist. However, this method is not recommended when tables are updated frequently or occasionally, or when many users access the table. When you need this method, remember to create a snapshot before each update.

Solution 4: View:

You can create a new database and move TEST_TABLE to the database. When you need to recover, you only need to restore this very small database. When accessing the data of the source database, the simplest way is to create a view and select all the data in all columns in the TEST_TABLE table. However, before creating a view, you must rename or delete the table in the source database:

USE the database to be restored; gocreate view TEST_TABLEAS SELECT * FROM backup database. Architecture name. TEST_TABLE; GO

Using this method, you can use the SELECT/INSERT/UPDATE/DELETE Statement on the view, just as if you were operating on the object table directly. When TEST_TABLE is changed, use the SP_REFRESHVIEW stored procedure to update metadata.

Solution 5: Create a Synonym (Synonym ):

Similar to solution 4, move the table to another database and create a synonym for the table in the source database:

USE the database to be restored; gocreate synonym TEST_TABLEFOR the new database. Architecture name. TEST_TABLE; GO

This method does not need to worry about the structure changes caused by metadata updates. However, the problem with this method is that it cannot reference synonyms in DDL statements or be found on the linked server.

Solution 6: Use BCP to save data: You can create a job and use BCP to regularly export data. However, the disadvantage of this method is similar to solution 1. You need to find and import the file of the specified day, and consider the trigger and foreign key issues.

Comparison of various methods:

Method	Advantages	Disadvantages
Restore database	Fast and easy	Applicable to small databases, and pay attention to triggers and Foreign keys.
Restore logs	Time point can be specified	New data after all time points will be overwritten
Database Snapshot	Useful when the table is not updated frequently	When tables are concurrently updated, snapshots are prone to problems.
View	Separate Table data from databases without data loss	Metadata needs to be updated periodically, and new databases must be maintained on a regular basis.
Synonym	Separate Table data from databases without data loss	Cannot be used on linked servers, and new databases must be regularly maintained
BCP	Dedicated backup with tables	Extra space required, trigger and foreign key issues

Conclusion: Good Programming habits and good backup mechanisms are fundamental to solving the problem. The above measures are just a solid solution. Some people may say that the new SQL Server version is partially restored? Let's take a look at the description of online books:

It can be seen that it is difficult to restore a table using other methods, but you can try it when the database is small.