SQL Server restores table-level data _mssql

In recent days, the company's technical maintenance staff frequently let me restore the database, because they are always less where conditions, resulting in the update, delete the consequences of unrecoverable, plus those libraries are dozens of G. It takes more than 10 minutes to get back up and say less. To this end, find some information and work summary, give a few methods, used to quickly restore the table, not the library, but remember, it is better to prevent than to mend.

In a production environment or a development environment, there are often some very important tables. These tables contain the core data. When data corruption occurs in these tables, it needs to be restored as soon as possible. However, the formal environment of the database is often very large, statistics show that 1T database restore time is close to 24 hours, so because a table to restore a library, not only space, and even time is a big challenge. This article describes how to recover a single table without having to restore 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 features: full, partial, file, differential, and transactional backups. The choice of the recovery model severely affects the backup strategy and the type of backup.

Here are a few scenarios to consider, but remember that each has its own good and bad and should be chosen according to the actual needs:

Scenario 1: Revert to a different database:

It is a good idea for a small database to restore a new library with backup and synchronize the table data in the new library. You can do a full recovery, or a point-in-time recovery. But for large databases, it is time-consuming and disk-intensive. This method is only used to restore data, when restoring data (that is, synchronization data), you have to consider triggers, foreign keys and other factors.

Scenario 2: Use STOPAT to restore the log:

You may want to restore the most recent database backup and roll back to a point in time before an accident occurred. You can use the STOPAT clause at this point, but only if you have a full or bulk-logged recovery model. Here is an example:

The restore database needs to be restored from 
 database backup with 
 file=3, NORECOVERY; 
 
Restore log requires a restored database from 
 database backup with 
 file=4, NORECOVERY, Stopat = ' Oct, 02:00 AM '; 
 

Note: The main disadvantage of this approach is that it overrides all data modified since the STOPAT specified point in time. So measure good gains and losses.

Scenario 3: Database snapshots:

Create a database snapshot. When an accident occurs, you can get the original data directly from the snapshot. However, it must be a snapshot that was created before an accident occurred. This is useful when the core table is not updated frequently, especially if it is regularly updated. However, this method is not available when the table is updated frequently, irregularly, or when many users visit. When you want to use this method, remember to create the snapshot before each update.

Scenario 4: Using Views:

You can create a new database and move the test_table into this library. When you need to recover, you just need to restore this very small database. The easiest way to access data from the source database is to create a view that selects all the data for all the columns in the Test_table table. But note that this method needs to rename or delete the table of the source database before creating the view:

Use the database that needs to be restored; 
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, as if you were directly manipulating the entity table. When test_table changes, use the Sp_refreshview stored procedure to update the metadata.

Scenario 5: Create synonyms (synonym):

Like Scenario 4, move the table to a different database, and then create a synonym for the table in the source database:

Use the database that needs to be restored; 
Go 
CREATE synonym test_table for 
new database. Schema name. test_table; 

Scenario 6: Save data Using bcp:

You can create a job that uses BCP to periodically export data. However, the disadvantage of this approach is similar to Scenario 1, which will need to find the day of the file and guide it, taking into consideration triggers and foreign key issues.

Comparison of various methods: This approach is a bit of you do not need to worry about metadata updates caused by the structural changes are not timely. But the problem with this approach is that you cannot reference synonyms in DDL statements, or you cannot find them in a linked server.

Method	Advantages	Disadvantages
Restore Database	Fast and easy	Apply to small libraries, and note triggers and foreign keys
Restore Log	Can specify a point in time	New data will be overwritten after all points in time
Database snapshots	Useful when tables are not updated frequently	Snapshots are prone to problems when tables are updated in parallel
View	Separate table data from library, no data loss	Metadata needs to be updated periodically and new databases are maintained on a regular basis
Synonyms	Separate table data from library, no data loss	Not available on linked server and maintain new databases on a regular basis
Bcp	Owning a private backup of a table	Requires extra space, triggers, foreign keys, and more.

Summarize:

Good programming habits and good backup mechanism is the fundamental solution to the problem, the above measures are only a way to mend. Could someone say that the new version of SQL Server is not partially restored? Let's take a look at the instructions for Books Online:

As you can see, it's hard to restore a table in other ways, but when the library is small, try it.