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

Source: Internet
Author: User
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.


Use master
--Set the full mode
ALTER DATABASE BackupTest SET RECOVERY FULL
- Create a backup device (do not execute it)
Use master
Exec sp_addumpdevice 'disk', 'BackupTestDevice', 'F:\SqlService\backup\BackupTestBackup.bak'
Go
-- Do a full backup to the backup device (backup baseline)
Backup database BackupTest to BackupTestDevice

--Add new data
Insert BackupTest.dbo.Employees values('Hunan Changsha')
Insert BackupTest.dbo.Employees values('Hunan Xiangtan')
--Log backup
Backup log BackupTest to BackupTestDevice 


The backup set looks like this:





-- Misoperation occurs, forget to add where condition, operation time is: 2018-8-12 10:55
Delete from BackupTest.dbo.Employees 
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 Backup tail log
Use master
Go
Backup log BackupTest to BackupTestDevice with norecover
Go
--Step 2 Restore a full backup from backup, norecovery (recovering...) is not readable or writable. File refers to the backup set location number
Restore database BackupTest from BackupTestDevice with file=19, norecovery -- transaction does not recover

--Step 3
Restore log BackupTest from BackupTestDevice with file=20, norecovery -- transaction does not recover

--Step 4 Restore to 10:54 with stopat
Restore log BackupTest from BackupTestDevice with file=21, stopat='2018/8/12 10:54', recovery -- transaction recovery
- The data is back
Select * from BackupTest.dbo.Employees 
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


-- Switch from full recovery mode to high capacity mode
ALTER DATABASE BackupTest SET RECOVERY bulk_logged
-- Added
Insert BackupTest.dbo.Employees values('Hunan Zhuzhou')
--Log backup
Backup log BackupTest to BackupTestDevice
-- delete
Delete from BackupTest.dbo.Employees 


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


Restore database BackupTest from BackupTestDevice with file=19, norecovery -- transaction does not recover
Restore log BackupTest from BackupTestDevice with file=20, norecovery -- transaction does not recover
Restore log BackupTest from BackupTestDevice with file=21, norecovery -- transaction does not recover
Restore log BackupTest from BackupTestDevice with file=22, 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 the full mode
ALTER DATABASE TestFULLToBulk SET RECOVERY FULL
-- Do a full backup to the backup device (backup baseline)
Backup database TestFULLToBulk to BackupTestDevice
Insert TestFULLToBulk.dbo.product values('Hunan Zhuzhou')
--Log backup
Backup log TestFULLToBulk to BackupTestDevice
--Set large capacity
ALTER DATABASE TestFULLToBulk SET RECOVERY bulk_logged

Insert TestFULLToBulk.dbo.product values('Hunan Xiangtan')
--Log backup
Backup log TestFULLToBulk to BackupTestDevice 


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


backup log TestFULLToBulk to BackupTestDevice with norecovery 
go
restore database TestFULLToBulk from BackupTestDevice with file=26, norecovery 
go
restore log TestFULLToBulk from BackupTestDevice  with file=27,  norecovery 
go
restore log TestFULLToBulk from BackupTestDevice  with file=28,  recovery 


The above restore success proves that the full recovery mode is switched to the large capacity mode and the log chain is not interrupted.





Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.