SQL Server Enterprise Platform Management Practice book notes-what to do when our backups are corrupted

Original: SQL Server Enterprise Platform Management Practice reading notes-what to do when our backups are corrupted

As a database administrator the most painful is that when the database is down to need to find a backup, but at this time suddenly found that the backup file is also bad, which means that the data will be lost, this may lose the position, the job is not insured, so we must ensure that the integrity of the backup, There are a few reasons why this usually happens:

1. The backup files and databases are placed on the same (or a set) of physical disks. The disk fails and the backup is lost.

2, backup media with bad, or do the network backup, the data in the network transmission has been damaged.

3, the database in the full backup, file backup or file group backup, the contents of the content has been bad.

So based on this, we want to avoid is the above three cases of occurrence, in addition to the case is SQL Server in the database backup time to save time, basically just very simple copy of the data page, do not do consistency check. But when recovering, you need to restore the database (Recover) to a point in time for transactional consistency. What should we do if corruption in the backup prevents SQL Server from rolling back (redo and undo) and recovering the action?

In fact, in the real bad situation, most of this problem is caused by a hardware error, but this kind of error is often permanent with the contents of the bad backup file, before SQL 2005 version, encountered this problem can only go to find an earlier backup. But that means there's a lot of data loss to be generated.

So after SQL 2005 introduced a new "Ignore error" recovery function, this situation in distress is a good time to play a role.

The command is: Continue_after_error

This command is a new option in the Restore command. It will cause the restore operation to skip the error and restore all the contents of the SQL Servr all existing features. After the data restore is complete, you can apply subsequent transaction log backups to restore the database. If an error is encountered during log recovery, SQL Server reports in the log and does not allow the user to access and manipulate the pages related to those transactions. The database will love online in as many cases as possible. So in most cases, the overall database can be recovered, but some of the data may be lost.

Data loss depends on the error encountered. For example, an error in a General data page will only cause the page to enter a suspicious state, but the database recovery will continue. Problematic page numbers are written to disk and logged to the Suspect_pages table and error logs, reminding administrators to continue processing them after the recovery is complete. If you do not set Continue_after_error,sql server, the entire recovery action will stop whenever you encounter a problem with a page.

If the error occurs in some of the more critical areas, such as the file header information for a data file, then recovery is likely to fail completely and the database cannot be recovered. All this method is only used for fire fighting and cannot guarantee the effect of each use.

After you have finished restoring the database using this command, remember to check the error log for more information.

The command syntax is as follows:

RESTORE DATABASE database_namefrom backup device with Continue_after_error,norecovery ....

The administrator uses DBCC CHECKDB to repair the database while ignoring the error while continuing to perform the restore sequence. To make checkdb run with the maximum consistency after using the restore Continue_after_error, we recommend that you use the WITH TABLELOCK option in the DBCC CHECKDB command. In very rare cases, there may not be enough information to repair the database, CHECKDB has no way to fix the database, and data loss will inevitably occur. Not that, with the restore continue_after_error, it doesn't matter if the backup is broken.

In fact, the most critical is to establish a standby server, change the single disk embarrassment.

Continue_after_error just skips everything by command. It is able to skip errors and recover all the data that can be read out, thereby maximizing data recovery. However, some data are not acceptable for systems with higher consistency requirements.

For such a system, when establishing a backup and choosing a recovery strategy, consider the worst case scenario and plan ahead to minimize the loss.

Can find a spare machine, do log Shipping, this method is recommended, mainly low cost

There are several advantages:

1, compared with the physical image of such technology, this scheme is more economical. Backup service hardware requirements are not high, as long as the hard disk is large enough.

2. Although SQL Server provides several backup verification mechanisms, the only way to ensure full reliability of backup is to actually recover it.

3, early recovery backup, so that when a real disaster occurs, only need to restore the last log backup. Without the need to wait for that long full backup recovery at burn, disaster recovery time can be greatly reduced.

4. Although the database on the standby cannot be modified, the database can be restored to read-only mode using the standby parameter. You can offload some report queries to your standby, reducing the burden on your production servers.

In short, data security is very important, disaster recovery time requires a very short database, if there is no mirror technology to ensure that the standby server is very necessary.

SQL Server Enterprise Platform Management Practice book notes-what to do when our backups are corrupted