431. chapter2.configure database recovery model

I. There are three database recovery modes: simple, large, and complete

Ii. Configure the database recovery mode

First, it is completed through the UI. Select database -- Right-click -- select attribute -- option -- recovery mode. Select the drop-down list to complete the change

Type 2: T-SQL

Code
Use Master
Go
-- Change to recovery mode to full
Alter   Database Databasename Set Recovery Full | Silple | Bulk - Logged

 

 

Iii. recovery mode Overview

 

1Backup in simple recovery mode

Supports database backup and file backup, but does not support log backup. Because there is no log backup, the database can only be restored to the last database backup. Data updated after the last backup will be lost.

 

2Backup in full recovery mode

Use log backup to prevent data loss when a fault occurs to the maximum extent. In this mode, you need to back up and restore transaction logs. The advantage of using log backup is thatDBARestore the database to any point in time contained in the log backup. You can use a series of log backups to roll the database to any point in time contained in one of the log backups.

 

Example: There is now a complete database backup for a database Db_1 , Two routine log backups Log_1 And Log_2 . In Log_2 At a certain time point after log backup, the database issue is lost. Before restoring the three backups, DBA Activity logs (tail of logs) must be backed up ). Then restore Db_1 , Log_1 And Log_2 Instead of restoring the database.Then DBARestore and restore the end log backup. Restore the database to the fault point to restore all data [This is not clear yet ].

 

 

3Backup in large-capacity log recovery mode

A special-purpose recovery mode that only occasionally improves the performance of some large-scale large-capacity operations. The full recovery mode also applies to the large-capacity log recovery mode.

Working principle: the large-capacity log recovery mode only records the large-capacity operations at the minimum. The large-capacity log recovery mode protects large-capacity operations from media faults and provides optimal performance and occupies the minimum log space. However, the large-capacity log recovery mode increases the risk of data loss for these large-capacity replication operations, because large-capacity log operations prevent the re-capturing of changes made to each transaction one by one. If the log backup contains a large log operation, the log backup cannot be restored to the time point in the log backup, but the entire log backup can only be restored.

4. simple exercise: complete backup of the database and log files in full mode, and then restore and restore the files.

Practice
Use Master
Go
-- Change to recovery mode to full
Alter   Database Sad Set Recovery Full

-- Create trial-Database Backup
Backup   Database Sad To   Disk   =   ' C: \ sqlbackup \ sad_db.bak '  
With Format
Go
-- ################# Return MSG #################
-- Processed 192 pages for database 'sad ', file 'sad' on file 1.
-- Processed 2 pages for database 'sad ', file 'sad _ log' on file 1.
-- Backup database successfully processed 194 pages in 0.253 seconds (6.253 MB/sec ).
-- ################# Retun MSG #################

-- Create trial-log backup
Backup   Log Sad To   Disk   =   ' C: \ sqlbackup \ sad_log.bak '  
With Norecovery
Go
-- ################# Return MSG #################
-- Processed 3 pages for database 'sad ', file 'sad _ log' on file 1.
-- Backup log successfully processed 3 pages in 0.208 seconds (0.093 MB/sec ).
-- ################# Return MSG #################

-- Restore the database -------------------
Restore   Database Sad
From   Disk = ' C: \ sqlbackup \ sad_db.bak '
With   File = 1 , Norecovery
Go
-- ################# Return MSG #################
-- Processed 192 pages for database 'sad ', file 'sad' on file 1.
-- Processed 2 pages for database 'sad ', file 'sad _ log' on file 1.
-- Restore database successfully processed 194 pages in 2.856 seconds (0.553 MB/sec ).
-- ################# Return MSG #################

-- Restore the log file
Restore   Log Sad
From   Disk = ' C: \ sqlbackup \ sad_log.bak '
With   File = 1 , Norecovery
Go
-- ################# Return MSG #################
-- Processed 0 pages for database 'sad ', file 'sad' on file 1.
-- Processed 3 pages for database 'sad ', file 'sad _ log' on file 1.
-- Restore log successfully processed 3 pages in 0.038 seconds (0.512 MB/sec ).
-- ################# Return MSG #################

-- Recovery the database
Restore   Database Sad With Recovery
Go
-- ################# Return MSG #################
-- Restore database successfully processed 0 pages in 0.831 seconds (0.000 MB/sec ).
-- ################# Return MSG #################