Misunderstanding #30: 30 misunderstandings about backup
All are wrong
If you do not know the basics of backup before you start the misunderstanding of backup, please refer to my previous article on TechNet Magazine: Understanding SQL Server Backups.
30-01) the backup operation will cause blocking.
No, backup will not lock user objects. Although the backup burden on the IO system seems to be blocked, it will not. The only special case is that when the data zone involved in the minimum log operations involved in the backup needs to be locked, this operation will block the CheckPoint, but the DML operation will never be blocked by the backup operation.
30-02) switching from full recovery mode to large-capacity transaction log recovery mode will cause the log chain to break
No. switching between the two modes will not cause the log chain to break.
30-03) only full backup can start the broken log chain again
In addition to the full backup mode, you can also re-start the log chain for differential backup-in short, the log chain can be re-started as long as it is included in the differential backup. For more information, see my previous blog post: about SQL Server misunderstanding on the 30th-Day20-after the log backup chain is damaged, a complete backup is required to restart the log chain.
30-04) log backup is not allowed during full or differential backup
Error: After SQL Server 2005, logs can be backed up in full or differential backup mode. For details, see Search Engine Q & A #16: Concurrent log and full backups.
30-05) full or differential backup clears logs
No, because the log backup contains all the logs since the last log backup, this is unchangeable, even if the logs during this period are complete or backed up by differential backups. I once wrote a famous article on Twitter about this: Misconceptions around the log and log backups: how to convince yourself. In summary, logs are cleared only when backup logs are backed up in full or large-capacity transaction log recovery mode.
30-06) if the large transaction log recovery mode contains the operations with the minimum log record, the logs backed up in the next log will be reduced.
No, the "minimum record log" is called this because logs are recorded only when page allocation is involved. The log backup must contain the part that enables such operations to be rolled back, that is, all the logs and the related areas involved in the "minimum log record" operation. This ensures that the log content to be backed up in the large transaction log mode is basically the same as that in the full recovery mode.
30-07) the logs contained in the full or differential backup are only the logs generated during this operation.
Errors. Full or differential backup requires logs to restore the database to the transaction consistency state when the complete or differential backup is complete.
The following two blog posts will explain this in more detail:
- Debunking a couple of myths around full database backups
- More on how much transaction log a full backup nodes des
30-08) the backup operation will check the page checksum
Error. The CHECKSUM is checked only when the with checksum option is specified during Backup. This is also the option that should be specified for backup.
30-09) the Backup reads data through the buffer
No, the backup subsystem opens a separate channel for data files to avoid reading all the involved content into the memory and then saving it to the storage device, in this case, the backup performance will be severely degraded (because this involves replacing the virtual memory back to the disk ). If you specify with checksum during backup, a small amount of memory is involved.
30-10) the backup will perform a consistency check (that is, the same as dbcc checkdb)
No, that's nothing to say.
30-11) If the backup is successful, the restoration is successful.
Wrong. I hope you don't have such a mindset. You must regularly check the backup to ensure that the backup can be restored correctly in the event of a disaster. For details, see Importance of validating backups.
30-12) even if the image path is unavailable, the image backup can still be successful.
Error. If a path in the image fails, the backup fails. I hope this mechanism can be changed to an image backup. Even if one end of the path is unavailable, the other end can be successfully backed up. Unfortunately, this is not the case.
30-13) tail log backup can be performed at any time
Error. The tail log contains all the logs since the last log backup, but this is an emergency. If the data file is damaged, in addition, the log contains the "minimum record log" operations, because the logs need to be backed up at this time and the related areas involved in such "minimum record log. If these partitions in the data file are shrunk, tail logs cannot be backed up. Therefore, for those 24*7 production environments, do not use the large-capacity log recovery mode.
30-14) backup can replace DBCC CheckDB
Error. For details, refer to SQL Server misunderstanding on the 30th-Day27-using BACKUP WITH CHECKSUM can replace DBCC CheckDB
30-15) You can back up database snapshots.
No. I also want to back up database snapshots.
30-16) you can use a database image to replace log backup.
No, the image is available only when the database based on the database image is available. If the database itself is damaged, the image will not be spared. And the database itself, suspect, database images often also suspect.
Of course, when the page in the database is modified, it also needs to be synchronized to the image. Therefore, multiple images have a great impact on the database performance. In addition, when the database has been modified more and more, the image will continue to expand. Therefore, Images cannot replace log backup.
30-17) the log backup size is the same as the log size.
Error. The log contains the logs for rolling back the active transaction. The usage of the log space embodied 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 logs to be backed up are usually all logs generated since the last log backup. If the log is larger than all the logs since the last log backup, it indicates that there are still transactions that have not been terminated for a long time.
30-18) The database cannot be backed up.
Error. You can use the WITH CONTINUE_AFTER_ERROR option to back up the damaged database (if this option does not work, the boot page or the file header page may be damaged ), this is the only way to damage the database except for SQL server backup at the OS level.
30-19) You cannot prohibit others from performing backup log .. WITH NO_LOG and TRUNCATE_ONLY operations
The error is true in SQL Server 2005, but in SQL Server 2008, you can trace 3231 to achieve this.
30-20) log backup will clear logs under any conditions
Error. If the log backup is not performed in parallel, the log backup attempts to clear the inactive VLF. From the perspective of SQL Server, those logs that are not backed up are also required by SQL Server. Such logs cannot be cleared. Therefore, in some special cases, although logs are backed up, SQL Server still deems these logs necessary. SQL Server constantly checks these logs until it deems that these logs are no longer necessary, I have discussed this in detail in an article in TechNet: Understanding Logging and Recovery in SQL Server.
30-21) Differential backup is incremental
Error: The data backed up by differential backup is the data zone modified since the last full backup-so it is cumulative: for example, if you modify a data area multiple times during the period, the size of the differential backup will not change ). Only logs increase. Although many people think that differential backup is cumulative, it is not.
30-22) when the backup is complete, you can delete the previous backup.
No.
If you find that the full backup has been damaged during restoration, you will be helpless. If you do not have the previous full backup at this time, you should go to the recruitment website to update your resume. You need to keep a few backups according to the policy, so that you can be prepared.
30-23) backup of the Image Database
Error. The image (Mirror) can only be accessed through database snapshots. Nor can it be backed up.
30-24) You can back up a table separately.
An error occurs. If it happens that this individual TABLE is in a file group, you can back up the file group to achieve this goal, but there is no such thing as backup table.
30-25) to back up data, you must disable SQL Server.
I really don't know where this rumor comes from. (Edit: it is clear that Oracle is used, because we all know that Oracle is much better than SQL Server :-).
30-26) ongoing transactions will be included in this backup as long as they are committed before the backup is complete.
Errors: transactions committed and written to the disk are included in the backup only when the backup data read phase is complete. For details, see Search Engine Q & A #6: Using fn_dblog to tell if a transaction is contained in a backup.
30-27) shrinking the database before backup can reduce the backup size
An error occurs. Only moving pages does not change the backup size. For details, see Conference Questions Pot-Pourri #10: Shrinking the database before taking a backup. In addition, there is also a blog post: SQL Server misunderstanding 30-Day9-database file shrinking will not affect performance. In addition, I was also reminded that if the database is shrunk after the full backup, the next differential backup will become huge even if the data does not change.
30-28) recovering from backup is the best solution in the event of a disaster.
Error: backup is the best solution to disaster recovery only when zero data loss occurs. However, it is not a good solution to reduce DownTime and restore data by backup. It would be better if the service permits failover or some data losses.
30-29) No backup requiredMaster, msdb, model...And other system databases
Errors: These System databases need to be backed up. The Master database contains security information and databases on the instance. The MSDB database contains the SSIS package, proxy task, and backup history. The Model database contains the template for creating a database. Do not just back up the user database, otherwise it will be very painful to configure the instance from the beginning.
30-30) you need a good backup policy
Error
I guess you will say "what "? What you need is a good restoration plan, not a backup plan. Determine the time to restore and the time to back up based on business needs and technical restrictions. See the following two articles:
- Importance of having the right backups
- Planning a backup strategy-where to start?
Many people have implemented a backup policy, but they do not want to restore it without testing. It cannot be restored when a disaster occurs. I hope this is not the case.
