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

For production data, the security of data is very important, and any loss of data may have serious consequences. As a copy of the data, backup can effectively protect and restore the data.

The cause of data loss

The main reasons for data loss include the following:

(1) Program error. For example, abnormal program termination or logic error, etc.

(2) Human error. For example, misoperation by a user or administrator

(3) the computer failed. For example, hardware and software failures cause system crashes

(4) disk failed. For example, the disk physical block is damaged

(5) disaster and theft. For example, fire, earthquake

Therefore, one of the main responsibilities of the database administrator is to plan a proper backup and restore strategy to protect the database and avoid the loss of data caused by various failures, so as to restore the database as soon as possible after the system failure.

Backup Typ

In SQL Server, three common backup types are provided: full backup, differential backup, and transaction log backup.

Full backup: including backups of the entire database, partial transaction logs, database structure, and file structure, providing a baseline for any other backup, which can only be performed after a full backup has been performed

Differential backup: the backup of all the changed data after the last full backup. The backup can identify which part has been modified. Only this part is backed up. The backup speed is fast.

Transaction log backup: records all changes to the database, mainly backing up T-SQL statements rather than the entire database structure, file structure, or data

Recovery Typ

Whether it is backup or restore, it is carried out in a certain recovery model. Recovery model is a characteristic of database, which controls the basic behavior of database backup and restore. Database administrators should choose the appropriate recovery model according to the actual needs and data recovery requirements.

Simple recovery model: inactive logs will be deleted, transaction log backups are not supported, and are only suitable for small databases and databases that change infrequently

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 and recovery to any specified point in time

Bulk-logged recovery model: a supplement to the full recovery model, which provides good performance and takes up less log space, but increases the risk of data loss, so it is suitable for mass operation of databases.

Change the recovery model

Open SSMS, expand SQL Server instance, expand Database, right-click the selected database, select attributes and options, and select recovery model in the recovery model drop-down list, as shown in the following figure:

Backup equipment

When making a backup, you first need to create a file to store the backup data, which is called a backup device.

Case: a company uses SQL Server2008 R2 to store data. In order to improve the security of the data, the database needs to be backed up. Perform a full backup at 22:00 every Monday and a transaction log backup at 12:00 every day

(1) in SSMS, expand the management and maintenance plan in turn, and right-click the maintenance plan wizard (you need to open the SQL Server agent service)

(2) in the Select Plan Properties window, enter the name of the maintenance plan, and select each task to plan separately.

(3) in the Select maintenance tasks window, select the following check box, as shown in the following figure:

(4) Select the database to be backed up, select the location of the backup, select to verify the integrity of the backup, and compress the backup. Finally, set up the execution plan and make a full backup at 22:00 every Monday.

(5) set backup transaction log, which is similar to the previous step.

(6) Click finish

By backing up the database every day, for a long time, it takes up too much disk space, and the history can be cleared automatically by modifying the maintenance plan.

Database restore

Right-click the database to be restored, select task, restore, database, and restore

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.