Database backup that thing.

Recently has been in the collation of the database best practices, I will also be a variety of articles suggested, synchronized to the blog Park, hoping to help more people understand the database, easy to play the database, but also to reduce the operational pressure of operators, after all, practice makes perfect, proficiency is both efficiency.

Database backup the cliché of the topic, a search database backup may be tens of thousands of articles, then why write an article? Because it's important! But often can not arouse the attention of the operation and maintenance personnel. Last week also helped a customer recover data, due to a power outage, boot server after the discovery of disk corruption, important system page damage. The use of conventional database recovery means all useless, using third-party recovery tools can only restore some of the data, simply can not meet the normal operation of the business, the data is the lifeblood of enterprises, lost, and can't find what to do? Is it necessary to undergo such a baptism to realize the importance of backup?

Database backup is a very heavy topic, too many things can not be written in the same article, and this is a large number of text literacy articles, the shortcomings of the hope that we all forgive.

Some nouns

Full database backup: A full database backup is a copy of all the information in the database, and with a single full backup, the database can be restored to a point-in-time state.

Note: Because database backup is an online operation, a large full database backup can take up to one hours or more, and the database will change during that time, so a full database backup will also have to back up some of the transaction logs so that the database can be restored to a transactionally consistent state.

File backup: A file backup refers to backing up all data in one or more files or filegroups.

Note: Under the full recovery model, a complete set of file backups and log backups covering all file backups are combined to be equivalent to a full database backup.

Using a file backup allows you to restore only corrupted files without restoring the rest of the database, which speeds up recovery. For example, if a database consists of several files located on a different disk, in which one of the disks fails, you simply restore the backup of the files on the failed disk, and the files on the other disks do not need to be restored, which shortens the restore time.
Partial backup: A partial backup is similar to a full database backup, but a partial backup defaults to only the read-write portion of the database, and the read-only file for the database will not be backed up.

Note: Because the read-only portion is not changed, it is a waste of time and effort to always back it up, so a partial backup is useful when you want to back up a read-only filegroup. A partial backup can be said to be an intermediate type between a database backup and a file backup. If there is no read-only file in a database, then there is no difference between a partial backup and a database backup.

Differential backup: A differential backup requires that a full backup be done before the database. A differential backup captures only the data that has changed since the full backup, which is referred to as the "baseline" of the differential backup. A differential backup includes only the data that was changed after the differential base was established. Differential backups are smaller and faster than differential baselines, making it easy to perform frequent backups, reducing the risk of data loss.

Log backup: Data backup focuses on backup of data files. For log files, there is a corresponding transaction log backup. Each log backup includes a portion of the transaction log that was active when the backup was created, and all the log records that were not backed up in the previous log backup. An uninterrupted sequence of log backups contains a complete (or continuous) log chain of the database. In the full recovery model (or at some point under the bulk-logged recovery model), a continuous log chain can restore the database to any point in time.

Tail-log backup: End-log backup captures any log records that have not been backed up (the "tail log") in case you lose your work and ensure that the log chain is intact. Before you can restore a SQL Server database to its most recent point in time, you must back up the transaction log for the database. The tail-log backup will be the last backup associated with the database restore plan.

Note: Not all restore scenarios require that a tail-log backup be performed. If the recovery point is included in an earlier log backup, there is no need for a tail-log backup. In addition, if you are ready to move or replace (overwrite) the database, and you do not need to restore the database to a point in time after the most recent backup, you do not need a tail-log backup.

Copy-only Backup (copy-only): A SQL Server backup that is independent of the regular SQL Server backup sequence. Typically, making a backup changes the database and affects the restore sequence of subsequent backups. However, it is sometimes useful to back up a particular purpose without affecting the entire database backup and restore process. To achieve this, SQL Server introduces the following two replication-only backups
(1) Copy only full backup
Copying only full backups also backs up the contents of the entire database. The difference between it and a normal full backup is that the baseline of the differential backup does not change after it has been done, so it does not affect the differential backup sequence.
(2) Copy-only log backups
A copy-only log backup backs up only the existing content in the current log file, but does not empty the log in the log file. As a result, the next time you do a normal log backup, the content will be backed up again, without affecting the sequence of regular log backups. This backup is primarily used when a backup schedule task is already running on the database, but now requires an urgent log backup, but it does not affect the original backup sequence.

