30 days of SQL Server misunderstanding-Day30-30 misunderstandings about backup

This series of articles I have seen on PAUL's blog at sqlskill.com. Many misunderstandings are typical and representative. The original Article is from T-SQLTuesday #11: Misconceptionsabout... EVERYTHING !!, After translation and arrangement by our team, it is published on AgileSharp. Hope to help you. Misunderstanding #30: 30 errors related to backup

This series of articles I saw in the sqlskill.com's PAUL blog, many misunderstandings are more typical and representative, the original from the T-SQL Tuesday #11: Misconceptions about... EVERYTHING !!, After translation and arrangement by our team, it is published on AgileSharp. Hope to help you. Misunderstanding #30: 30 errors related to backup

This series of articles I saw in the sqlskill.com's PAUL blog, many misunderstandings are more typical and representative, the original from the T-SQL Tuesday #11: Misconceptions about... EVERYTHING !!, After translation and arrangement by our team, it is published on AgileSharp. Hope to help you.

 

Misunderstanding #30: 30 misunderstandings about backup

All are wrong

Before you start the misunderstanding about backup, if you have no knowledge about the backup Basics of the Hong Kong Server, 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-all in all, as long as the server space and log breaking are included in the differential backup, you can start the log chain again. 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 include the portion of the Hong Kong server that enables such operations to be rolled back, that is, all the logs and the related areas involved in the "minimum record log" 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:

 

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.