First article--first article SQL Server backup basics

Objective

• Why would you want to back up? The reason is simple-to restore/restore. Of course, if you do not back up, you can also recover the lost files through disk recovery, but SQL Server is very angry, the consequences are serious. Then you'll know why you'll have to back up once before you start reading the article. ∩__∩. This series describes all of the backup restore features available to SQL Server and uses instances to speak as much as possible.
• What is a backup? SQL Server is based on Windows, storing data as files, so a backup is a copy of a point in time for SQL Server-related files on Windows. Depending on the type of backup, there are different types and contents of replicas.
• What are the backup types? In the current version of SQL Server, the available backup types are: Full database backup, differential database backup, transaction log backup (post-log backup), file and filegroup backup, partial backup, depending on the version of SQL Server, some backup types are not supported, and depending on the recovery model, Some backup types are also not supported.

What is recovery mode?

Many people only focus on the backup, not the recovery model, in fact, all the backup should be from the recovery model as a point of entry. The recovery model is actually a database-level option that controls the behavior of the backup restore. SQL Server has only three recovery models in all current releases: The Simple Recovery Model (hereafter referred to as simple mode), the bulk-logged recovery model (hereafter referred to as the large capacity mode), and the Full recovery model (later called full mode).

This article starts from the recovery mode, reminds, most of the professional belong to will be explained, if the reader does not understand, you can continue to look down or search the Internet:

• Simple Recovery model: Some operations can be minimally logged. In this mode, log backups, point-in-time recovery, and page recovery are not supported. The file recovery feature is limited to read-only files in secondary data files.
• Bulk-Logged mode, bulk-logged Recovery model: Similar to full mode, can sometimes be understood as a transition mode with full mode in simple mode. This mode minimizes the logging of certain bulk operations and supports a backup restore strategy in a full backup, but the point-in-time recovery is not guaranteed because some operations are minimally logged.
• Full Recovery model: In this mode, the operation is fully documented, and all types of backup restore policies are supported.

By default, the new library inherits the configuration of the model library, including the recovery mode, which is full mode. Can be modified during creation or daily use, and does not require a restart of the service. The most important difference in the recovery model is the behavior of the log.

Simple mode:

is the easiest to manage in three modes, complete, differential, and file backup, but not log backups. In this mode, whenever the checkpoint process occurs, it automatically writes the inactive log in the log file (described in the log backup article) to the data file, and after writing, the space in the corresponding log file is available for the new transaction, noting that this space reuse or truncation does not automatically reduce the physical size of the log file. If you need to reduce space, you need to use command implementations such as DBCC SHRINKFILE/DATABASE. The process of making log space reuse is called truncation. In simple mode this process is called automatic truncation (auto-truncate). In this mode, the log usually does not need to be managed, but for a single large transaction, the log file may grow very quickly, in which case it is better to reduce the batch to a small batch. The primary limitation of the simple mode is that log backups cannot be made, that is, point-in-time restores are not possible. This pattern can be used in some environments where testing, development, or SLA requirements are not stringent.

Full mode:

In this mode, all database operations are fully recorded in the log, and 2008 of the operations in this mode also minimize the log. and is not automatically truncated. It supports any backup restore strategy, especially point-in-time restore, described in the log restore chapter. Even if checkpoint occurs, inactive transactions are not truncated to the data file. The only log backup that can control log files, so this mode of log backup is extremely important, on the one hand to provide point-in-time restore, on the other hand, control the log file size.

The log file is completely saved from the transaction since the last log backup. Using the Copy_only or NO_TRUNCATE option does not truncate the log.

Bulk log:

This mode is least used, and some operations are minimally logged, including:

• Importing by Using bcp
• BULK INSERT
• Insert Select *from OPENROWSET (BULK)
• SELECT INTO
• Inserting or attaching data using Writetext/updatetext
• Rebuilding indexes

In this mode, the area with the smallest log occurrence is recorded with bitmap image. If a database failure results in a data file that is not available, and the tail of the log contains a minimized log, you cannot make a tail backup of the log because this operation requires access to the modified extents of the data in the data file. This mode is suitable for bulk operations, but if the transaction contains a minimized log, a point-in-time restore is not possible and can only be restored to the previous.

Description of the recovery mode extension:

