Nine situations frequently encountered during the SQL Server database backup process

Source: Internet
Author: User

First case: Do I need to back up the database if I have RAID?
Answer: 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, you still need to back up the cluster. The same applies to disk images.
Case 2: Do we need full backup + Log backup?
Answer: If you only perform full backup, it is impossible to perform this operation normally because of the size and time of the full backup. 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.
Case 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.
A: 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.
Case 4: You can back up the data locally on the database server and transfer the backup file 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.
Fifth case: After the backup file is transferred to the backup machine, can you rest assured?
Answer: 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?
Case 6: In order to restore the database to the latest version, you may execute a log backup every 10 minutes (or even 1 minute). If the database breaks down, is it unrealistic to manually restore hundreds of log files?
Case 7: if your company has many database servers (like my company) and the disk space is limited, 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.
Case 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. Therefore, 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.
The ninth case: 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.