Shulou(Shulou.com)05/31 Report--

This article is to share with you about the solution when there is an error in SQLServer database backup. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

We are more or less familiar with the principle of backup and recovery of the database. However, when there is an error in a SQLServer database backup, most people may not know what to do. The following editor will explain what to do when there is an error in SQLServer database backup.

What to do when there is an error in SQLServer database backup

1. I have RAID. Do I still need to back up the database?

Need. With RAID, the database can be repaired in case some of the disks are damaged, and in some cases the database can even continue to be used. But if one day, your colleague accidentally deletes an important record, there is nothing RAID can do. You need an appropriate backup strategy to recover the mistakenly deleted data. So with RAID, you still need to do a backup cluster, and disk mirroring is the same.

2. If you only do a full backup, it is impossible to do it often due to the size and time of the full backup.

And there is only a full backup, and you cannot restore the database to a certain point in time. Therefore, we need full backup + log backup. For example, a full backup every day and a log backup every one hour or several minutes. When it comes to differential backup, because Microsoft's differential backup records the changes that have taken place since the last full backup, if the database changes frequently, it will not be long before the differential backup is close to the size of the full backup. so it's not appropriate in this case. Therefore, the scheme of full backup + log backup is suitable for the vast majority of users.

3. If you only make a backup locally in the database, in case the disk is damaged, or the entire server hardware is damaged, the backup is gone, and the database cannot be restored.

Therefore, you need to transfer the backup file to another physical hardware. Most users do not use tape drives, so they do not consider them. In general, we need another cheap server or PC to store database backups to prevent backup loss caused by hardware damage.

4. You can finish the backup locally on the database server, and then transfer the backup files to the standby machine in some way.

Did you send it immediately after the backup? in fact, you can consider using the T-SQL statement to write the script that sends the backup.

5. After the backup files are transferred to the standby, can you rest easy?

No, no, no. As a DBA, you also need to check whether the backup files on the standby machine can restore the database to the latest. If you use log backup, will the database not be able to recover to the latest due to the loss of a log backup file? how to check that there is a gap between log backup files

What to do when there is an error in SQLServer database backup

6. In order to restore the database as up-to-date as possible, you may perform a log backup every 10 minutes (or even 1 minute), so in case the database goes down, restore hundreds of log files manually during recovery. Isn't that realistic?

7. If your company has a lot of database servers (like my company) and the disk space is limited, you have to log in to the server often to delete the old backup files. If you forget one day, or May Day holiday, you will be in trouble when you run out of disk space.

8. when the database is backed up, it will not check the integrity of the data page. if the data page is broken, the backup job will still be executed and will not report an error. By the time you find that there is an error in the data page, you may have deleted the early backup because of insufficient disk space, and the rest of those backups may contain corrupted data pages. If the corrupted data page is the header of a table, you will never be able to recover the table.

9. So you need to perform regular DBCC checks to find the integrity of the database page as soon as possible.

You cannot delete the old backup until you have finished the DBCC check to prevent problems with the new backup. Therefore, the work of deleting backup files becomes a bit troublesome.

10. You may know that SQLServer provides a database maintenance plan.

Yes, you can use it to make regular backups and perform DBCC checks, but all of this is limited to local operations. In order to make the database reliable, you still need to transfer the local backup to the standby machine yourself.

Thank you for reading! This is the end of this article on "the solution when there is an error in SQLServer database backup". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.