Myth #30:30 Misconceptions about Backup
It's all wrong.
Before starting a misunderstanding about backup, if you don't know 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, see my previous blog post: SQL Server misunderstanding 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 log backup, for details, see: Search Engine q&a #16: Concurrent log and full backups。
30-05) Full or differential backups will clear the log
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, see SQL Server misunderstanding 30th talk-day27-use Backup with checksum to replace DBCC CheckDB
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.
30-23) can back up the mirror database
Error, mirroring (Mirror) can only be accessed through database snapshots. It cannot be backed up either.
30-24 You can back up a separate table
Error, if coincidentally this individual table is on a filegroup, then you can do this by backing up filegroups, but there is no such thing as backup table.
30-25) Backup data needs to shut down SQL Server
This, I really don't know where this rumor came from. (Edit: Obviously from Oracle, because we all know that Oracle is a lot more:-) than SQL Server.)
30-26 The transaction being executed will be included in this backup as long as it is committed before the backup is complete
Error, only transactions that are committed and written to disk before the data read phase of the backup are included in the backup. For more information, please see: Search Engine q&a #6: Using fn_dblog to tell if a transaction was contained in a backup。
30-27 shrinking the database before backup can reduce the size of the backup
Error, shrinking is simply a move page and does not cause a change in the size of the backup. For more information, please see: Conference Questions Pot-pourri #10: Shrinking the database before taking a backup。 In addition, there is a blog post: SQL Server misunderstanding 30th about-day9-database file shrinkage does not affect performance。 Moreover, I was reminded that if the database was shrunk after a full backup, the next differential backup would be huge even if the data did not change.
30-28 recovery from backup is the best way to do it when a disaster occurs
Error, Backup is the best way to recover from a disaster only if 0 data is lost. But reducing downtime by backup is not a good idea, and if business allows, failover or allow some data loss would be better.
30-29 do not need to backup master, msdb, model ... And so on several system databases
Error, these system databases need to be backed up. The master database contains security information and which databases exist on the instance. The msdb database contains SSIS packages, agent tasks, and backup history. The model database contains templates for creating a new database. Don't just back up the user database, otherwise it would be painful to configure the instance from scratch.
30-30 You need a good backup strategy
Error
I guess you're going to say "what"? What you need is a good restore plan, not a backup plan. Depending on the business requirements and technical constraints to determine what time to restore what, and then based on the restore to determine what time to back up what. See the following two articles:
- Importance of the right backups
- Planning a backup strategy-where to start?
A lot of people do a backup strategy, but do not test and do not want to how to restore. When a disaster occurs that cannot be restored, I hope you are not.
