Integrity check

A corrupt database is very rare, though it can happen due to issues with memory or I/O subsystems, so it’s important to look out for it. You can use:

DBCC CHECKDB (AdventureWorks);
GO

There are various parameters you can include in the code above (see Books Online), as well as similar checks you can do, though these are all included within DBCC CHECKDB.

It is good practice to run DBCC CHECKDB on a database prior to backup, and fairly regularly. It does consume a fair amount of memory and I/O. Also, it takes up a lot of space on disk, as it takes a snapshot of the database to check, as well as taking up space in the tempdb.

There are 2 repair options. For both of these, the database needs to be in SINGLE_USER mode:
REPAIR_BUILD – rebuilds the corrupt pages based on non-corrupt pages. There is no data loss, but this only works with certain forms of corruption
REPAIR_ALLOW_DATA_LOSS – almost always produces data loss. It’d be preferable to restore the database.