SQL Server Myth: 30 Misconceptions about backups

Myth #30:30 Misconceptions about Backup

It's all wrong.

Before starting a misunderstanding about backup, if you are not aware of the basics of backup, see my previous article in TechNet Magazine: Understanding SQL Server Backups.

30-01) backup operation can cause blocking

No, the backup does not cause a lock on the user object, although the burden of the backup on the IO system causes it to appear blocked, but not in practice. The only special case is that when the backup contains the data area involved in the minimal log operation that needs to be lock, this operation blocks the checkpoint, but the DML operation is never blocked by the backup operation.

30-02 switching from the full recovery model to the bulk transaction log recovery mode and switching back will cause the log chain to break

No, switching between the two modes does not cause the log chain to break.

30-03 only a full backup can restart the broken log chain

In addition to the full backup mode, the log chain can be restarted by a differential backup-except that the log break section can restart the log chain as long as it is contained by a differential backup. For more information, please see my previous blog post: SQL Server myth 30th about-day20-Destroy the log backup chain, you need a full backup to restart the log chain.

30-04. Log backups are not allowed when full or differential backups are made

Error, after SQL Server 2005, a full or differential backup can be performed at the same time as log backups, please see: Search Engine q&a #16: Concurrent log and fully backups.

30-05) Full or differential backups will clear the log

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

No, because a log backup contains all the logs since the last log backup, this is no change, even if the logs are backed up by a full or differential backup. I once had a famous article on Twitter that explained this: misconceptions around the log and log backups:how to convince yourself. In summary, under the full or bulk transaction log recovery model, only the backup log clears the log.

30-06 If you use the bulk transaction log recovery model to include operations that have minimal log logs, the next log backup log will decrease

No, the "minimum log" is so called because only related page assignments are involved and are logged to the log. A log backup must contain a section that allows such operations to be rolled back, that is, all the logs and the related areas covered by the minimum log operation. This allows the contents of the log to be backed up in the bulk transaction log mode and the amount of content that logs need to be backed up in the full recovery model is basically the same size.

30-07 the log that is included in the full or differential backup is only the log generated by this operation

Error, full or differential backups require a log to restore the database to a transactional consistency state at the end of a full or differential backup.

The following two posts explain this in more detail:

Debunking a couple of myths around full database backups

More on how much transaction log a full backup includes

30-08 The backup operation checks the page for checksums

Error, the checksum is checked only when the WITH CHECKSUM option is specified at the time of the backup, which is also the option that should be specified by the backup.

30-09) The backup reads data through the buffer

No, the backup subsystem opens a separate channel to the data file to avoid having all the involved content read to memory before it is saved to the storage device, because if so, the performance of the backup is significantly reduced (because it involves virtual memory substitution back to disk). If you specify the with CHECKSUM at the time of backup, a small amount of memory is involved.

30-10) The backup will be checked for consistency (that is, same as DBCC CHECKDB functionality)

No, there's nothing to say.

30-11 If the backup is successful, then the restore can be successful

Wrong, I hope you do not form such a mind-set. You must regularly check your backups to make sure that you can restore them correctly in the event of a disaster. For more information, please see: Importance of validating backups.

30-12 a mirrored backup can still succeed even if the mirrored path is not available

Error, the entire mirrored backup will fail if one of the paths in the mirror fails. I hope this mechanism can be changed to a mirrored backup, even if one end of the path is not available, the other end can be successfully backed up, but unfortunately, this does not work.

30-13. End-end log backups are available at any time

Error, the end log contains all the logs since the last log backup, but this is an emergency if the data file is compromised and the log contains the operations for the minimum log, because the backup log is required and the relevant area of this "minimum log" is involved. If these extents in the data file shrink, you cannot back up the end log. So, for those 24*7 production environments, never use the bulk-logged recovery model.

30-14) Backup can replace DBCC CheckDB

Error, please see the!!!!!!!!!!!!!!!!!!!!!!!!!。

30-15) can back up the database snapshot

No, although I also want to be able to back up the database snapshots.

30-16 can use database mirroring to replace log backups

No, the mirror is available only if the database on which the database mirroring is based is available. If the database itself is corrupted, the mirror is generally not spared. But the database itself suspect, the database mirror often also can suspect.

Of course, because the pages in the database need to be synchronized to the mirror when they are modified, the presence of multiple mirrors can have a significant impact on database performance. In addition, as the parts of the database are modified more and more, the mirrors will expand. Therefore, you cannot replace log backups with mirrors.

30-17 the size of the log backup will be the same size as the log account

Error. The log contains a log that needs to roll back the active transaction. The log space used by DBCC Sqlperf (LOGSPACE) does not correctly reflect the space occupied by log entries. Search Engine q&a #25: Why isn ' t my log backup the same size as my log? In addition, the portion of the log that needs to be backed up is often all logs since the last log backup. If the log is greater than all the logs since the last log backup, there are also transactions that have long periods of inactivity.

30-18 unable to back up the corrupted database

Error, you can use the WITH CONTINUE_AFTER_ERROR option to back up the corrupted database (if this option is not available, it may be the boot page or the file header page is corrupted), which is the only way to destroy the database for SQL Server backups above the OS level.

30-19 you cannot prevent others from doing backup LOG. With NO_LOG and TRUNCATE_ONLY operations

Error, in SQL Server 2005, this is true, but in SQL Server 2008, you can do this by tracking tag 3231来.

30-20 log backups will clear the log regardless of the conditions

Error. If the log backup does not concurrently perform a database backup, the log backup attempts to clear the inactive VLF. For SQL Server's perspective, logs that are not backed up are the logs that SQL Server must have, and such logs cannot be purged. So for some special cases, although log backups are performed, SQL Server still considers these logs to be necessary, and SQL Server keeps checking the logs until it is considered that the logs are no longer required, as I have discussed in a TechNet Magazine article in detail: Understanding Logging and Recovery in SQL Server.

30-21 differential backup is an incremental

Error, the data backed up by the differential backup is the data area that has been modified since the last full backup-so it is cumulative (for example, you can change the size of a single data area over and over again, and the difference backups will not change in the same amount). Only the logs are growth-type. Although many people think that differential backups are cumulative in nature, they are not.

30-22 when the backup is complete, you can delete the previous backup

No. No. No.

If you find that a full backup is corrupted when you restore it, you should be at your wits end. If you do not have a full backup at this time, you might as well go to the recruiting website to update your resume. You need to keep a few more copies of the strategy so you can be prepared.