Detailed explanation of SQL Server recovering table level data

Time:2021-9-21

In recent days, the technical maintenance personnel of the company frequently asked me to restore the database, because they always lack the where condition, resulting in the irrecoverable consequences of update and delete. In addition, those databases are dozens of G. It will take at least ten minutes to recover. To this end, I found some data and work summary, and gave some methods to quickly recover the table, not the library, but remember, prevention is always better than making up for the lost sheep.

In the production environment or development environment, there are often some very important tables. These tables hold the core data. When the data of these tables is damaged, they need to be restored as soon as possible. However, databases in formal environments are often very large. Statistics show that the database restore time of 1t is close to 24 hours. Therefore, restoring a database because of a table is a great challenge not only in space but also in time. This article describes how to restore a single table without restoring the entire library.

Now suppose a table: test_ TABLE。 We need to restore this table as soon as possible and minimize the impact on other tables and users during the recovery process.

SQL Server (especially after 2008) has many backup and recovery functions: full, partial, file, differential and transaction backup. The choice of recovery mode seriously affects the backup strategy and backup type.

The following are some schemes for reference, but remember that each has its own advantages and disadvantages, which should be selected according to actual needs:

Scenario 1: restore to a different database:

For small databases, it is a good way to restore a new database with backup and synchronize the table data in the new database. You can do a full recovery or a point in time recovery. However, for large databases, it is very time-consuming and disk space consuming. This method is only used to restore data. When restoring data (that is, synchronous data), you should consider triggers, foreign keys and other factors.

Scenario 2: restore logs using stopat:

You may want to restore the most recent database backup and roll it back to a point in time, a point before the accident. You can use the stopat clause at this time, but only if it is a full or bulk log recovery mode. Here is an example:

Restore database the database to be restored 
 From database backup 
 WITH FILE=3, NORECOVERY ; 
 
Restore log database to be restored 
 From database backup 
 WITH FILE=4, NORECOVERY, STOPAT = 'Oct 22, 2012 02:00 AM' ; 
 
Restore database the database to be restored WITH RECOVERY ;

Note: the main disadvantage of this method is that it will overwrite all data modified after the time point specified by stopat. So weigh the gains and losses.

Scenario 3: database snapshot:

Create a database snapshot. When an accident occurs, the original data can be obtained directly from the snapshot. However, it must be a snapshot created before an accident occurs. This is useful when the core table is updated infrequently, especially regularly. However, when the table is updated frequently or irregularly, or many users are accessing it, this method is not desirable. When you need to use this method, remember to create a snapshot before each update.

Scenario 4: using Views:

You can create a new database and put test_ Move table to this library. When you need to restore, you only need to restore this very small database. When accessing the data of the source database, the easiest way is to create a view and select test_ All data for all columns in the table. Note that this method requires renaming or deleting the table of the source database before creating the view:

Use the database to be recovered; 
GO 
CREATE VIEW TEST_TABLE 
AS 
  SELECT * 
  From backup database. Schema name.test_ TABLE ; 
GO

Using this method, you can use the select / insert / update / delete statement on the view, just like directly operating on the entity table. When test_ When table changes, use sp_ Refresh view stored procedure to update metadata.

Scenario 5: create synonym:

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

Use the database to be recovered; 
GO 
CREATE SYNONYM TEST_TABLE 
For new database. Schema name.test_ TABLE ; 
GO


Scenario 6: save data using BCP:

You can create a job to export data regularly using BCP. However, the disadvantage of this method is similar to that of scheme 1. You need to find the file of which day and import it. At the same time, you should consider the problems of triggers and foreign keys.

Comparison of various methods: the advantage of this method is that you don’t need to worry about the untimely structural changes caused by metadata update. However, the problem with this method is that synonyms cannot be referenced in DDL statements or found in linked servers.

method advantage shortcoming
Restore database Fast and easy Applicable to small libraries, and pay attention to triggers, foreign keys, etc
Restore log Can specify a point in time New data after all time points will be overwritten
Database Snapshot Useful when tables are not updated frequently Snapshots are prone to problems when tables are updated in parallel
view Separate the data of the table from the database without data loss Metadata needs to be updated periodically and a new database should be maintained regularly
synonym Separate the data of the table from the database without data loss It cannot be used on the linked server, and the new database should be maintained regularly
BCP Private backup of owned tables Additional space is required, triggers, foreign keys, etc

Summary:

Good programming habits and good backup mechanism are the fundamental to solve the problem. The above measures are only a way to make up for the lost sheep. Some people may say that the new version of SQL server is not partially restored? Let’s take a look at the description of online books:

You can see that other methods are difficult to restore a table, but you can try when the library is small.