SQL Server database backup error and Countermeasures

If you are a DBA veteran and have read my article, if you find any mistakes, please criticize and correct them.

If you are not doing DBA for a long time and are worried about database backup, and want to find a backup solution that gives you peace of mind, this article is definitely suitable for you.

Everyone is familiar with the principles of database backup and recovery. However, how reliable is your current database backup? Can you go to bed with peace of mind? If the answer is yes, you don't have to spend more time reading the following. If you don't feel at ease, you are always worried about the day when the database fails to be repaired:

[1] I have RAID. Do I need to back up the database? Yes. With RAID, you can repair the database if some disks are damaged. In some cases, the database can continue to be used. But what if one day your colleagues accidentally deleted an important record? RAID is powerless. You need a proper backup policy to recover the deleted data by mistake. Therefore, with RAID, backup is still required.

The same applies to cluster and disk images.

[2] If you only perform full backup, it is impossible to perform this operation as long as the size and time of the full backup are limited. In addition, the database cannot be recovered to a certain time point only after full backup. Therefore, we need full backup + Log backup. For example, one full backup every day and one log backup every one hour or several minutes. Speaking of differential backup, because Microsoft's differential backup records the changes that have occurred since the last full backup, if the database changes frequently, it will not be long before, differential Backup will be close to the full backup size, so this is not suitable. Therefore, the full backup + Log backup solution is suitable for the vast majority of users.

[3] If you only back up the database locally, you cannot recover the database if the disk is damaged or the entire server hardware is damaged. Therefore, you need to transfer the backup file to another physical hardware. Most users do not need a tape drive, so do not consider it. Generally, we need another cheap server or PC to store Database backups to prevent backup loss caused by hardware damage.

[4] You can back up data locally on the database server and transmit the backup files to the backup server in some ways. Are you wearing the backup immediately after the backup is complete? In fact, you can consider using T-SQL statements to write the backup transfer script.

[5] After the backup file is transferred to the backup server, can you rest assured? No. As a DBA, you also need to check whether the backup files on the backup machine can restore the database to the latest status. If you use Log backup, will the database fail to be restored to the latest due to the loss of a log backup file? How can I check that there is a gap between log backup files?

[6] in order to restore the database to the latest version, you may execute a log backup every 10 minutes (or even 1 minute). In case the database breaks down, is it unrealistic to manually restore hundreds of log files?

[7] if your company has a lot of database servers (like my company) and limited disk space, you have to log on to the server frequently to delete old backup files, if you forget about it one day, or take the holiday, the disk space will be used up and it will be troublesome.

[8] During database backup, the integrity of the data page is not checked. If the data page is broken, the backup job will still be executed and no error will be reported, when you find a data page error, you may have deleted the early backup because of insufficient disk space, at this time, the remaining backups may contain corrupted data pages. If the damaged data page is the header of a table, you cannot recover the table any more.

[9] So you need to perform regular DBCC checks to discover the integrity of the database page as soon as possible. You cannot delete the old backup before you complete the DBCC check to prevent problems in the new backup. Therefore, deleting a backup file is troublesome.

[10] You may know that SQL Server provides a database maintenance plan. Yes, you can use it to regularly back up and perform DBCC checks, but all this is limited to local operations. To make the database reliable, you still need to transmit the local backup to the backup machine.

In summary, are your backups ready? Check? Does it take a lot of time to delete old backups, especially when network conditions are poor? If the transfer of database backup files stops at a certain time point, how long will it take you to find out? Is my colleagues in the evening shift authorized to check the database backup status?