Recovery mode: SQL Server Backup and restore operations occur in the context of the recovery model of the database. The recovery model is designed to control transaction log maintenance. Recovery mode is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backups, and what types of restore operations can be used. There are three recovery modes: The simple recovery model, the full recovery model, and the bulk-logged recovery model. Typically, the database uses the full recovery model or the simple recovery model. The database can be switched to other recovery models at any time.

Recovery Mode	Description	risk of job loss	can I recover to the point of time?
simple	no log backup. automatically reclaims log space to reduce space requirements, and it is virtually no longer necessary to manage transaction log space. For more information about database backups under the simple recovery model, see full database backup (SQL Server). The Simple recovery model does not support operations that require transaction log backups. The following features are not available in the simple Recovery model: -Log shipping -alwayson or Database mirroring -recovery of media without data loss -point-in-time restore	The changes after the latest backup are not protected. In the event of a disaster, these changes must be re-made.	can only revert to the end of the backup. For more information, see full database Restore (Simple recovery model).   For more in-depth instructions on the simple recovery model, see the  sql Server Simple recovery model provided by  MSSQLTips!  personnel.
Full	A log backup is required. Loss or corruption of data files does not cause loss of work. Can be restored to any point in time (for example, before an application or user error). For information about database backups in the full recovery model, see full database backup (SQL Server) and full database restore (Full recovery model).	Under normal circumstances. If the tail of the log is damaged, you must redo the changes made since the latest log backup.	If the backup completes at a specific point in time, you can revert to that point. For information about restoring to a point of failure using log backups, see Restore a SQL Server database to a point in time (Full recovery model). Note: If you have two or more full recovery model databases that must be logically consistent, it is a good practice to perform special steps to ensure the recoverability of these databases. For more information, see Recovery of related databases that contain marked transactions.
Large-capacity logs	A log backup is required. is an additional mode of the full recovery model that allows high-performance bulk copy operations to be performed. Reduce the amount of log space usage by logging most bulk operations in a minimal way. For information about operations that minimize log volume, see Transaction Logs (SQL Server). For information about database backups under the bulk-logged recovery model, see full database backup (SQL Server) and full database restore (Full recovery model).	If a log corruption or bulk-logged operation occurs after the most recent log backup, you must redo the changes that were made since the last backup. Otherwise, do not lose any work.	Can revert to the end of any backup. Point-in-time recovery is not supported.

General recommended production systems do not use the simple recovery model

Note: The simple recovery model is not suitable for production systems. Because it is unacceptable for production systems to lose the latest changes, we recommend using the full recovery model.

Basic tip: In simple mode, there are two ways to backup: Full backup and differential backup. Both of these backups are expensive, so they are not a type that can be backed up frequently, so there is a risk of loss of data over the period of two backup intervals. Microsoft Official Documentation: Backup Under the simple recovery model

Actual scenario Story: Many maintainers prefer simple mode because the simple mode automatically reclaims log space to reduce space requirements, and in fact no longer needs to manage transaction log space. But is it true that you understand the secret principle of the situation? Not, and even on the contrary, I see running terabytes of data in many customer systems, and the database backup model is a simple mode, with only daily full backups and no differential backups.

I usually ask: "Now the backup mode may lose one day of data, the company can accept it?" ”

Maintenance staff: "That certainly can't accept!" ”

And I asked, "Why not use a better backup approach?" ”

Maintenance personnel: "I also do not understand, do not know how to do, the database ran so long, not so easy bad?" ”

　　

Use the full recovery model to have a log backup plan

Recommendation Note: The full recovery model uses log backups to prevent data loss at the maximum extent of failure, which requires backing up and restoring the transaction log ("Log backup"). The advantage of using log backups is that it allows you to restore the database to any point in time that is included in the log backup ("Point-in-time recovery"). A series of log backups can be used to roll forward a database to any point contained in one of the log backups.

Basic tip: In full mode, you can use frequent backups of logs to reduce the time of data loss, such as: 00:00 point made full backup, every 10 minutes log backup, then when the 23:50 database corruption, only need to use 0 points of full backup and corruption before the log backup can be restored to 23:50 of the data, Instead of losing the entire data for nearly a day. Log backups allow inactive logs to be reused, which also resolves the growing issue of log in full mode. Microsoft Official documentation: backing up in the full recovery model

