When you upgrade a database from 2000 to 2005 or 2008 one of the things you need to do is run the DBCC CHECKDB command with the DATA_PURITY option at least one time to set a flag in the boot page of the database and from that point forward, every DBCC CHECKDB operation will perform the data purity checks.
I have been looking for a way to programmatically check to see if the flag was set and never really found anything. The other day, I read a blog post by Paul Randal on how to tell if the flag was set.
CHECKDB From Every Angle: How to tell if data purity checks will be run?
I found this very interesting, because I have been wondering about what flag this was for a while. However, this was still too manual for me to do. It’s just easier to run the command against all of your databases if you are not sure if the flag is set.
Then I read Sankar Reddy's blog post today, and then it clicked. I can use the same logic to check for the data purity flag.
Database Internal version; Create Version and Current Version
Note: If you read the preceding blog posts, the following code will make more sense.
I created a query based on the logic in Paul’s blog and the code in Sankar’s blog to come up with the following script.
DBCC TRACEON (3604); GO CREATE TABLE #DBCC ( ParentObject VARCHAR(255) , [Object] VARCHAR(255) , Field VARCHAR(255) , [Value] VARCHAR(255) ) CREATE TABLE #DBCC2 ( DatabaseName VARCHAR(255), ParentObject VARCHAR(255) , [Object] VARCHAR(255) , Field VARCHAR(255) , [Value] VARCHAR(255) ) EXEC master.dbo.sp_MSFOREACHDB 'USE ? INSERT INTO #DBCC EXECUTE (''DBCC DBINFO WITH TABLERESULTS''); INSERT INTO #DBCC2 SELECT ''?'', * FROM #DBCC; DELETE FROM #DBCC' SELECT * FROM #DBCC2 WHERE Field = 'dbi_DBCCFlags' AND Value = 0 AND DatabaseName NOT IN ('master','model') DROP TABLE #DBCC DROP TABLE #DBCC2 GO
Next, I took a backup of a 2000 database and restored it to my 2008 instance just to make sure I had a database that fit the criteria. After running the script, you see the following results.
As you can see, I have several databases on my 2008 instance and the only one without the data purity flag set is the CMS database I restored from the 2000 instance. I thought this was really cool and maybe a few of you will find this useful as well.
