SQL Server database backup details and precautions, SQL Server

SQL Server database backup details and precautions, SQL Server

SQL Server backup

Preface

Why is backup required? The reason is simple-for restoration/recovery. Of course, if you do not back up data, you can recover the lost files through disk recovery. However, SQL Server is very angry and has serious consequences. Then you will know why you have to back up your data before reading the article. Partition _ partition. This series will introduce all the available backup and restoration functions of SQL Server, and try to use the instance to speak.

What is backup? SQL Server stores data as files based on Windows. Therefore, backup is a copy of SQL Server related files at a certain time point on Windows. Depending on the backup type, the type and content of the copy are also different.

What are the backup types? Currently, SQL Server supports the following Backup Types: full database backup, differential database backup, transaction log backup (hereinafter referred to as log backup), file and file group backup, and partial backup, depending on the SQL Server version, some backup types are not supported. In addition, some backup types are not supported depending on the recovery mode.

What is the recovery mode?

Many people only focus on backup, but do not care about the recovery mode. In fact, all backups should be taken from the recovery mode as the starting point. The recovery mode is actually a database-level option that controls the Backup Recovery behavior. SQL Server has only three recovery modes in all released versions: simple recovery mode (simple mode) and large-capacity log recovery mode (large-capacity mode ), full recovery mode (hereinafter referred to as full mode ).

Starting from the recovery mode, this article reminds you that most of the majors will be explained one after another. If you do not understand it, you can continue to look down or search online:

1. Simple mode, Simple recovery model: some operations can be logged to the minimum. In this mode, log backup, time point recovery, and page recovery are not supported. The file recovery function is limited to read-only files in secondary data files.

2. Bulk-logged recovery model: similar to the full mode, Bulk-logged recovery model can sometimes be understood as the full mode in the simple mode of transition mode. This mode minimizes some large-capacity operations and supports the backup and restoration policies in the full backup. However, because some operations are logged at the minimum, the recovery time cannot be guaranteed.

3. Full recovery model: In this mode, all operations are fully recorded and all types of backup and restoration policies are supported.

By default, the new database inherits the configuration of the Model database, including the recovery mode, that is, the FULL mode. It can be modified during creation or daily use without restarting the service. The most important difference in the recovery mode is the log behavior.

Simple mode:

It is the easiest to manage in three modes. It can be complete, differential, and file backup, but it cannot be used for log backup. In this mode, when a Checkpoint process occurs, the logs that are not active in the log file (described in log backup) are automatically written to the data file, the space in the corresponding log file can be used by new transactions. Note that this space reuse or truncation does not automatically reduce the physical size of the log file. If you need to reduce the space, you must use commands such as dbcc shrinkfile/DATABASE. The process of reusing a log space is called truncation. In simple mode, this process is called auto-truncate ). In this mode, logs usually do not need to be managed, but for a single large transaction, log files may grow very fast. In this case, it is best to reduce the batch processing to a small batch. In simple mode, you cannot back up logs, that is, you cannot restore logs at a time point. This mode can be used in environments where testing, development, or SLA requirements are not strict.

Full mode:

In this mode, all database operations are fully recorded in the log, and 2008 shows that some operations in this mode also minimize the log. It is not automatically truncated. It supports any backup and restoration policies, especially point-in-time restoration, which is described in the log restoration chapter. Even if a Checkpoint occurs, non-active transactions are not truncated to the data file. The only log file that can be controlled is log backup. In this mode, log backup is extremely important. On the one hand, it provides time point restoration and on the other hand, it controls the log file size.

The log file is fully stored from the transaction after the last log backup. Logs are not truncated when copy_only or no_truncate is used.

Large-capacity logs:

This mode is least used, and some operations will be logged to the minimum, including:

• Use bcp for Import
• Bulk
• Insertinsert
• Select * from openrowset (bulk)
• Select
• Use writetext/updatetext to insert or attach data
• Re-Indexing

In this mode, bitmap image is used to record the least logged zone. If the data file is unavailable due to database faults and the tail of the log contains the smallest log, the tail of the log cannot be backed up, because this operation requires access to the data file's modified data zone. This mode is applicable to large-capacity operations. However, if a transaction contains a minimal log, you cannot restore the transaction at a time point. You can only restore the transaction to a previous time point.

