"Go" SQL Server recovers table-level data

Original address: http://blog.csdn.net/dba_huangzj/article/details/8309503

In recent days, the company's technical maintenance staff frequently let me recover the database, because they are always missing the where condition, resulting in update, delete has unrecoverable consequences, plus those libraries are dozens of G. It takes about more than 10 minutes to recover. To this end, to find some information and summary, give a few ways to quickly restore the table, rather than the library, but remember, prevention is better than the mend.

In a production environment or development environment, there are often some very important tables. These tables hold 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 the 1T database restore time of nearly 24 hours, so because a table and restore a library, not only space, and even time is a big challenge. This article describes how to restore a single table without having to restore the entire library.

Now suppose a table: test_table. We need to restore the table as quickly 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 backup. The choice of recovery mode severely affects the backup policy and the backup type.

Here are a few options for reference, but remember that each has good or bad, and should be chosen according to actual needs:

Scenario 1: Revert to a different database:

This is a good way for small databases to restore a new library with a backup and synchronize the table data in the new library back. You can do full recovery, or point-in-time recovery. But for large databases, it is very time consuming and disk-intensive. This method is only used to restore the data, when you restore the data (that is, synchronization of 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, that is, a moment before the accident occurred. You can use the STOPAT clause at this point, but only if the full or bulk-logged recovery model is required. Here is an example:

RESTORE DATABASEdatabases that need to be recovered fromDatabase Backup with FILE=3, NORECOVERY;RESTORElog database from database backup to be restored with FILE=4, NORECOVERY, STOPAT= 'Oct, 02:00 AM' ;RESTORE DATABASEDatabases that need to be recovered withRECOVERY;

Note: The main disadvantage of this approach is to overwrite all data that has been modified since the STOPAT specified point in time. So you have to measure good and loss.

Scenario 3: Database snapshot:

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

Scenario 4: Working with Views:

You can create a new database and move the test_table into the library. When you need to recover, you just need to recover this very small database. The simplest way to access the data from the source database is to create a view that selects all the data for all the columns in the Test_table table. Note, however, that this method requires renaming or deleting the table of the source database before creating the view:

 Use databases that need to be restored; GO CREATE VIEW test_table  as    SELECT  *     from     back up the 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 manipulating the entity table directly. When test_table changes, you use the Sp_refreshview stored procedure to update the metadata.

Scenario 5: Creating Synonyms (synonym):

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

 Use databases that need to be restored; GO CREATE synonym test_table  for new database. Schema name. test_table; GO

A bit of this approach is that you don't have to worry about the changes in the metadata that are caused by the change in the structure. However, the problem with this approach is that synonyms cannot be referenced in DDL statements, or they cannot be found in a linked server.

Scenario 6: Use BCP to save data:

You can create a job that uses BCP to export data on a regular basis. But the drawbacks of this approach and programme 1 similar, you need to find the day of the file and lead in, but also to consider triggers and foreign key issues.

Comparison of the various methods:

Method	Advantages	Disadvantages
Restoring a Database	Fast and easy	Suitable for small libraries, and attention to triggers and foreign keys, etc.
Restore Log	Can specify a point in time	New data will be overwritten at all points in time
Database snapshots	Useful when a table is not updated frequently	Snapshots are prone to problems when tables are updated in parallel
View	Separate the table data from the library without data loss	Metadata needs to be updated periodically, and new databases are maintained periodically
Synonyms	Separate the table data from the library without data loss	Not available on linked servers, and to maintain new databases on a regular basis
Bcp	Private backup of the owning table	Requires additional space, triggers, foreign keys, and other issues

Summarize:

 

        good programming habits and good backup mechanism is the root of the problem, the above measures are just 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:

Go to SQL Server to recover table-level data