In every RDBMS environment, taking backup from consistent is an important task that DBAs need to handle. Base on our experience most of DBAs just take backup from the existing database without structural and consistency check of the database, which they might backup data page-corrupted databases.
Why DBCC CheckDB is important?
Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:
- Runs DBCC CHECKALLOC on the database.
- Runs DBCC CHECKTABLE on every table and view in the database.
- Runs DBCC CHECKCATALOG on the database.
- Validates the contents of every indexed view in the database.
- Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
- Validates the Service Broker data in the database.
This means that the DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands do not have to be run separately from DBCC CHECKDB.
Note: DBCC CHECKDB is supported on databases that contain memory-optimized tables but validation only occurs on disk-based tables. However, as part of database backup and recovery, a CHECKSUM validation is done for files in memory-optimized filegroups. Since DBCC repair options are not available for memory-optimized tables, you must back up your databases regularly and test the backups. If data integrity issues occur in a memory-optimized table, you must restore from the last known good backup.
Be aware that DBCC CHECKDB with any of the REPAIR options are completely logged and recoverable, and Microsoft always recommends a user use CHECKDB with any REPAIR options within a transaction (execute BEGIN TRANSACTION before running the command) so that the user can confirm and to accept the results of the operation. Then the user can execute COMMIT TRANSACTION to commit all work done by the repair operation. If the user does not want to accept the results of the operation, user can execute a ROLLBACK TRANSACTION to undo the effects of the repair operations.
The following TSQL script shows the last DBCC CheckDB operation on every database within the SQL Server instance:-
Create Table #result (DBName sysname,Last_DBCC datetime); Create Table #temp (ParentObject sysname,Object sysname,Field sysname,Value nvarchar(200)); Exec sp_MSforeachdb ' USE [?]; Insert Into #temp Exec (''dbcc dbinfo() With Tableresults;''); Insert Into #result select db_name(),VALUE from #temp where Field = ''dbi_dbccLastKnownGood''; Truncate Table #temp; '; Select * From #result; Drop Table #result; Drop Table #temp;
Author: Hamid Jabarpour Fard