SQL Server database backup details and considerations _mysql

SQL Server Backup

Objective

Why do 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 missing files through disk recovery, but SQL Server is very angry, the consequences are serious. Then you will know why you have to make a backup once before you start reading the article. ∩__∩. This series will introduce all of the backup restore features available to SQL Server and speak with examples as much as possible.

What is Backup? SQL Server, based on Windows, stores data as files, so backups are a point-in-time copy of SQL Server-related files on Windows. Depending on the type of backup, the kind and content of the replica are different.

What are the backup types? The available backup types in the current version of SQL Server are full database backups, differential database backups, transaction log backups (later called log backups), file and filegroup backups, partial backups, and, 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 a recovery model?

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

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

1. Simple Recovery model: Some operations can be minimally logged. In this mode, log backups, Point-in-time restores, and page restores are not supported. The file recovery feature is limited to read-only files in secondary data files.

2. Bulk-Logged mode, bulk-logged Recovery model: Similar to the full pattern, sometimes understood as a transition mode for full mode in simple mode. This pattern minimizes some bulk operations, supports backup restore policies in full backups, but does not guarantee point-in-time recovery because some operations are minimally logged.

3. Full Recovery model: In this mode, the operations are fully documented and all types of backup restore strategies are supported.

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

Simple mode:

is the easiest to manage in three modes, complete, differential, and file backups, but not log backups. In this mode, whenever the checkpoint process occurs, automatically writes inactive logs in log files (described in log backup) to data files, and when written, the space in the corresponding log file is available for new transactions, and note that this space reuse or truncation does not automatically reduce the physical size of the log files. If you need to reduce space, you need to implement commands such as DBCC SHRINKFILE/DATABASE. The process of making log space reusable 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 best to drop the batch to a small batch. The primary limitation of simple mode is that log backups cannot be performed, which means that point-in-time restores are not possible. This pattern can be used in 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 some operations occur in this mode or minimize the log. and is not automatically truncated. It supports any backup restore strategy, especially Point-in-time restore, as described in the log restore chapter. Inactive transactions are not truncated to the data file even if checkpoint occurs. The only log backup that can control the log files, so log backups are extremely important in this mode, providing point-in-time restores and, on the other hand, controlling the log file size.

The log file is fully saved from the transaction after the last log backup. Logging is not truncated using either the copy_only or the NO_TRUNCATE option.

Bulk log:

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

• Importing by Using bcp
• Bulk
• Insertinsert
• Select *from OpenRowset (BULK)
• SELECT INTO
• Inserting or attaching data using Writetext/updatetext
• Rebuilding indexes

In this mode, the bitmap image is used to record the area where the minimum log occurs. If a database failure causes the data file to be used, and the log tail contains a minimized log, the log tail backup cannot be done because this operation requires access to the data-modified area in the data file. This pattern is appropriate for bulk operations, but if a transaction contains a minimized log, Point-in-time restores are not possible and can only be restored to the previous.

Recovery mode Extension Description:

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

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

Simple mode:

Some operations can be minimized log, here to illustrate, 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 mode, will record the log, but the form and content of records are different. In simple mode, the log backup option is disabled, which has the effect of not supporting point-in-time restores, page restores, and file restore functionality limited to secondary data files in the READONLY filegroup.

Simple mode is the easiest recovery mode to manage, in which you can perform full database backups, differential database backups, and file backups, but not log backups. In the log backup article will be detailed, but here to mention, about the log space reuse problem, regardless of any recovery mode, there will be a system process running in the background--checkpoint, whenever this process starts, the database log file (usually LDF file), The inactive transaction writes to the data file and then identifies this part of the space as "reusable", a step called log truncation, called automatic truncation in simple mode (auto-truncate), remembering that reusability does not mean that the space is emptied, and that the only operation that can empty the physical size of LDF files is to shrink the database/ File operation. This truncation is done automatically by the simple mode after truncation, the log space can be reused by new things, from macro to present, is the LDF file's physical size does not increase, or increase slowly, in fact, when using simple mode, and LDF appropriate circumstances, if LDF physical size is still growing, may need 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, this pattern can be used to minimize the management of the logs for environments where data requirements are not high, or where there is no specific requirement for SLAs (described in the article on Restore Basics). But is not meant to use this mode, there is no need to manage the log, for some large-scale, long-running batch, will cause a lot of activity transactions, at this time LDF files will continue to grow rapidly, causing some potential problems. In this respect, the batch process is split into multiple, short transactions as much as possible.

In simple terms, the advantages and disadvantages of this model are:

Advantages:

Easy to manage, not required in most cases

Disadvantages:

• Cannot perform transaction log backups, Point-in-time restore
• Increased risk of data loss

Select by: Choose from business needs, for very important databases, regardless of the current database size, do not use this pattern, detailed reference to the content of the SLA in the restore base.

