Database Backup is easy to go to database series 11

Hard Disks are valuable and data is priceless. We have been busy working for several days to see a lot of databases, but we can't lose them. Some people say I won't lose them. If you're so absolute, I can only ask you one question. If you're wrong, do you not want to perform the Postfix operation? This is another feature of backup. We can restore our database to you based on the time point.

Hard Disks are valuable and data is priceless. We have been busy working for several days to see a lot of databases, but we can't lose them. Some people say I won't lose them. If you're so absolute, I can only ask you one question. If you're wrong, do you not want to perform the Postfix operation? This is another feature of backup. We can restore our database to you based on the time point.

Hard Disks are valuable and data is priceless.

We have been busy working for several days to see a lot of databases, but we can't lose them. Some people say I won't lose them. If you're so absolute, I can only ask you one question. If you're wrong, do you not want to perform the Postfix operation? This is another feature of backup. We can restore our database based on the time point so that it can be restored to the time you need. Don't underestimate the backup!

Therefore, after the database is created, we must rememberBackupFor futureRestore. In this way, we can ensure that we do not lose a lot of data, or even do not lose it. To do this, we need to closely utilize the information to minimize our damage.

Database Backup: Copies SQL server databases or transaction logs. database backup records the status of all data in the database to be backed up.

Database recovery: When data corruption occurs, delete the damaged database and recreate the database from the backup file.

Database Backup Type: Database Backup (full backup), differential backup, transaction log backup, file or file group backup.

Full backup: Full backup of data and database objects. Essence: First, write the transaction logs to the disk, and then create the same database and database objects based on the transaction, as well as the data replication process.

Differential backup: backs up data changes that have occurred since the last database backup. It is an incremental database backup.

Transaction Log backup: backs up the transactions that have been completed in all databases since the last backup.

File or file group backup: Back up database files or folders without backing up transactions.

Advantages and disadvantages: Full backup: it is slow and occupies a large amount of space. Complete backup.

Differential backup: the fastest, with a small footprint.

Transaction Log backup, file, or file group backup: It takes less space and takes less time than full backup.

Notes: Differential backup can be performed only after a complete backup has been performed.

Backup Policy: The unfinished database is changing at all times. How should we select the Backup Type to ensure that the backup is complete?

1. Database Backup only: the last backup time can only be recovered.

2. Back up transaction logs while backing up the database: It can be restored to the time when the transaction is successfully committed when data corruption occurs.

3. perform database backup and differential backup: Replace the differential backup with the transaction log backup, which is relatively simple and accurate without transaction logs.

Recovery Policy: Depends on the backup policy. First, recover the database backup, and then recover the differential backup and transaction log Backup Based on time.

Simple recovery: no log backup is involved. Data is lost when the last backup is restored.

Full recovery (using database backup, differential backup, and transaction log backup) can be recovered to the point where the previous failure occurred. No data loss

Large-capacity Log Recovery: Three backups are also used

Backup Device: Storage medium used to store databases, transaction logs, files, and file groups. It can be divided into physical devices and logical devices. The physical device is the actual file path name. Logical backup is the alias of physical backup (simplified ). The path where the physical device name contains files. The two names can be used for backup.

Manage backup devices: Object Explorer-server objects-backup devices, right-click to create or Delete backup devices.

Create using the system stored procedure: sp_addumpdevice; Delete sp_dropdevice

Syntax: sp_addumpdevice 'device type', 'logical device name', 'Physical device name '. Device Type: disk, pipe, and tape.

Sp_dropdevice 'logical device name' and 'physical device name '. The physical backup name must be provided during deletion so that the object can be deleted. Or add delfile after the logical name.

Database backup and recovery:Backup | restore

1, Select the data library to back up, right-click -- task -- backup|Restore. Start the operation.

2,SQLStatement backup and recovery.

Full backup|Differential backup

Backup database databasename to Backup the device (both physical names and logical names are acceptable) [with different]

The difference between the two lies in the presence or absence of with different.

Log backup

Backup log databasename to Backup the device [with no_truncate]

The content in [] indicates that the original log data is not cleared.

File and File Group Backup

Restore

1. Simple recovery

2. Transaction Log Recovery

Let's take a look at this example. The first is to restore to the default, and the second is to specify a time point for recovery.

Example