SQL Server Backup and Recovery series five backup and restore in full mode

I. Overview

The simple recovery model and the bulk recovery model are described earlier, and this article continues to write backup and restore under the full recovery model. The biggest advantage in the full recovery model is that as long as the tail log can be successfully backed up, it can be restored to any point contained in the log backup ("Point-in-time recovery"). Of course compared to the first two modes it is sacrificing disk I/O performance.

Recovery mode	Backup policy	Data security	I/O performance
Simple recovery	Full backup + differential backup	Security is the worst. After the last backup, all data operations are lost.	Optimal
Large Capacity recovery	Full backup + differential backup + log backup	Compromise. There is a risk of loss in bulk operations. Tail log backup failed. All data operations are lost after the last backup	Compromise
Full recovery	Full backup + differential backup + log backup	Compared to the above two kinds of safest. Tail log backup failed. All data operations are lost after the last backup	Worst

Under the full recovery model, the most common backup strategy is as shown in:

Two. Backup

In the previous chapter, we talked about backups under the bulk recovery model. The backup strategy is the same as the bulk mode, which is also a full backup + differential backup + log backup. Here to highlight the point is: When the error occurred, how to restore to a minute before the misoperation, to find out the data before the error operation.
In the "SQL Server log file structure and mis-operation data back" in the introduction of misoperation data back, but based on third-party tools ApexSQL Log. Although the tool is convenient, but to charge yo.

I have a backuptest library with a Employees table in the library.

 UseMaster--Set Full modeALTER DATABASEBackuptestSETRECOVERY Full  --Create a backup device (do not do so) UseMasterexecSp_addumpdevice'Disk','Backuptestdevice','F:\SqlService\backup\BackupTestBackup.bak'Go--make a full backup to the backup device (backup baseline)Backup DatabaseBackuptest toBackuptestdevice--New DataInsertBackupTest.dbo.EmployeesValues('Changsha, Hunan')InsertBackupTest.dbo.EmployeesValues('Xiangtan, Hunan')--Log backupBackup LogBackuptest toBackuptestdevice

The backup set looks like this:

-- error operation occurs, forget to add where condition, Operation time is: 2018-8-12  10:55 Delete  from

Three. Restore (1)

When an error occurs, you need to find an administrator to restore the data. If the database is too large, the restore takes a long time (note using a copy, do not use a production library). In this case, you need to wait. Avoid the method: (1) is to do SQL audit, not directly in Managemnet studio, to avoid such things happen. (2) is the use of smaller granularity of backup, but the corresponding complexity.

-- Step 1 Back up the tail log  Use Master Go Backup Log  to  with

Go--Step 2 Restore a full backup from backup, NORECOVERY (restoring ...) Cannot read and write. File refers to the backup set location numberRestore DatabaseBackuptest fromBackuptestdevice with file= +, NORECOVERY--transaction does not recover--Step 3Restore LogBackuptest fromBackuptestdevice with file= -, NORECOVERY--transaction does not recover--Step 4 Restore to 10:54 with StopatRestore LogBackuptest fromBackuptestdevice with file= +, Stopat='2018/8/12 10:54', recovery--Transaction Recovery

-- the data's back. Select *  from  

　　

Four. Restore (2)

In the previous introduction, it has been said that the full recovery model switches to large capacity mode and the log chain is not interrupted. below to verify

--switching from the full recovery model to large capacity modeALTER DATABASEBackuptestSETRECOVERY bulk_logged--NewInsertBackupTest.dbo.EmployeesValues('Zhuzhou, Hunan')--Log backupBackup LogBackuptest toBackuptestdevice--DeleteDelete  fromBackupTest.dbo.Employees

-- Tail Log Backup Log  to  with

The backup set is as follows, the log file id:22 is backed up in bulk mode, and 23 is the tail log

Restore DatabaseBackuptest fromBackuptestdevice with file= +, NORECOVERY--transaction does not recoverRestore LogBackuptest fromBackuptestdevice with file= -, NORECOVERY--transaction does not recoverRestore LogBackuptest fromBackuptestdevice with file= +, NORECOVERY--transaction does not recoverRestore LogBackuptest fromBackuptestdevice with file= A, recovery

When the log reverts to file id:22, the error is as shown

Skipping file id:22, committing a transaction using 23来, will also report an error, as follows:

Restore Log  from Backuptestdevice  withfile=  Recovery

After testing, the restore fails with an error that is related to the previous restore to a specified point in time.

Here's a new library test Testfulltobulk

--Set Full modeALTER DATABASETestfulltobulkSETRECOVERY Full  --make a full backup to the backup device (backup baseline)Backup DatabaseTestfulltobulk toBackuptestdeviceInsertTestFULLToBulk.dbo.productValues('Zhuzhou, Hunan')--Log backupBackup LogTestfulltobulk toBackuptestdevice--set Large capacityALTER DATABASETestfulltobulkSETRECOVERY bulk_loggedInsertTestFULLToBulk.dbo.productValues('Xiangtan, Hunan')--Log backupBackup LogTestfulltobulk toBackuptestdevice

The backup set is as follows: File ID28 is a backup made under large capacity

　　

Backup LogTestfulltobulk toBackuptestdevice withNorecoveryGoRestore DatabaseTestfulltobulk fromBackuptestdevice with file= -, NorecoveryGoRestore LogTestfulltobulk fromBackuptestdevice with file= -, NorecoveryGoRestore LogTestfulltobulk fromBackuptestdevice with file= -, recovery

The above restore succeeds, it is difficult to pass the full recovery mode switch to the large capacity mode, the log chain is not interrupted.

SQL Server Backup and Recovery series five backup and restore in full mode