SQL SERVER Operations Journal-database backup

Overview

Yesterday afternoon saw suddenly, "furnace stone legend" game database outage and caused data loss accident news. When I first saw it, it was full of magic. Blizzard Ah, NetEase ah.

Are all very bull-forked companies. I like all the games they play.

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/603809/201701/603809-20170119085415187-1938163926. PNG "style=" margin:0px;padding:0px;border:0px; "/>

When I saw that the first time I started to repair, restart the server, and try to recover data, my idea was their high-availability program? Why not switch now?

When I see the associated backup database also fails, it becomes more silent. In fact, such a thing in our customers every year will meet a lot. Shortly before, there was a hospital where databases and backups were both corrupted and there were no high-availability scenarios.

Although eventually helped them repair the good database, but still lost some of the data, and in the middle of 1 days, the business is manual operation, seriously affect the business.

For large companies such as furnaces, the corresponding plan should be done very fully, the accident may also have other reasons.

Analysis

The reason for this is that, when confronted with the same problem, the associated operations and DBAs are desperate. Summarize the above questions:

1. Lack of highly available programmes

2. Develop a better backup strategy

Solve

There are small partners mentioning high availability, which is not written here. The main high-availability scheme is too many, in an article difficult to say clearly, so this article first give a backup solution.

The following gives me a previous backup strategy for a foreign company, can solve the above mentioned backup problem. Small friends can refer to the following:

Location of Backup

1. Local backup, placed on a different physical disk than the database file

2. Machine Backup. Synchronize backups to dedicated NAS in real time using auto-sync software

3. Offsite Backup (optional)

Backup method

First, the recovery model strongly recommends the use of full mode. In order to ensure the database corruption, the fastest time to resume business.

1. Weekly fully prepared

2. Daily differences

3. Log every half hour

The frequency of backups can be adjusted according to the specific business situation.

Options for backup

Our backup strategy looks perfect so far. But is that the truth? The answer is in the negative.

We've done a seemingly perfect backup. But if our database itself already exists page corruption, then we do more backup is futile. Because the backed up files are also corrupted.

So how do we fix it? The best approach is to restore the backups on a regular basis and then run DBCC CHECKDB immediately. If the condition does not allow continuous restores and checks, then using the Restore VERIFYONLY command is another best option for you. However, restore VERIFYONLY is not used alone. It must be mated with the with CHECKSUM. means that use the with CHECKSUM parameter on backup, and then periodically run the restore VERIFYONLY on the backed-up file to verify the validity of the backup file. If some pages in the database are corrupted, jobs that use with CHECKSUM to back up will fail immediately. This gives us the first time to discover the problem of database page corruption.

Give me a chestnut:

BACKUP DATABASE AdventureWorks to DISK = ' G:/backups/adventureworks_full.bak ' with CHECKSUM

If you change the file data backup file and then run restore verifyonly on that file, you will get the following hint:

Server:msg 3189, Level A, State 1, line 1
Damage to the backup set is detected.

Server:msg 3013, Level A, State 1, line 1
VERIFY DATABASE is terminating abnormally.

The media cluster on device ' D:\tttttt.bak ' is not structurally correct. SQL Server was unable to process this media cluster.

Alarm

Backups can fail for a variety of reasons, such as a backup disk full of space, and so on when the database is damaged, suddenly found that the backup task failed, and then perfect backup strategy hundred. So on the backup task, increase the mail alarm mechanism, if the backup failed, you can know and solve the first time.

SQL SERVER Operations Journal-database backup