The actual scene small story: Many customers ' systems adopt the full recovery model, but the lack of log backup, then what is the difference between this and the simple mode? There is a difference, instead of reducing the risk of data loss, it increases the space consumption of the log. Many times asked this question, the database log is very large, how to shrink? Many novice databases may be completely unaware of the role of log backups, and use the recovery model to be simple, then shrink! Change back to Full mode! The more funny question is that the database has a mirrored or AlwaysOn available group (must be full recovery model), the mirror is removed, and then changed to simple, after shrinking

and re-build .... Many times only need a log backup to solve the problem!

System Database Backup

SQL Server maintains a set of system-level databases, called system databases, that are critical to the operation of the server instance. You must back up multiple system databases after a large number of updates. The system databases that must be backed up include msdb, master , and model. If any databases are using replication on the server instance, you must also back up the distribution system database. By backing up these system databases, you can restore and restore the SQL Server system in the event of a system failure, such as a lost hard disk.

But often the system database is not paid attention, in the maintenance task is missing.

Using a compressed backup

Databases tend to be large, so the same backup files take up a lot of space, because often to keep a few days or even a week of data on the local disk, compressed backup can greatly reduce the use of disk space backup files. At the same time, because the file is small, the pressure of backup to generate IO will also decrease, but it will consume more CPU.

　　

Using checksums (CHECKSUM)

This option is primarily to verify if there are torn pages (or if there are data page corruptions) at the time of backup, and turn on this option to find out if there is a problem with the data at the time of backup.

　　

For more information, see: Database backup checksum option will you use it?

Verifying backup availability

Verify the backup without restoring the backup, check that the backup set is complete, and that the entire backup is readable. However, RESTORE Verifyonly does not attempt to validate the data structure in the backup volume. In Microsoft SQL Server, RESTORE verifyonly has been enhanced to perform additional checks on data to increase the likelihood of errors being detected. The goal is to be as close as possible to the actual restore operation.

The RESTORE Verifyonly performs the following checks:

• Whether the backup set is complete and whether all volumes are readable.

• Some header fields in a database page, such as the page ID (just as you would write data).

• Checksum (if available in the media).

• Whether there is enough space in the target device.

Have offsite backups

To prevent local disk damage or the entire engine room failure, this vital data must be taken offsite backup method.

Check disk space periodically

Many customers operation of the strategy is not perfect, but also lack of inspection process, many times the backup job is not timely maintenance, resulting in disk space is full, the backup job failed.

Detailed description of the backup in simple and full mode

　　Backup under the Simple recovery model

The simple recovery model is the simplest form of backup and restore. The recovery model supports both database and file backups, but does not support log backups. Transaction log data is backed up only with the associated user data. Lack of log backups simplifies the management of backup and restore. However, the database can only be restored to the end of the most recent backup.

Shows the simplest backup and restore strategy under the simple recovery model. This policy only uses a full database backup that contains all the data in the database. There are five full database backups, but only the most recent backups (backups performed at T5 Point in time) need to be restored. Restoring this backup restores the database to the T5 point in time. All subsequent updates that are represented by the T6 box are lost.

Minimize the risk of job lossUnder the simple recovery model, the risk of lost work is increased over time before the next full or differential backup is performed. Unlike a full backup, a differential backup includes only the changes that have been made since the last full backup. Therefore, we recommend that you always back up without compromising backup management to avoid losing large amounts of data.

Shows the risk of work loss for a backup plan that uses only database backups. This policy applies only to small databases that can be backed up frequently.

The backup policy displayed reduces the risk of job loss by supplementing database backups with differential database backups. After the first database backup is complete, three differential database backups are followed. The third differential backup is large enough so that the next backup is a full database backup. The database backup becomes the new differential benchmark.

Backup Under the full recovery model

The full recovery model uses log backups to prevent data loss at the maximum extent of failure, which requires backing up and restoring transaction logs ("Log Backups"). The advantage of using log backups is that it allows you to restore the database to any point in time that is included in the log backup ("Point-in-time recovery"). A series of log backups can be used to roll forward a database to any point contained in one of the log backups. Note that in order to minimize restore time, you can make a series of differential backups of the same data to complement each full backup.

