SQL Server Backup and restore

For production data, the security of data is critical, and any loss of data can have serious consequences. Backup as a copy of the data, can effectively protect and restore data

Reasons for data loss

The main reasons for data loss include the following categories:

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

(2) Human error. For example, a user or administrator mistakenly operates

(3) the computer failed. For example, a hardware, software failure causes a system crash

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

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

Therefore, one of the primary responsibilities of the database administrator is to plan a proper backup and restore strategy to protect the database, avoid loss of data due to various failures, and restore the database as soon as the system fails

Backup Type

In SQL Server, there are three common types of backups, namely full, differential, and transaction log backups

Full backup: includes a backup of the entire database, part of the transaction log, database structure, and file structure, providing a baseline for any other backups that can only be performed after a full backup has been performed

differential Backup: A backup of all changed data after the last full backup, which identifies which part has been modified, which is backed up by the backup speed

transaction log backup: records all changes to the database, primarily backup T-SQL statements, rather than the entire database structure, file structure, or data

Recovery type

Whether it's a backup or a restore, it's done in a certain recovery model. The recovery mode is a characteristic of the database, which controls the basic behavior of database backup and restore, and the database administrator should choose the appropriate recovery model according to the actual demand and the recovery demand of the data.

Simple Recovery model: Inactive logs are deleted, transaction log backups are not supported, only for small databases and infrequently changed databases

Full Recovery model: protects against loss of data at maximum range, including database and transaction log backups, and provides full protection, and restores to any specified point in time

Bulk-Logged Recovery model: complements the full recovery model, provides good performance, and consumes less log space, but increases the risk of data loss and is suitable for large-volume operating environments for databases

Change the recovery model

Open SSMs, expand SQL Server instance, click Database, right-click the selected database, select Properties, Options, select Recovery mode in the recovery mode drop-down list, as shown in:

Backup device

In the backup, you first need to set up a file to store the backed up data, this backup file is called the backup device

Case: A company uses SQL Server2008 R2 to store data, and in order to improve the security of the data, the database needs to be backed up. Full backup every Monday 22:00, transaction log backups every 12:00

(1) in SSMs, expand Administrative, maintenance plan, right-click the Maintenance Plan Wizard (requires SQL Server Agent service to be turned on)

(2) in the Select Schedule Properties window, enter a name for the maintenance plan, and select each task to schedule separately

(3) In the Select Maintenance Task window, select the following check boxes, as shown:

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

(5) Set up a backup transaction log, similar to the previous step

(6) Click Finish

The database is backed up every day for a long time and takes up too much disk space to automatically clear the history by modifying the maintenance plan.

Database restore

Right-click the database you want to restore, select Tasks, restore, database, restore

SQL Server Backup and restore