search

dataplat / dbatools

🚀 SQL Server automation and instance migrations have never been safer, faster or freer
https://dbatools.io
MIT License
2.44k stars 795 forks source link

Test-DbaLastBackup - Add info to output messages, or as part of the properties returned. #5375

Open kmramsey opened 5 years ago

kmramsey commented 5 years ago

name: Feature request/idea :bomb: about: Suggest a new feature or enhancement

When you do a DBCC in a SQL Query window... "dbcc checkdb" you recieve a lot of information about the database being checked. I'd like to see that information in the result set of the Test-DbaLastBackup command.

This feature is mainly due to needs to audit detailed specifics for DBCC commands. The information is generally helpful. This would basically be in addition to things you can get when you follow advice from a blog like this... https://sqldbawithabeard.com/2017/03/22/taking-dbatools-test-dbalastbackup-a-little-further/

From what I can tell, there is a line in the code that does this... $dbccresult = Start-DbccCheck -Server $destserver -DbName $dbname 3>$null This feature would be to capture these results.

Proposed technical details (if applicable)

SourceServer : TestServer : Database : FileExists : Size : RestoreResult : DbccResult : RestoreStart : RestoreEnd : RestoreElapsed : DbccStart : DbccEnd : DbccElapsed : BackupDates : BackupFiles :

Add... (output trucated for issue length) DBCCResult set :
{DBCC results for 'dbname'. Service Broker Msg 9675, State 1: Message Types analyzed: 14. Service Broker Msg 9676, State 1: Service Contracts analyzed: 6. Service Broker Msg 9667, State 1: Services analyzed: 3. Service Broker Msg 9668, State 1: Service Queues analyzed: 3. Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0. Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0. Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0. Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0. DBCC results for 'sys.sysrscols'. There are 1146 rows in 13 pages for object "sys.sysrscols". DBCC results for 'sys.sysrowsets'. There are 154 rows in 2 pages for object "sys.sysrowsets". DBCC results for 'sys.sysclones'. There are 0 rows in 0 pages for object "sys.sysclones". DBCC results for 'sys.sysallocunits'. There are 177 rows in 2 pages for object "sys.sysallocunits".}

Latest version of dbatools as of writing 0.9.803

kmramsey commented 5 years ago

Another reasons for this:

If something is wrong with the database... We would have to re-run the DBCC to get this detailed information.

sqllensman commented 5 years ago

The requested output can be obtained by changing the code in the internal function Start-DbccCheck AT line 21 there is a call $null = $server.Query("DBCC CHECKDB ([$dbname])")

If this is changed to

$query = "DBCC CHECKDB ([$dbname])" $results = Invoke-DbaQuery -SqlInstance $server -Database $dbName -Query $query -MessagesToOutput -EnableException

then the $results variable will contain all Informational output

This can then be used to allow the DBCCResult information to be captured.

sqllensman commented 5 years ago

Looking at the code for Start-DbccCheck also indicates that it will not complete correctly for any database that has a ']' character in the name

For example a database named Sandbox] will return the following results for DbccResult Unclosed quotation mark after the character string 'dbatools-testrestore-Sandbox])'. Incorrect syntax near 'dbatools-testrestore-Sandbox])

This is due to failure to correctly escape the ] character when calling the DBCC CHECKDB command