Hello,
to check the consistency of a database you can refer to Eduardo's reply. DBCC CHECKDB will check a whole database.
Regarding your question how you can recover from a corruption automatically I clearly have to state:
YOU CANNOT.
There are various types of corruptions, e.g. pages might not linked properly anymore, links between pages might be missing completely, pages from the allocation maps (GAM, IAM, SGAM) might contain incorrect data, pages might be damaged to an extent that they do not even have the physical structure of a SQL Server page anymore.
First you need to understand that corruptions are usually caused by malfunctions on hardware or driver level. This means that due to a malfunction on these lower layers a database page or more pages are damaged - meaning their content is not fully correct anymore to a certain extent. If you are very very lucky this affects a page which was cached for faster access in your main memory and the corrupt hasn't yet been written back to the disk. This is what we call a transient corruption and it will disappear after you restart the SQL Server instance. To be honest I see such situations very rarely or hardly ever.
In most cases the corrupt pages are in the database files or in the log files. This means the incorrect information is on disk and there is no proper version of the affected pages anymore. This should make it clear why you cannot simply "recover" from a corruption. A corruption is a damaged page - there is no way to make the database guess what the correct content of a corrupt page would have been and to let the DB simply revert the page content back to the correct version.
In most cases you will have more than one corrupt page. In order to judge how bad the situation is you need very exhaustive knowledge of SQL Server to understand which kind of pages (e.g. index pages, data pages, leaf pages, allocation map pages) are affected and which impact this has.
I know that there is one way to let SQL Server simply throw away everything that looks corrupt until it reaches a clean database again (repair_allow_data_loss option of DBCC CHECKDB) but please make sure that you understand that IT IS NOT SUPPORTED BY SAP to use this command and the reason why is very simple. You damage even more! By using this option SQL Server will simply throw away more or less everything in the database that is not properly interpretable or readable anymore. On the first hand you might still have corruptions left afterwards as depending on how bad the situation is, it might not even be possible anymore to return to a physically consistent state. On the other hand and much more important: this leads to completely uncontrollable, unpredictable dataloss and there is no way to log or trace what is thrown away. You will have data loss and this will cause inconsistencies on SAP application level (usually SEVERE inconsistencies).
For this reason there is only one strategy to be prepared for corruptions:
- think about your business requirements: how much days of data loss are acceptable?
- thoroughly design a proper backup strategy which allows you to restore and recover in a way that meets your requirements
- implement this backup strategy and have an eye on it constantly
- never ASSUME just because you take a backup you're on the safe side. A backup can also be corrupt so test your backups to KNOW that you are on the safe side
- thouroughly design a proper consistency check strategy to make sure you are aware of any corruptions as soon as possible
- implement this consistency check strategy and constantly have an eye on it
Implementing a proper backup and check strategy is one of the main tasks of a database or basis administrator and must not be left unattended.
Regards,
Beate
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.