SQL Server Backup and Recovery series backup and restore in four capacity modes

I. Overview

In the first section of the SQL Server backup and recovery series, there is knowledge about backup and restore in bulk mode. This article focuses on the most common backup and restore modes in bulk mode "full backup + differential backup + log backup". In the bulk recovery model, it is important to note in what circumstances the risk of loss of data restore, with this problem, to demonstrate. The backup policy is as follows:

Two. Backup

I have a testbulklogged library with a new product empty table in the library. The backup SQL statement looks like this:

 UseMaster--set up large capacity modeALTER DATABASETestbulkloggedSETRECOVERY bulk_logged--make a full backup to the backup device (backup baseline)Backup DatabaseTestbulklogged toBackuptestdevice--NewInsert  intoTestBulkLogged.dbo.product (Model,upbymemberid,brand)Values('new data for the first time',9708,'IT')--make a log backupBackup LogTestbulklogged toBackuptestdevice--BULK INSERT (5998 rows affected)Insert  intoTestBulkLogged.dbo.product (Model,upbymemberid,brand)SelectModel,upbymemberid,brand fromtest.dbo.product--do two log backupsBackup LogTestbulklogged toBackuptestdevice--new After the second log backupInsert  intoTestBulkLogged.dbo.product (Model,upbymemberid,brand)Values('new data for the second time',9708,'IT')--make a differential backupBackup DatabaseTestbulklogged toBackuptestdevice withdifferential--Delete all (6000 rows affected)Delete  fromTestBulkLogged.dbo.product

View the list of backup sets as shown in:

Three. Restore (1) Whether the bulk insert will be lost

By restoring to see if the bulk insert operation is missing, when backing up the tail log if an error occurs, the information is as follows: " exclusive access to the database is not available because the database is in use " need to set the library to single-user mode

 Use Master

-- restore full backup first, NORECOVERY (restoring ...) Cannot read and write. File refers to the backup set location number restoredatabasefrom withfile=10 

　　　　

When you restore in bulk mode, SQL Server detects that you have a tail-log backup and ensures that after the last log backup, the data operations are not lost after the restore. ( if the tail-log backup fails, the data is lost). the following first back up the tail log, using NORECOVERY not submit

-- tail-Log backup Backup Log  to  with

After backing up the tail log, there is a file number 14 in the backup set, which restores the full backup

-- (re) restore a full backup from backup, NORECOVERY (restoring ...) Cannot read and write. File refers to the backup set location number restoredatabasefrom withfile=ten

　　　　

-- Restore to log  file one by one Restore Database  from Backuptestdevice  withfile=one, Norecovery--  Restore to log  filerestoredatabase from Backuptestdevice with   file = Recovery,

　　　　

Next, we'll look at the product table in the next library to see if the data is all restored.

-- query data for large-volume operations, whether it has been restored Select *  from TestBulkLogged.dbo.product

　　

Conclusion: We can learn that the first and second log backups are perfectly restored. The BULK insert operation has also been restored. It is proved that in large-capacity mode, the data of large-volume operation can be lost, but not necessarily lost .

Four. Restore (2) interrupt log chain

As mentioned in the previous transaction log, the transaction log chain LSN must be restored in the order in which the transaction chain is to be maintained. The following demonstrates skipping the log chain file id:11, directly restoring the log chain file id:12.

--tail-Log backupBackup LogTestbulklogged toBackuptestdevice withNorecovery--Restore a full backup from backup, NORECOVERY (restoring ...) Cannot read and write. File refers to the backup set location numberRestore DatabaseTestbulklogged fromBackuptestdevice with file=Ten, Norecovery--Skip log file 11, revert to log fileRestore DatabaseTestbulklogged fromBackuptestdevice with file= A, recovery

　　

Conclusion: If only (full and transaction log backups) are restored, the transaction log must remain in the LSN order, restoring in turn, or the data will be lost if the restore fails.

Five.Restore (3) Log restore based on differential backup

　In a production environment, because log file backups are frequent, causing too many log files, it takes a lot of time and effort to restore by one of the log files. The following demo starts with a differential backup restore directly, to see if the subsequent log files can be restored successfully.

--tail-Log backupBackup LogTestbulklogged toBackuptestdevice withNorecovery--Restore a full backup from backup, NORECOVERY (restoring ...) Cannot read and write. File refers to the backup set location numberRestore DatabaseTestbulklogged fromBackuptestdevice with file=Ten, Norecovery--revert to the differential backup file 13. Skip log file 11,12Restore DatabaseTestbulklogged fromBackuptestdevice with file= -, recovery

The above restore is to skip the log file and restore directly using the differential backup file. Let's look at the data in the table below and find that the differential backup can be completely restored correctly and successfully.

　　

The following is a combination of differential backup and log backup to restore, the conclusion is that the log file does not need one to restore, you can directly navigate to a differential backup to restore, and then restore, after the log file.

--tail-Log backupBackup LogTestbulklogged toBackuptestdevice withNorecovery--Restore a full backup from backup, NORECOVERY (restoring ...) Cannot read and write. File refers to the backup set location numberRestore DatabaseTestbulklogged fromBackuptestdevice with file=Ten, Norecovery--revert to the differential backup file 13. Skip log file 11,12Restore DatabaseTestbulklogged fromBackuptestdevice with file= -, Norecovery--revert to log fileRestore DatabaseTestbulklogged fromBackuptestdevice with file= -, recovery

Conclusion: With a differential backup, you save a lot of restore time and effort when you restore. You can restore it directly from the base of the full backup by selecting the last differential backup plus subsequent log backups.

SQL Server Backup and Recovery series backup and restore in four capacity modes