Assuming that you can back up the activity log after a critical failure, you can restore the database to a point of failure where data loss did not occur. The disadvantage of using log backups is that they need to use storage space and increase restore time and complexity.

Shows the simplest backup strategy under the full recovery model. In this figure, complete database backup db_1 and two routine log backups log_1 and log_2 have been completed. At some time after the Log_2 log backup, data loss occurs in the database. Before restoring these three backups, the database administrator must back up the activity log (the tail of the log). Then restore the db_1, log_1, and log_2 without recovering the database. The database administrator then restores and restores the tail-log backup (Tail). This restores the database to the point of failure, which restores all data.

Minimize the risk of job lossAfter the first full database backup is complete and a regular log backup begins, the potential job loss risk exists only when the database is corrupted and when the most recent regular log backup is performed. Therefore, it is recommended that log backups be performed frequently to limit the risk of work loss to the extent permitted by the business requirements.

The backup policy displayed uses a differential database backup to complement the full database and log backups. Transaction log backups reduce the time of potential job loss risk, leaving the risk only after the latest log backup t14. Make a series of differential backups (three backups) to reduce the number of transaction logs that need to be restored in the event of a failure. A third differential backup is large enough to make the next backup a full database backup. The database backup becomes the new differential benchmark.

Before the first database backup in this diagram is created, there is a potential job loss risk from the database (from time t0 to time T1). After the backup is established, routine log backups reduce the risk of work loss to the loss of changes made since the most recent log backup (in this figure, the last backup time is t14). If a failure occurs after the most recent backup, the database administrator attempts to back up the tail of the log (logs that have not been backed up). If the tail-log backup succeeds, the database administrator can avoid any work loss by restoring the database to the point of failure.

More suggestions

1. Regular data backups (complete or differential backups) and log backups.
2. Use compressed backups to reduce disk space consumption and improve backup efficiency.
3. Periodically check the disk's remaining space and backup file growth to ensure there is enough room for the next backup.
4. Use checksums (CHECKSUM) to check data integrity.
5. Use the RESTORE VERIFYONLY to verify backup availability.
6. Determine the frequency of full and differential backups based on data changes.
7. Determines the frequency of log backups based on the speed of log generation.
8. Prioritize using scripts to back up the database.
9. If you are using a maintenance plan backup, make sure that you need to generate reports and records.
10. Check the log file size and the number of VLF periodically.
11. Periodically cleans up and backs up and restores records in the msdb database.
12. Under sufficient disk space conditions, a copy of the latest backup (last complete and back up file) should be kept locally.
13. Periodically replicate the database backups to other servers and periodically check for offsite backups.
14. Restore the database on the standby server to test backup availability and run DBCC CHECKDB to check data integrity.
15. Archive historical data on a regular basis and, where possible, archive historical data to a database dedicated to historical records.
16. In addition to modifying the database recovery model with special needs, you should ensure that the database is running under the full recovery model.
17. When the database is switched from the simple recovery model to the full recovery model, the broken log chain should be repaired immediately by a full or differential backup.
18. When the database is switched from the large-log recovery model to the full recovery model, log backups should be made immediately to ensure that they can later be restored at a point in time.
19. Before you do anything that might be risky, make sure that the backup is valid.
20. Maintain a list of information such as how often the database is backed up, the path, and the path of the offsite backup so that the backup can be found the first time the failure occurs.
21. About the error of Backup:SQL Server error 30th talk about-day30-30 myths about Backup

--------------Blog Address---------------------------------------------------------------------------------------

Original address: http://www.cnblogs.com/double-K/

If there is reprint please keep the original address!

-----------------------------------------------------------------------------------------------------

Summary: Backup is really important! The article tells very little, only reminds of a cause to pay attention to the purpose!

For more detailed articles on backup, see: Microsoft official documentation, Backup overview

----------------------------------------------------------------------------------------------------

Note: This article is original, welcome reprint, please in the article page obvious location give this article link!
If you think this article is not bad please click on the lower right corner of the recommendation , thank you very much!

Database backup that thing.