Recovery mode extension description:

As mentioned above, the recovery mode is a configuration item at the database level, which can be modified during creation and subsequent use. However, for various reasons, we recommend that you complete the configuration during the planning phase, it is explicitly specified during the creation process.

This option is mainly used to determine whether the database can (or needs) perform log backup? What transactions need to be recorded? Whether other types of backup and restoration operations can be performed.

Simple mode:

Some operations can be minimized. here we need to explain that many people think that "No logs" in simple mode is actually a very serious misunderstanding and will cause many problems to be used in the future, logs are recorded in any recovery mode, but the format and content are different. In simple mode, the log backup option is disabled, which does not support time point restoration or page restoration. The file restoration function is limited to secondary data files in the READONLY file group.

Simple mode is the easiest mode to manage. In this mode, you can perform full database backup, differential database backup, and file backup, but log backup is not supported. The log backup article will be described in detail, but here I would like to mention that there will be a system process running -- CHECKPOINT, when this process is started, inactive transactions in the Database Log Files (usually LDF files) are written to the data file, and the space in this part is marked as "Reusable ", this step is called Log truncation. In simple mode, it is called auto-truncate. Remember that reusable does not mean that the space is cleared, the only operation that can clear the physical size of LDF files is to shrink the database/file. In simple mode, the truncation operation is automatically executed. After the truncation, the log space can be reused by new things. In macro realization, the physical size of the LDF file is not increased, or the increase is slow, in fact, when the simple mode is used and LDF is suitable, if the physical size of LDF is still increasing, you may need to pay attention to it.