As mentioned above, the recovery model is a database-level configuration item that can be modified during and after the creation process, but for various reasons, as far as possible in the planning phase of the configuration, and explicitly specified during the creation process.

This option is primarily used to determine if the database can (or need) log backups? What transactions need to be recorded? There are other types of backup restore operations that you can do, and so on.

Simple mode:

Some operations can be minimized log, here to explain, many people think that the simple mode "do not log", in fact, this is a very serious misunderstanding, will lead to the subsequent use of many problems, regardless of any recovery model, will record the log, only the form and content of the record is different. In simple mode, the log backup option is disabled, with the effect that point-in-time restores, page restores are not supported, and file restore is limited to secondary data files in the READONLY filegroup.

Simple mode is the most manageable recovery model in which full database backups, differential database backups, and file backups can be done, but not log backups. In the log backup article will be described in detail, but here to mention, on the issue of log space reuse, regardless of any recovery model, there will be a system process in the background run--checkpoint, whenever the process starts, the database log file (usually the LDF file), The inactive transaction writes the data file and then identifies this part of the space as "reusable", this step is called log truncation, which is called automatic truncation in simple mode (auto-truncate), remember that reusable does not mean that the space is emptied, the only operation that can empty the LDF file physical size is shrinking the database/ File operations. The simple mode automatically performs this truncation operation, after truncation, the log space can be reused by the new thing, from the macroscopic monetization, is the LDF file's physical size does not increase, or increases slowly, in fact, when the use of simple mode, and the LDF appropriate case, if the LDF physical size is still growing, it may be necessary to attract attention.

Due to automatic truncation of logs, point-in-time recovery is not possible in simple mode and log backups are not possible. However, for systems with low data requirements, or for environments where SLAs (described in the Restore Basics article) do not have any special requirements, this pattern can be used to minimize the management of logs. However, it does not mean that the use of this mode, you do not have to manage the log, for some large-scale, long-running batch processing, will cause a large number of active transactions, the LDF file will continue to grow rapidly, causing some potential problems. To do this, split the batch into multiple, short transactions whenever possible.

In simple terms, the pros and cons of this model:

Advantages:

• Easy to manage, not required in most cases

Disadvantages:

• Transaction log backups cannot be made and point-in-time restores are not possible
• Increased risk of data loss

Select by: Depending on your business needs, do not use this mode for very important databases, regardless of the current database size, and refer to the content of the SLA in the restore base for details.

Full mode:

Full mode Many concepts are relative to the simple mode, in which all operations are fully logged in the transaction log file, and no automatic truncation occurs (except that the database has not done at least one full backup), the transaction log is truncated to the data file only when the transaction log backup occurs. and make the corresponding section available. This mode is capable of performing all types of backup restore options, especially for point-in-time recovery to ensure that data is nearly 0 lost. This is the recovery model used by almost all formal environments (also known as production environments).

Advantages:

• Ability to fully record database operations
• Point-in-time recovery to ensure data loss as much as 0

Disadvantages:

• Transaction log files need to be tightly managed
• Database size can become unmanageable

Bulk-Logged Mode:

This is the least of the recovery model, readers do not give the name of the flicker, see a lot of people in the bulk of the operation to switch to this mode, and then switch back after the operation, this kind of operation is actually more dangerous. It is not recommended. In addition, it supports log backups and can perform a certain amount of point-in-time recovery. In addition to the previously mentioned can minimize the log operation, its daily use and management with a complete pattern undoubtedly. It can be understood as a transition between a full pattern and a simple pattern.

Disadvantages:

If a data file suddenly becomes unavailable, and the tail of the log contains minimal log operations in the bulk-logged mode, it is not possible to make a tail backup of the log because it requires access to the area where the data modification occurred, and this zone only logs "an action has occurred" in the minimized log operation. The operation content is not fully documented. There is a risk of data loss due to the inability to perform point-in-time restores. If you do a good job management, this model basically doesn't have any value.

Backup ingredient:

Now say what a backup will contain, many people think, especially the full database backup, is to put everything back, in fact, they are confused by the name. Before introducing the backup component, the database component of SQL Server is described, the SQL Server database is a series of Windows-based files, the simplest mode contains a data file (the default suffix is called MDF) and a log file (the default suffix is called ldf), the suffix name can be changed, But there is no reason to change. The consequences are serious .... These two files are created automatically when the database is created, and in subsequent runs, multiple data files may be created (the default suffix is NDF), multiple log files (not necessary in most cases, described in the log backup article), and a number of filegroups, each containing several files.

