Database backup and Recovery scenarios

Data security

With the popularization of computer and the progress of information technology, especially the rapid development of computer network, the importance of information security becomes more and more obvious. Data backup is an important method to ensure information security.

As long as data transmission, data storage and data exchange occur, it is possible to create a failure. At this point, if you do not take data backup and data recovery means and measures, it will lead to the loss of data. Sometimes the damage is irreparable and measurable.

The form of data failure is varied. Typically, data failures can be divided into three categories: system failure, transactional failure, and media failure. From the point of view of backup and recovery scheme of information security database, in fact, the "information attack" of third party or enemy can also produce different kinds of data failure. For example: Computer virus type, Trojan horse type, "hacker" intrusion Type, logic bomb type and so on. The consequences of these failures are: data loss, data modification, increased useless data, and system paralysis. As a system administrator, to maintain the integrity and accuracy of the system and data. The usual measures are: Install a firewall, prevent "hacker" intrusion, install anti-virus software, take access control measures, choose high-reliability software products, enhance the security of computer network.

There is no foolproof information security in the world. The information world "attacks and counter-attacks" is also endless. The attack and protection of information is like the relationship between Spear and shield, and develops in a spiral way.

There are often new problems in the collection, processing, storage, transmission, and distribution of information, the most notable of which is system failure, data loss or destruction.

Threat data security, the main causes of system failure are the following aspects:

1) The hard drive is damaged;

2) human error;

3) hacker attacks;

4) virus;

5) natural disasters;

6) power surge;

7) magnetic interference;

Therefore, data backup and data recovery are the last means to protect data, and it is also the last line of defense against active information attack.

SQL Server Data Backup scenarios

The backup methods of SQL Server database mainly include full backup, differential backup, transaction log backup and so on. Based on data security requirements, the recommended backup method is a weekly full backup, a differential backup per day, and a transaction log backup every half an hour.

By default, the sysadmin fixed server role and members of the db_owner and db_backupoperator fixed database roles are granted backup database and backup LOG permissions.

The backup device's physical file ownership and permissions issues may interfere with the backup operation. SQL Server must be able to read and write to the device, and the account that runs the SQL Server service must have write permissions.

The backup file storage disk needs to be separated from the database file storage disk, avoiding disk IO conflicts. The backup execution time is staggered from the database job execution time to prevent backups from affecting the execution of the database job.

The SQL Server Maintenance Plan feature provides a better automated backup to start the SQL Server Agent feature on the database manager before using this feature. Such as:

Full backup

1) Select Management-Maintenance Plan-Maintenance Plan Wizard

2) After you open the Maintenance Plan Wizard, you can add a maintenance plan and name the maintenance plan, such as:

3) Select Backup Database (full) Maintenance task

4) Set up the database complete maintenance plan related information, including the backup database, backup set expiration time, backup file storage path

5) Select Next, then select Done

6) Specify the execution cycle for the maintenance plan

As shown, the plan type is selected for recurrence, the cycle is selected weekly, and the plan is executed every Sunday hours.

7) After the backup plan is created, you need to automatically delete the outdated backup database to ensure efficient use of disk space. Establish a purge maintenance task in the current maintenance plan to remove backup files that have a retention time exceeding the set value.

For example, open the data backup plan that you just established, drag the Purge maintenance task from the Maintenance Plan task list into the backup task box, and drag the associated arrows to establish the relationship between the backup task and the cleanup task two tasks. The association is used to specify the order in which execution is performed.

8) Set the folder in the Purge maintenance task and the file retention period

9) This is done through the Maintenance Plan Wizard, or through the new maintenance plan feature, by creating a new maintenance plan, and then dragging the Backup Database task, clear Maintenance task, and the related settings above method.

Differential backup

1) Select a maintenance plan-create a new maintenance plan

2) Set maintenance plan Name: differential backup

3) Open the differential backup schedule and drag the Backup database task with the Purge maintenance task to the current scheduled task box.

4) Set Backup Database task in the backup type to difference, and set the backup type, back up the specified database, backup storage folder

5) Double click to open the Clear maintenance task, set the folder in the Purge maintenance task and the file retention period

Transaction log Backups

1) Select a maintenance plan-create a new maintenance plan

2) Set maintenance plan Name: transaction log backup

3) Open the differential backup schedule and drag the Backup database task with the Purge maintenance task to the current scheduled task box.

4) Set backup Database task in Backup type as transaction log, set backup type, back up specified database, backup storage folder

5) Double click to open the Clear maintenance task, set the folder in the Purge maintenance task and the file retention period

Database recovery restore full backup

Restoring a full backup is the restoration of a full backup file of the database, restoring the database to a complete state.

1) Select Restore Database

2) Under General Options, set the restore target database name, source device (database complete file), and so on.

3) Set the Restore database file storage path and choose to overwrite the existing database and roll back the uncommitted transaction.

Restore full backup + differential backup

This is the way to restore the database to the state of the differential backup. After you restore the full backup, you can continue to restore the differential backup to the target database, which is used to update the data saved by the differential backup into the current database, restoring the database to the state of the differential backup.

1) Select Restore Database

2) Under General Options, set the restore target database name, source device (database complete file), and so on.

3) Set the Restore database file storage path, and choose to overwrite the existing database, do not roll back uncommitted transactions to the database.

4) When the previous step is complete, the database is in a restored state; you can continue restoring the database, and the source device chooses the differential backup file.

5) Set the file storage path when the differential backup file is restored, and the database transaction log rollback mode.

Restore full backup + differential backup + transaction log backup

This is the state at which the database is restored to the transaction log backup. After you restore the full backup, you can continue to restore the differential backup to the target database and then continue to restore the transaction log backup, which is used to update the database to the current database for differential backups, transaction log backups, and to restore the databases to the state of the transaction log backup.

1) Select Restore Database

2) Under General Options, set the restore target database name, source device (database complete file), and so on.

3) Set the Restore database file storage path, and choose to overwrite the existing database, do not roll back uncommitted transactions to the database.

4) When the previous step is complete, the database is in a restored state; you can continue restoring the database, and the source device chooses the differential backup file.

5) Set the file storage path when the differential backup file is restored, and the database transaction log rollback mode.

6) After the differential backup restore is complete, the database is in the restored state; Continue to select Restore to restore the transaction log files to the current database.

Select Restore transaction log, general selection settings

Restore point-in-time settings, by setting the point in time, you can restore the database to any point in time that is complete until the transaction log backup period.

Setting rollback uncommitted transactions in options

After execution completes, the database resumes the available state, and the data is updated to the state of the specified time. If no time is specified, the state of the transaction log backup.