As I mentioned in my last post, many people may be considering upgrading their SQL Server Instance(s) due to the release of SQL Server 2012 and the upcoming release of SP1. Because of this reason I thought it would be a good idea to highlight the importance of the command for people who are planning to upgrade from SQL Server 2000.
Before I get into the details about the command, let me just highlight that DBCC CHECKDB is a command that checks the logical and physical integrity of all the objects in the specified database and should be run on a regular basis. If you are a SQL Server DBA but never used or heard about DBCC CHECKDB before then you need to do some reading about it and start using it!
In versions prior to SQL Server 2005, it was possible to import invalid data into databases. This was resolved for databases created in SQL Server 2005 and later versions by adding column-value integrity checks. This was a great solution to avoid the issue in the future but how do you deal with the invalid data issue in older databases that are being upgraded?
DBCC CHECKDB ([DATABASENAME]) WITH DATA_PURITY
When this command is executed it will confirm if there are any data issues in the specified database. Once there are no issues/errors then DBCC CHECKDB column will check the column value integrity by default aka you don’t have to specify the DATA_PURITY anymore.
For more information about this command, I recommend Paul Randal's post:
and of course MSDN: DBCC CHECKDB.