Possible errors in data backup and solutions

This article summarizes the database backup process of all possible errors, you can refer to the following.

If you do not have a long DBA, the backup of the database some worry, hoping to find a way to ease your backup solution, then this article is absolutely suitable for you.

On the database backup recovery principle, we are more or less familiar with. But how reliable are your current database backups? Can you sleep at ease? If the answer is yes, then there is no need to spend more time to see the following, if you feel that is not enough peace of mind, always worry about the database which day bad repair, then go on to see:

1. I have raid, do I need to do database backup? With raid, if part of the disk is damaged, you can repair the database, and in some cases the database can even continue to use. But what if one day your co-worker accidentally deletes an important record? RAID is powerless. You need a proper backup strategy to restore the data that was mistakenly deleted. So with raid, you still need to do backup clusters, and disk mirroring is the same.

2, if you only do full backup, then limited to full backup size and backup time, can not be done often. And only full backups, you cannot restore the database to a point in time. Therefore, we need full backup + log backup. For example, a full backup every day, every 1 hours or a few minutes a log backup. When it comes to differential backups, because Microsoft's differential backup records the changes that have taken place since the last full backup, if the database changes very often, it won't be long before the differential backup is close to the size of the full backup, so it's not appropriate. Therefore, full backup + log backup scheme for the vast majority of users.

3, if you are only local backup in the database, in case of disk damage, or the entire server hardware damage, backup is gone, you can not restore the database. Therefore, you need to transfer the backup files to another physical hardware. Most users do not use a tape drive, so they are not considered. In general, we need another cheap server or PC to hold the backup of the database to prevent the loss of the backup caused by the hardware damage.

4, you can do a backup of the database server locally, and then use some way to transfer the backup files to the standby machine. Did you wear it right after the backup was completed? You can actually consider using a T-SQL statement to write a script that transmits backups.

5, backup files transferred to the standby, you can sit back and relax? As a DBA you also need to check whether the backup files on the standby can restore the database to the latest, if the use of log backup, will not be due to the loss of a log backup files caused the database can not be restored to the latest? How do I check for suspended between log backup files?

6. In order to restore the database as soon as possible to the latest, you may every 10 minutes (or even 1 minutes) to perform a log backup, if the database is broken, in the recovery, manual recovery of hundreds of log files, is not too realistic?

7, if your company has a lot of database servers (like my company), and limited disk space, then you have to log on to the server often to delete old backup files, if the day forgot, or 511 long vacation, the disk space ran out, on the trouble.

8, the database in the backup, and 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 complain, wait until you find the data page has errors, you are likely to have been due to insufficient disk space, and deleted the early backup, The rest of the backup may be a corrupted data page, and if the corrupted data page is the header of a table, you will never be able to recover it.

9, so you need to perform a DBCC check periodically to discover the integrity of the database page as early as possible. You cannot delete old backups until a DBCC check is completed to prevent new backups from being problematic. So, the job of deleting backup files is a bit of a hassle.

10, you may know that SQL Server provides a database maintenance plan. Yes, it can be done on a regular basis to perform a DBCC check, but this is limited to native operations. 　　To make the database reliable, you still need to transfer your local backup to the standby. In summary, do you have your backup ready? Did you check it? Is it going to take you a lot of time to delete old backups, especially when the network conditions are bad? How soon can you find out if a database backup file is routed at some point? company value does the late-night colleague have permission to check the database backup? please contact the site, timely note your name. Contact Email: edu#chinaz.com (change # to @).