Data security magic weapon-Database Backup

The data in the data database is stored on the computer. Data loss may occur if the system crashes or the hard disk is damaged. It takes a lot of time and money to recover the data, in this case, you don't have to worry about data backup.

The backup and recovery components are an important part of SQL Server. Database backup records the status of all data in the database during the backup operation.

SQL Server supports online backup. You can perform other operations while performing backup, but the following operations are not allowed:

1. Create or delete database files

2. Create an index

3. perform non-log operations

4. automatically or manually reduce the size of database or database files

 

Before performing a backup, you must specify or create a backup device.

 

Backup Device

Definition: Storage medium for database, transaction logs, or file and file group backup. It can be a hard disk, tape, or pipe. Backup devices are stored as files on disks.

Category:

Physical device: the name of the backup device.

Logical Device: the alias or public name used to identify the physical backup device. Is permanently stored in the SQL Server mainland system table. It is easier to reference a physical backup name than to reference a physical backup name.

Example:

Backup database accounting to accountin_backup (logical device)

Or

Backup database Accounting

To disk = 'C: \ Backups \ acounting \ full. back' (physical device)

 

Manage backup devices

Create a backup device:

Enterprise Manager: 1) Open Enterprise Manager and expand the specified server.

2) Expand the "manage" node and select "backup" for the node in the next level"

3) Right-click the backup node, select the "new backup device" command from the pop-up menu, and open the "backup device properties-New Device" dialog box.

4) in the backup device Properties dialog box, perform the following operations: Enter the logical File Name of the backup device in the Name field, and enter the file name and storage path of the backup file in the file name field.

5) Click OK.

T--SQL: sp_addumpdevice 'device type', 'device logical name', 'device physical name'

Example: exec sp_addumpdevice 'disk', 'backup _ company ',

'C: \ mssq17 \ backup \ bk_company.bak'

 

Delete backup:

Enterprise Manager: select the backup to be deleted.

T--SQL: sp_dropdevice 'device logical name', 'delfile' (delfile: Specifies whether to delete a physical backup device file .)

Example: Delete the backup device created above

Sp_dropdevice 'backup _ company 'or

Sp_sropdevice 'backup _ company ', 'selfile'

 

Four backup methods

Database Backup

Used for: When a database is updated slowly, the backup will create a copy of the data in the database when the backup is complete.

Comparison: Compared with transaction log backup and differential database backup, each backup in the Database Backup uses more storage space. Therefore, it takes more time for the database backup to complete the backup operation. Therefore, the frequency of Database Backup creation is usually lower than that of differential database or transaction log backup.

When the database is restored, the backup will re-create all related files in the database when the database and backup are complete. However, all database modifications made since the backup is created will be lost. To restore the transactions that occur after the database backup is created, you must use the transaction log backup or differential backup.

Differential backup or Incremental Backup

Definition: Back up data changes that have occurred since the last database backup.

Compared with full database backup, differential backup takes a short time for backup and recovery because the backup data volume is small. By increasing the number of backups for differential backup, you can reduce the risk of data loss and restore the database to the time when the last differential backup is performed, but it cannot be like a transaction log

 

Transaction Log backup

Definition: Back up transactions in the database. This includes all completed transactions after the last physical log backup, differential backup, and full database backup.

Generally, transaction logs use less resources than Database backups. Therefore, you can often create transaction log backups.

Features: it is valid only when the database backup and transaction log backup are used to restore the database from the last database backup or the continuous transaction log backup after the differential database backup. If the log backup is lost or damaged, you must create a database backup or differential database backup and start backing up the transaction log again.

Database file or File Group Backup:

Definition: a relatively complete backup and restoration process, usually used in super large databases with high availability requirements.

Features: Only corrupted files or file groups can be restored without restoring the rest of the database, thus speeding up recovery.

With data backup, we have data security. You don't have to worry about data loss. You can work and learn with peace of mind.