Full mode:

Many concepts in the complete schema are relative to the simple schema, in this mode, 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), and the transaction log is truncated to the data file only when a transaction log backup occurs. and make the corresponding parts available. This mode is capable of performing all types of backup restore options, especially for Point-in-time restores, ensuring 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 log database operations
• Point-in-time recovery to ensure data is lost as much as 0

Disadvantages:

• Need to strictly manage transaction log files
• Database size can become unmanageable

Bulk-Logged Mode:

This is the least use of recovery mode, the reader should not be fooled by the name, see a lot of people in bulk operation switch to this mode, and then switch back after operation, this operation is actually more dangerous. Not recommended for use. In addition, it supports log backups, which enable a certain amount of point-in-time recovery. In addition to the previously mentioned minimize log operations, its day-to-day use and management and complete mode undoubtedly. Can be understood as a transition between full and simple patterns.

Disadvantages:

If the data file suddenly becomes unavailable, and the tail of the log contains a minimized log operation in bulk-logged mode, it is not possible to perform a log tail backup because this backup requires access to the area where the data modifications occur, and the zone only records "action taken" in the minimized log operation. It does not fully record the contents of the operation. There is a certain risk of data loss due to the inability to perform point-in-time restores. Good business management, in fact, this model is basically no value of existence.

Backup components:

Now to say what a backup will contain, many people think that, especially a full database backup, is to back up everything, in fact they are confused by the name. Before introducing the backup component, introduce SQL Server database components, SQL Server database is a series of windows-based files, the simplest mode contains a data file (the default suffix is MDF) and a log file (the default suffix named 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, in subsequent operations, you may create multiple data files (the default suffix is NDF), multiple log files (most of the cases are not necessary, described in a log backup article), and some filegroups, each containing several files.

Data files:

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

The master data file contains all system objects and data, objects and data that are customized by default for all users. There are also the addresses of other secondary data files.

file group:

A filegroup is a logical collection of files that can contain one or more data files, and a primary filegroup is created by default when the database is created, storing primary data files. This is also the default filegroup, all data will be stored here, unless the extra specified, the default file group can be changed, provided that 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 on the disk. In addition, the primary file group also has a path to all other filegroups.

For a database of multiple filegroups, file group backups can be done in a way that is very effective for very large databases (VLDB) because, according to my work experience, even if a 150G library does a full backup, it often takes about 20 minutes, and if it's 150T, I'm afraid it will be a few hours. Filegroup backup plays an important role in controlling the file group to a certain size, and then each backup is made to a separate filegroup, which splits a continuous backup operation into many small operations. In addition, filegroups can be set to read-only (read-only), which reduces the generation of locks and waits in a pure read operation, and helps to some extent in performance. It is not cumbersome to place filegroup configurations in other chapters.

The need to be reminded of the performance improvements that the filegroup brings, as well as the increased management complexity. So you need to think carefully.

transaction log:

This section also has a separate description, which is only an introduction, with at least one transaction log file for all SQL Server databases and all recovery modes. Although there is a special article to follow, but here to remind you, do not because of any mode, or LDF files too large to delete LDF let SQL Server, the most serious situation is that your database can not be used.

Backup type:

The following types of backups are supported in Microsoft's published SQL Server version: full database backups, differential database backups, transaction log backups (post-log backups), file and filegroup backups, partial backups, but, as mentioned earlier, some backup types are not supported, depending on the version of SQL Server. Also, depending on the recovery model, some backup types are not supported. Data files, filegroups, and log files form a SQL Server database and are objects of various backup types. Here's a brief description of the various backup types:

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

• Full database backup: Backs up all the data and objects for all files in a particular database, as well as a log portion 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 that have been modified since the most recent full database backup on a particular database. Transaction log backup: Writes a backup file to a log record of a particular database written to the LDF file since the last log backup.

file Backup: The data file or file group of data and objects written back to the file, can be subdivided into:

• Full file backup: Backs up all data and objects on a specific data file or filegroup.
• Differential file backup: Backs up data and objects that have been modified in a particular data file or filegroup since the last full file was backed up.
• Partial backup (full partial backup): Backs up all writable portions of the database except for read-only files/filegroups (unless specifically 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, some prerequisites, especially the recovery model, and this series will demonstrate these operations step-by-step.

Factors to consider for backup:

Backup needs to consider the following factors, not to consider the backup is a simple operation, as any database management (including professional DBA or part-time management), backup is the first priority, so take seriously:

• Sla
• Backup storage location
• Backup cycle and Backup type combination
• Backup file storage cycle
• Tools for performing backups
• Impact on performance

These parts will be introduced in the following succession.

What ' s the next?

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

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

Thank you for reading, I hope to help you, thank you for your support for this site!