Due to Automatic Log truncation, the time point cannot be restored in simple mode, and log backup cannot be performed. However, this mode can be used to minimize log management for systems with low data requirements or environments with no special requirements for SLA (introduced in the document on restoration basics. However, this does not mean that you do not need to manage logs. For batch processing that runs on a large scale and for a long time, it will cause a large number of active transactions. At this time, LDF files will continue to grow rapidly, cause some potential problems. In this case, try to split the batch processing into multiple short transactions.

Advantages and disadvantages of this mode:

Advantages:

Easy to manage, not required in most cases

Disadvantages:

• Transaction Log backup is not supported and cannot be restored at a time point
• Increased risk of data loss

Selection basis: select the mode based on business needs. This mode is not used for important databases regardless of the current database size. For details, refer to the SLA content in restoration basics.

Full mode:

Many concepts of the complete mode are relative to the simple mode. In this mode, all operations are fully recorded in the transaction log file, and automatic truncation will not occur (except that the database has never done at least one full backup). Transaction logs will be truncated to the data file only when the transaction log backup occurs, and the corresponding part is available. In this mode, all types of backup and restoration options can be executed, especially for time point recovery to ensure that data is close to zero loss. This is the recovery mode used in almost all formal environments (also called production environments.

Advantages:

• Able to fully record database operations
• Restore time points to ensure zero data loss as much as possible

Disadvantages:

• Strict management of transaction log files
• The database size may become difficult to control

Large-capacity log mode:

This is the least-used recovery mode, and the reader should not give the name fooled. Many people have seen this mode switch when performing large-capacity operations, and then switch back after the operation is complete. This operation is actually more dangerous. It is not recommended. In addition, it supports log backup to restore data at a certain time point. In addition to the previously mentioned operations that can minimize logs, there is no doubt that their daily use and management and complete mode are complete. It can be understood as the transition between the complete mode and the simple mode.

Disadvantages:

If the data file suddenly becomes unavailable and the tail of the log contains the minimal log operation in the large-capacity log mode, it is impossible to back up the tail of the log, because this backup requires access to the region where data changes occur, this region only records "operations occurred" in the minimal log operation, but does not completely record the operation content. As a result, restoration at a certain time point cannot be performed, resulting in a certain risk of data loss. If you do a good job in transaction management, this model basically has no value.

Backup ingredients:

Now let's talk about what a backup will contain. Many people think that, especially the full database backup, is to back up everything. In fact, they are confused by their names. Before introducing the backup components, we first introduce the database components of SQL Server. SQL Server database is a series of Windows-based files. The simplest mode includes a data file (default Suffix: MDF) and a log file (the default suffix is LDF). The suffix name can be changed, but there is no reason to change it. Serious consequences .... These two files are automatically created when the database is created. In subsequent operations, multiple data files may be created (the default suffix is ndf ), multiple log files (not required in most cases, as described in log backup), there are also some file groups, each of which contains several files.

Data file:

Data files are used to store system and user data and objects. In short, they are data, tables, views, stored procedures, triggers, and so on. In addition, permission information is included. Each database must have at least one data file, which is the primary data file by default. The default suffix is. MDF. Stored in the primary file group (primary Filegroup). If you need to add new files, these files are secondary data files (although the names are secondary, they are not secondary at all ...), The default suffix is. NDF.

The primary data file contains all system objects and data, and all custom objects and data by default. There are other secondary data file addresses.

File Group:

A file group is a logical set of files. It can contain one or more data files. By default, a primary file group is created when a database is created to store the primary data files. This is also the default file group, and all data will be stored here. Unless otherwise specified, the default file group can be changed on the premise that there are two or more file groups, in this way, data can be forcibly written to other file groups. Sometimes, this method can relieve the pressure on the disk. In addition, the primary file group also stores the paths of all other file groups.

For databases with multiple file groups, you can back up file groups. This method is very effective for super-large databases (VLDB), because according to my work experience, even if a GB database is used for a complete backup, it usually takes about 20 minutes. If it is a TB database, I am afraid it may not last for several hours. At this time, file Group Backup plays a very important role, controlling the file group to a certain size, and then each backup only applies to a separate file group, this allows you to split a continuous backup operation into many small operations. In addition, the file group can be set to read-only, which can reduce the number of locks and waits in the pure read operation, which helps the performance to a certain extent. The file group configuration is not cumbersome here in other sections.

It should be noted that file groups bring about performance improvements while also improving management complexity. Therefore, you need to consider it with caution.

Transaction log:

This section also has a separate introduction. Here we only make an introduction. All SQL Server databases and all recovery modes have at least one transaction log file. Although there will be a special article later, I would like to remind you that you should not delete LDF to enable SQLServer because of any Mode or because the LDF file is too large, the most serious problem is that your database cannot be used.

Backup Type:

Currently, Microsoft's version supports the following types of backup: full database backup, differential database backup, and transaction log backup (hereinafter referred to as log backup), file and file group backup, partial backup, but as mentioned above, according to different SQL Server versions, some backup types are not supported. In addition, depending on the recovery mode, some backup types are not supported either. Data files, file groups, and log files constitute the SQL Server database and become objects of various backup types. The following describes various backup types:

Database Backup: stores the data and objects in the primary and secondary data files (if any) into the backup files. This type of data is subdivided:

• Full database backup: Back up all the data and objects of all files in a specific database, as well as the logs that can be used to restore the database to a consistent State in case of a fault.
• Differential database backup: backs up the data and objects of all data files modified since the last full database backup on a specific database. Transaction Log backup: writes the log records written to the LDF file after the last log backup of a specific database to the backup file.

File backup: writes data and objects in the data file or file group to the backup file, which can be subdivided:

• Full file backup: backs up all data and objects on a specific data file or file group.
• Differential file backup: backs up the data and objects modified in a specific data file or file group after the previous full file backup.
• Partial backup (full backup): All writable parts in the backup database except read-only files/file groups (unless otherwise specified.
• Differential backup: the data and objects changed after the last complete backup.

Once again, these backup types are not always available and there are some prerequisites, especially the recovery mode. This series will gradually demonstrate these operations.

Backup considerations:

The following factors must be taken into account during backup. Backup cannot be considered as a simple operation. As a Database Management Service (including professional DBAs or part-time administrators), backup is the top priority, so take it seriously:

• SLA
• Backup storage location
• Backup cycle and Backup Type combination
• Backup File Storage Period
• Backup Tool
• Impact on Performance

These sections will be introduced later.

What's the next?

1. Prepare the environment. This series mainly uses the Windows Server 2012 R2 + SQL Server 2008 R2 Enterprise Edition + AdventureWorks 2008 R2 database and some additional databases created for demonstration.

2. The following describes how to back up a complete database. You must note that it is a full database backup instead of a full backup. Although this is equivalent in most cases, a full backup actually contains a full file backup, to reduce misunderstandings, it must be noted that database backup is used.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!