Data files:

Data files are used to store systems and user data and objects, simply, data, tables, views, stored procedures, triggers, and so on. In addition, permission information is included. Each database must have at least one data file, default to the main data file, the primary. MDF. stored in the primary filegroup (primary filegroup), if you need new files, these files are secondary data files (although the name is secondary, but it is not secondary ...). ), the default suffix is named. Ndf.

The Master data file contains: All system objects and data, all user-defined objects and data by default. There are other secondary data file addresses.

File group:

A filegroup is a logical collection of files that can contain one or more data files that, when created by default, create a primary filegroup that holds the primary data file. This is also the default filegroup, all data will be stored here, unless otherwise specified, the default filegroup can be changed, if there are two or more filegroups, so that the data can be forced to write to other filegroups, sometimes in this way can alleviate the pressure of the disk. Additionally, the primary filegroup has the path to all other filegroups.

For multiple filegroups of the database, you can make a filegroup backup, which is very effective for the very large database (VLDB), because, according to my experience, even if a 150G library to do a full backup, it will often take about 20 minutes, if it is a 150T library, I am afraid that a few hours can not be confused, this time, Filegroup backups play an important role in controlling filegroups to a certain size, and then each backup is only for individual filegroups, which can split a continuous backup operation into many small operations. In addition, filegroups can be set to read-only (read-only), which can reduce the generation of locks and waits in a read-only operation, with some degree of performance assistance. For filegroup configuration In other chapters, this is not cumbersome.

The need to be reminded is that filegroups bring performance improvements as well as administrative complexity. So it needs to be considered carefully.

Transaction log:

This section also has a separate introduction, where only the introduction, all SQL Server databases, all recovery models, there is at least one transaction log file. Although there is a special article in the following, but here to take pains to remind, do not because of any mode, or LDF file is too large to remove the LDF to SQL Server, the most serious situation will cause your database can not be used.

Backup type:

Currently, Microsoft has released a version of SQL Server that supports the following types of backups: full database backups, differential database backups, transaction log backups (post-log backups), file and filegroup backups, partial backups, but as previously mentioned, some backup types are not supported depending on the version of SQL Server. In addition, depending on the recovery model, some backup types are not supported. The data files, filegroups, and log files make up the SQL Server database and become objects of various backup types. Here's a brief introduction to the various backup types:

Database backup: The data and objects above the main data file and the secondary data file (if any) are stored in the backup file, which is subdivided into:

• Full database backup: Backs up all data and objects for all files of a particular database, as well as a log portion that is sufficient to recover the database to a consistent state in the case of a failure.
• Differential database backup: Backs up data and objects for all data files on a specific database that have been modified since the most recent full database backup.

Transaction log Backup: Writes to the backup file the log records of the specified database that were written to the LDF file since the last log backup. File backup: The data files or filegroups of data and objects written to the backup file, can be subdivided into:

• Full file backup: Backs up all data and objects on a particular data file or filegroup.
• Differential file backup: Backs up data and objects that have been modified from a specific data file or filegroup since the last full file backup.
• Partial backup (full partial backup): Backs up all writable portions of the database except for read-only files/filegroups (unless otherwise specified).
• Differential partial backup: Backs up data and objects that have changed since the last full partial backup.

Again, these backup types are not always available, and some prerequisites, especially the recovery model, are shown in this series.

Factors to consider for backup:

There are several factors to consider when backing up, and it is not considered a simple operation, as any database management (including a professional DBA or part-time manager), backup is the first priority, so take it seriously:

• Sla
• Backup storage location
• Backup cycle and Backup type combination
• Backup file storage cycle
• Tools to perform backups
• Impact on performance

These sections will be introduced in succession.

What ' s the next?

1, prepare the environment, this series mainly uses the Windows Server R2+sql Server R2 Enterprise Edition +adventureworks R2 database and some additional databases created for demonstration purposes.

2, the following will demonstrate a full database backup, it is necessary to note that a full database backup, rather than a full backup, although this is equivalent in most cases, but the full backup actually contains a full file backup, in order to reduce the misunderstanding, here need to explain is a database backup.

First article--first article SQL Server backup basics