dbatools is a free PowerShell module with over 100 SQL Server administration, best practice and migration commands included.


Get-DbaLastGoodCheckDb

This command retrieves and compares the date/time for the last known good DBCC CHECKDB, as well as the creation date/time for the database.

This function supports SQL Server 2005+

Please note that this script uses the DBCC DBINFO() WITH TABLERESULTS. DBCC DBINFO has several known weak points, such as:
– DBCC DBINFO is an undocumented feature/command.
– The LastKnowGood timestamp is updated when a DBCC CHECKFILEGROUP is performed.
– The LastKnowGood timestamp is updated when a DBCC CHECKDB WITH PHYSICAL_ONLY is performed.
– The LastKnowGood timestamp does not get updated when a database in READ_ONLY.

An empty ($null) LastGoodCheckDb result indicates that a good DBCC CHECKDB has never been performed.

Screenshots

get-dbalastgoodcheckdb

get-dbalastgoodcheckdb2

Examples

Get-DbaLastGoodCheckDb -SqlServer ServerA\sql987

Returns a custom object with Server name, Database name, and the date the last time a good checkdb was performed

Get-DbaLastGoodCheckDb -SqlServer ServerA\sql987 -Detailed | Format-Table -AutoSize

Returns a formatted table displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated, DaysSinceLastGoodCheckDb, Status and DataPurityEnabled

References

DBCC CHECKDB:
https://msdn.microsoft.com/en-us/library/ms176064.aspx
http://www.sqlcopilot.com/dbcc-checkdb.html
Data Purity:
http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run/
https://www.mssqltips.com/sqlservertip/1988/ensure-sql-server-data-purity-checks-are-performed/

Author

This command was created by Jakob Bindslet. You can find Jakob on his blog and on LinkedIn.

From PowerShell, execute   Get-Help Get-DbaLastGoodCheckDb -Detailed   for more information on this function. Want to see the source code? See it on GitHub