SQL Server Backup and Recovery series backup and restore under three simple recovery models

Original: SQL Server Backup and Recovery series backup and restore under three simple recovery models

I. Overview

In front of some theoretical knowledge of backup, this article starts with backup and restore under the simple recovery model. In the simple mode is not a log backup, after a disaster, the database after the last backup of the data modifications will be all lost, so in the production environment, the data is very important, it is generally not recommended to use this mode. For example, a database has 5 full data backups, the time is T5, and then a disaster occurs, the department is lost.

As the database grows larger, the full backup time grows longer, and differential backups are introduced to reduce the risk of loss. For example, after the first database full backup was established, three differential backups were established, followed by a full backup to establish a new differential benchmark. Whether it's a full or differential backup, it's usually only done in the evenings. If the data is large and does not allow long-time data loss, the simple recovery model is not sufficient.

Two. Backup Demo

The primary backup under the simple recovery model is full and differential backups. I have a testlog library with two tables in the library. Suppose that a full backup was made in Sunday, and a differential backup was made every day from Monday to Saturday, and a new baseline was started by Sunday in the second week. As shown below

 Use Test exec ' Disk ' ' Backuptestdevice ','F:\SqlService\backup\BackupTestBackup.bak'

　　

--Set recovery mode to Simple recovery
ALTER DATABASE testlog SET RECOVERY Simple
Go--  do a full backup to the backup device (backup baseline) Assuming that the backupdatabase   to be in the Sunday night BackuptestdeviceGo

Go--differential backup Monday nightBackup DatabaseTestlog toBackuptestdevice withdifferentialGo--differential backup Tuesday nightBackup DatabaseTestlog toBackuptestdevice withdifferentialGo--differential backup Wednesday nightBackup DatabaseTestlog toBackuptestdevice withdifferentialGo--differential backup Thursday nightBackup DatabaseTestlog toBackuptestdevice withdifferentialGo--differential backup Friday nightBackup DatabaseTestlog toBackuptestdevice withdifferentialGo--differential backup Saturday nightBackup DatabaseTestlog toBackuptestdevice withdifferentialGo--Full backup Sunday night (new benchmark)Backup DatabaseTestlog toBackuptestdevice withdifferentialGo--differential backup Monday nightBackup DatabaseTestlog toBackuptestdevice withDifferential

Viewing backup sets in a backup device
　　

-- View by Script

SELECT DISTINCT S.first_lsn,s.last_lsn,s.database_backup_lsn,s.position,
S.backup_finish_date,s.type,y.physical_device_name,s.backup_size
From msdb: Backupset as S inner join
Msdb.. Backupfile as F on f.backup_set_id=s.backup_set_id inner join
Msdb.. Backupmediaset as M on s.media_set_id=m.media_set_id inner JOIN
Msdb.. Backupmediafamily as Y on m.media_set_id=y.media_set_id
where S.database_name= ' Testlog '
ORDER BY s.position ASC

Three. Restore Demo

To restore a database, you need to construct a correct restore sequence. During the restore process, the backup file ends with a NORECOVERY transaction that is not recovered (restoring: ) is not readable and writable, using recovery transaction Recovery at the end of the last backup file. The database is back to normal.

-- Switch to Master library  Use Master

-- set the single-user mode (otherwise, execute the following error: "No exclusive access to the database is available because the database is in use") ALTER DATABASE SET  with ROLLBACK IMMEDIATE

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

　　

-- revert to differential backup file 3, skip differential backup 2 to see if the backup was successful Restore Database  from Backuptestdevice  withfile=3, recovery

　　

-- End Single-user mode after backup ends ALTER  Database  Testlog  set   online  

Here is a demonstration to restore the diff file, using the old benchmark. What will it look like?

-- Restore a full backup from the old benchmark, norecovery (restoring ...) Cannot read and write. File is 1restoredatabasefrom withfile=1   --restoredatabase from  backuptestdevice  withfile=9, recovery

Summary: For the simple recovery model, there is no log backup, only one full database backup is required for recovery, and the last differential backup. For multiple differential backup files, the continuity of the LSN (within the same benchmark) is not required at restore time.

SQL Server Backup and Recovery series backup and restore under three simple recovery models