Database recovery mode

--- Recovery content start --- the database recovery mode is the data storage method for restoring data in the database when the database is damaged. It is related to availability, performance, disk space, and other factors. Each recovery mode maintains database data and logs in different ways. The system provides three database recovery modes: 1. Full recovery mode 2. large-capacity logs

--- Recovery content start --- the database recovery mode is the data storage method for restoring data in the database when the database is damaged. It is related to availability, performance, disk space, and other factors. Each recovery mode maintains database data and logs in different ways. The system provides three database recovery modes: 1. Full recovery mode 2. large-capacity logs

--- Restore content start ---

The restoration mode of a database is the data storage mode for restoring data in the database when the database is damaged. It is related to availability, performance, disk space, and other factors. Each recovery mode maintains database data and logs in different ways. The system provides three database recovery modes:

1. Full recovery mode

2. recovery mode of large-capacity log records

3. Simple recovery mode

The full recovery mode is the highest level of database recovery mode. In the full recovery mode, all operations on the database are recorded in the transaction log of the database. Even those large-capacity database operations and index creation operations are recorded in the transaction logs of the database. When the database is damaged, you can use the transaction logs of the database to quickly restore the database.

In the full recovery mode, because transaction logs record all changes in the database, you can use transaction logs to restore the database to any point in time. However, this recovery mode consumes a lot of disk space. Unless it is a database backup policy that is very important to transaction logs, this recovery mode is generally not recommended.

Just like the full recovery mode, the recovery mode of large-capacity log records also uses database backup and log backup to restore the database. However, in a database that uses the recovery mode of large-capacity log records, the transaction log consumes much less disk space than the transaction log of the database that uses the full recovery mode. In the recovery mode of large-capacity log records, Check Index, bulk insert, BCP, Select Into, and other operations are not recorded in the transaction log.

Simple recovery mode can be used for small databases or databases with infrequently changed data. When the simple recovery mode is used, you can restore the database by performing full database backup and incremental database backup. The database can only be restored to the time point when the backup operation is performed. All data modifications made after the backup operation are lost and need to be rebuilt. This mode features no transaction logs in the database. The advantage of this model is that it consumes less disk space and the recovery mode is the simplest.

For example:

Assume that the database with the problem is A, and the database with the new recovery is B.
1. premise: the recovery mode of database A must be complete.
2. Back up the log of database A. Assume the file name is log. bak.
3. Use the latest full backup of database A and use the with norecovery method to restore to database B.
4. use the Log Recovery Method to specify the restoration time point (T) and. after the bak file is restored to database B, the content of database B is the complete data before the T time.

The following is A test statement for creating database:

-- Create database a go -- back up database a to disk = 'C: \. bak 'with format go -- CREATE test table create table. dbo. TB_test (ID int) GO -- Insert data Into. dbo. TB_test (ID) select 1GO -- delay 1 second, and then perform subsequent operations -- (this is because SQL Server has a time precision of up to 3% seconds, without delay, this operation may fail to be restored to the time point.) waitfor delay '00: 00: 01' GO -- assume that we have deleted A by mistake. dbo. TB_test this table drop table. dbo. TB_test -- to test the restoration time point, first record the operation time point SELECT dt = GETDATE () INTO # t go -- after the deletion operation, the TB_test table should not be deleted. The following example shows how to restore the table TB_test that was accidentally deleted and how to restore the data row. To avoid overwriting database A, it is generated to database B. GO -- first, back up the transaction LOG of database A (the transaction LOG can be restored TO the specified time point) backup log a to disk = 'C: \ A_log.bak 'with format go -- next, we need to restore the full backup first (the restoration log must be performed on the basis of the full backup) to generate the database brestore database B FROM DISK = 'C: \. bak 'with file = 1, MOVE n'a' TO n'c: \ B. mdf ', MOVE n' A _ log' TO n' C: \ B. ldf', NORECOVERY, replace go -- restore the transaction log before the delete operation (the time here corresponds to the previous Delete time, and earlier than the deletion time DECLARE @ dt datetime SELECT @ dt = DATEADD (MS,-20, dt) FROM # T -- get the restore log B FROM DISK = 'C: \ A_log.bak 'WITH RECOVERY, STOPAT = @ dt GO -- Query earlier than the time when the table is deleted, check whether the table resumes SELECT * from B. dbo. TB_test -- delete the test environment drop database a drop database bdrop table # T

--- Restore content end ---