SQL Server recovery table-level data details, SQL Server details

SQL Server recovery table-level data details, SQL Server details

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:

It 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: Database snapshots:

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; go create view TEST_TABLE as 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; go create synonym TEST_TABLE FOR the new database. Architecture name. TEST_TABLE; GO

Solution 6: Use BCP to save data:

You can create a job to regularly export data using BCP. 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: This method does not need to worry about the structure changes caused by metadata updates in a timely manner. However, the problem with this method is that it cannot reference synonyms in DDL statements or be found on the linked server.

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

Summary:

Good Programming habits and good backup mechanisms are the basis for 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.

Why can't the SQL Server database recover single table data?

For example, if you have backed up the AdventureWorks database, now you only need to restore the data in the table "events. Unfortunately, SQL Server itself does not support such restoration. You need to execute such a task from a third-party tool. All programs that provide this function are SQL Server Third-Party backup tools. They allow you to extract data from a backup file or read data from a single table. However, if you choose to recover a complete database, the data you choose to recover may be different from the data you obtain. This is because when you perform a full database backup, the backup files include data files and transaction log files. Some committed transactions have not yet written data files. In this case, transactions are only stored in transaction logs or some data is stored in data files. When you use a third-party program to recover the selected single table data, you can only obtain data from the database files in the backup file. New data in the transaction log will be ignored. Shows the backup sequence with no checkpoint during Backup. What if the transaction has not been written to the data file before the backup starts? The answer is not sure, because after the backup starts, SQL Server automatically performs the checkpoint operation. During the checkpoint period, dirty data pages have been written to the disk. When we recover data from a single table from the backup file, do we think that the data to be restored is at the beginning of the backup? This depends on the backup process. If no transactions are running during the backup process, no problem occurs. If a transaction is running during this period and the checkpoint is not triggered, it is okay. However, if a transaction is being executed during the backup, the recovered data may include the data at the start of the backup and the data is committed during the backup, if a checkpoint occurs during the backup and the data is written to the data page, the data page is not backed up when the checkpoint occurs. (I really don't know how to translate this sentence. the original Article is attached as follows: If however there were transactions have med during the backup, the data we recover may include data at the beginning of the backup, and data that were committed during the backup IF checkpoints occurred during the backup AND the data was written to pages that were not yet backed up yet at the time the checkpoint occurred .) it shows the backup sequence with checkpoint. Before you perform table-level recovery, you must first consider some things. A full backup includes all the data and all the changes in the database until the end of the backup. A separate data file cannot map the database status at the specified time. Therefore, table-level recovery includes old and dirty data.

In SQL server 2005, how does one restore data in a table?

1. If there is a backup, use the log to back up the backup first, and then restore the full backup differential backup (if any) in sequence.
2. Try to use log explorer to restore without backup
However, it is not guaranteed that all data can be restored.