Analyze nine of scenarios that occur during database backup

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 not enough peace of mind, always worry about the database which day of bad repair, then go on to see:

In the first case:

Phenomenon: RAID, do you need to do a database backup?

Answer: Need. 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.

In the second case:

Phenomenon: We need full backup + log backup?

Answer: 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.

In the third case:

Phenomenon: If you do backup only locally in the database, if the disk is damaged, or the entire server hardware is damaged, and the backup is gone, you cannot recover the database.

Answer: Therefore, you need to transfer the backup file 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.

The fourth situation:

You can do backups locally on the database server, and then use certain methods to transfer the backup files to the standby machine. Did you wear it right after the backup was completed? In fact, you might consider using a T-SQL statement to write a script that transmits backups.

The fifth situation:

Phenomenon: After the backup file is transferred to the standby, can you sit back and relax?

Answer: No. As a DBA, you also need to check the backup files on the standby to restore the database to the latest, if the use of log backups, will not because of the loss of a log backup files caused the database can not be restored to the latest? How do I check the existence of suspended between log backup files?

The sixth situation:

In order to restore the database as much as possible to the latest, you may perform a log backup every 10 minutes (or even 1 minutes), so if the database is broken, it is not realistic to manually recover hundreds of log files at the time of recovery.

The seventh situation:

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

The eighth situation:

When the database is backed up, it does not check the integrity of the data page, if the data page is broken, the backup job will still execute, and will not complain, until you find that the data page is wrong, you are also 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. So you need to perform a DBCC check regularly to discover the integrity of your database pages 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.

The nineth situation:

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?