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

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; the transaction log cannot be backed up under the simple recovery model; and the transaction log cannot be restored if there is no consistent database backup.

During a database transaction log backup, SQL server will do the following: back up the transaction log: from the last successful transaction log backup to the tail of the current transaction log; truncate the transaction log: to the beginning of the active portion of the transaction log, discard the inactive parts.

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 below is a backup device created:

How do I set up automatic backup? It is important to note that 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 automatically clear the historical backups as follows:

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

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.