< Back
Get-DbaLastGoodCheckDb
Post
NAME Get-DbaLastGoodCheckDb
SYNOPSIS
Get date/time for last known good DBCC CHECKDB
SYNTAX
Get-DbaLastGoodCheckDb [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
[[-SqlCredential] <Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>]
[[-InputObject] <System.Object[]>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
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 and higher.
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.
SQL Server 2008R2 has a "bug" that causes each databases to possess two dbi_dbccLastKnownGood fields, instead of
the normal one.
This script will only display the newest timestamp. If -Verbose is specified, the function will announce every
time more than one dbi_dbccLastKnownGood fields is encountered.
PARAMETERS
-Database [<System.Object[]>]
Specifies one or more database(s) to process. If unspecified, all databases will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
Specifies one or more database(s) to exclude from processing.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<System.Object[]>]
Enables piped input from Get-DbaDatabase
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances. Defaults to localhost.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: CHECKDB, Database
Author: Jakob Bindslet (jakob@bindslet.dk)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
Ref:
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/che ... ll-be-run/
https://www.mssqltips.com/sqlservertip/ ... performed/
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Get-DbaLastGoodCheckDb -SqlInstance ServerA\\sql987
Returns a custom object displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated,
DaysSinceLastGoodCheckDb, Status and DataPurityEnabled
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Get-DbaLastGoodCheckDb -SqlInstance ServerA\\sql987 -SqlCredential sqladmin | Format-Table -AutoSize
Returns a formatted table displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated,
DaysSinceLastGoodCheckDb, Status and DataPurityEnabled. Authenticates using SQL Server authentication.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Get-DbaLastGoodCheckDb -SqlInstance sql2016 -ExcludeDatabase "TempDB" | Format-Table -AutoSize
Returns a formatted table displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated,
DaysSinceLastGoodCheckDb, Status and DataPurityEnabled. All databases except for "TempDB" will be displayed in the
output.
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Get-DbaDatabase -SqlInstance sql2016 -Database DB1, DB2 | Get-DbaLastGoodCheckDb | Format-Table -AutoSize
Returns a formatted table displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated,
DaysSinceLastGoodCheckDb, Status and DataPurityEnabled. Only databases DB1 abd DB2 will be displayed in the output.
RELATED LINKS
https://dbatools.io/Get-DbaLastGoodCheckDb
SYNOPSIS
Get date/time for last known good DBCC CHECKDB
SYNTAX
Get-DbaLastGoodCheckDb [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
[[-SqlCredential] <Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>]
[[-InputObject] <System.Object[]>] [-EnableException <Switch>] [<CommonParameters>]
DESCRIPTION
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 and higher.
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.
SQL Server 2008R2 has a "bug" that causes each databases to possess two dbi_dbccLastKnownGood fields, instead of
the normal one.
This script will only display the newest timestamp. If -Verbose is specified, the function will announce every
time more than one dbi_dbccLastKnownGood fields is encountered.
PARAMETERS
-Database [<System.Object[]>]
Specifies one or more database(s) to process. If unspecified, all databases will be processed.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables
advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own
try/catch.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
Specifies one or more database(s) to exclude from processing.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<System.Object[]>]
Enables piped input from Get-DbaDatabase
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory -
Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances. Defaults to localhost.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: CHECKDB, Database
Author: Jakob Bindslet (jakob@bindslet.dk)
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
Ref:
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/che ... ll-be-run/
https://www.mssqltips.com/sqlservertip/ ... performed/
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Get-DbaLastGoodCheckDb -SqlInstance ServerA\\sql987
Returns a custom object displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated,
DaysSinceLastGoodCheckDb, Status and DataPurityEnabled
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Get-DbaLastGoodCheckDb -SqlInstance ServerA\\sql987 -SqlCredential sqladmin | Format-Table -AutoSize
Returns a formatted table displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated,
DaysSinceLastGoodCheckDb, Status and DataPurityEnabled. Authenticates using SQL Server authentication.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Get-DbaLastGoodCheckDb -SqlInstance sql2016 -ExcludeDatabase "TempDB" | Format-Table -AutoSize
Returns a formatted table displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated,
DaysSinceLastGoodCheckDb, Status and DataPurityEnabled. All databases except for "TempDB" will be displayed in the
output.
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Get-DbaDatabase -SqlInstance sql2016 -Database DB1, DB2 | Get-DbaLastGoodCheckDb | Format-Table -AutoSize
Returns a formatted table displaying Server, Database, DatabaseCreated, LastGoodCheckDb, DaysSinceDbCreated,
DaysSinceLastGoodCheckDb, Status and DataPurityEnabled. Only databases DB1 abd DB2 will be displayed in the output.
RELATED LINKS
https://dbatools.io/Get-DbaLastGoodCheckDb