Shulou(Shulou.com)06/01 Report--

In life, the database administrator is most worried about database paralysis, resulting in data loss, any data loss will cause very serious consequences, so we need to back up the data in order to protect and restore the data more effectively.

Generally speaking, the loss of data mainly includes several categories:

1. Program error: abnormal termination of program or logic error

2. Human error: misoperation by administrator

3. Computer failure: system crash, hardware and software errors

4. Disk failure: disk read / write head damage, disk physical block damage

5. Disaster and theft: natural disasters and man-made theft can all cause

There are three types of SQL server backups:

1. Full backup: full backup includes the backup of the entire database, part of the transaction log, database structure and file structure. It represents the database at the time when the backup is completed. Is the basis for all backup types.

2. Differential backup: refers to the backup of all the data that has changed since the last full backup. The backup process can identify which part of the database has been modified, and only back up this part.

The advantage of differential backup is that it is fast and takes very little time to back up the database, but differential backup requires that a full backup has been performed in advance.

3. Transaction log backup: all changes to the database are recorded. When backing up the transaction log, you need to consider the following factors:

The transaction log cannot be backed up until at least one full backup has been performed

Transaction logs cannot be backed up under the simple recovery model

If there is no consistent database backup, the transaction log cannot be restored.

During a database transaction log backup, SQL server does the following:

Backup transaction log: from the last successful transaction log backup to the tail of the current transaction log

Truncate the transaction log: discard the inactive part at the beginning of the active part of the transaction log.

Transaction log backups are mainly T-SQL statements, not the entire database structure, file structure, or data.

There are also three recovery models:

1. Simple recovery model: under the simple recovery model, inactive logs will be deleted, so transaction log backup is not supported. It is precisely because transaction log backups are not supported that the database can only be restored to the point in time of the backup, and the database cannot be restored to the point of failure or a specific point in time. Therefore, the simple recovery model applies only to small databases and databases that do not change frequently.

2. Complete recovery model: for very important production databases, such as banks and telecommunications systems, it may be required to restore to a certain point in history in the event of a failure. In the event of a failure, it is necessary to ensure that the data is not lost. Ensure that the data can be restored to the state of failure. In this way, the full recovery model must be adopted.

The full recovery model provides maximum protection against data loss in the event of a failure, including database and transaction log backups, and provides comprehensive protection against media failures.

Because transaction log backups are supported, the full recovery model can restore the database to any specified point in time.

3. Bulk log recovery model:

In some situations, DBA needs to insert, update or delete a large number of data into the database, such as importing millions of pieces of data at a time. Under the full recovery model, these operations will generate a large number of log records, resulting in poor database performance. In this case, the bulk-logged recovery model can be used to improve performance.

Although the bulk-logged recovery model records other transactions in its entirety, it only minimizes bulk operations. As long as the log backup contains bulk operations, the database can only be restored to the end of the log backup, not to a point in time or to a marked transaction in the log backup.

Backup device:

When making a backup, you usually create a file to store the backup data. This backup file is called a backup device. For example, the backup in the figure is a backup device created:

Note: if you want to create an automatic backup, the SQL server agent must be enabled:

Create a backup schedule:

Right-click "maintenance Plan" and click "maintenance Plan Wizard" to make backup plans according to the wizard:

Check the work to be performed:

Define details:

Set up automatic cleanup of historical backups:

As a result of backing up the database every day, over time, the backup files take up more and more disk space. By modifying the maintenance plan, you can

For all backup settings, it is best to conduct a comprehensive check during the initial backup to ensure that the backup is foolproof.

After the backup is complete, view the backup file:

Here we delete the tables in the database to simulate the loss of database data

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.