Last updated on August 28, 2017
Had some fun talking to the fine people at the PDAVP and wanted to post some links.
The video:
Introduction to dbatools
Their awesome virtual chapter:
Data Architecture Virtual Group
The code:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| break # Prevent everything from running at once. | |
| $SqlServer = "SQL2016" | |
| Import-Module dbatools | |
| # Stop the current command if it throws an error or exception. | |
| $ErrorActionPreference = "Stop" | |
| # Multiple assignment, woo! | |
| # Give us more information about what is going on behind the scenes! | |
| $DebugPreference = $VerbosePreference = "SilentlyContinue" | |
| # If you dont have all the help you see in this demo, run the Update-Help command in an admin shell. | |
| # if you are familiar with the *nix word | more only works in console, but great for paging help content or long strings. | |
| Get-Help about_redirection | |
| Get-Help about_* | |
| # Native help | |
| Get-Help *dba* | |
| Get-Help Test-DbaSqlPath | |
| Get-Help Test-DbaSqlPath -Examples | |
| Get-Help Test-DbaSqlPath -Online | |
| Find-DbaCommand *bios* | |
| # PowerShell naming conventions are a bit particular, but let you live within your own namespace. | |
| Get-Verb | |
| # Get/Test - Good (though test-dbalastbackup can restore databases, but no harm should come from that.) | |
| # Anything else - potentially state altering in the dbatools project. | |
| # If you do data things, you want to learn select and where :) | |
| Get-Help Select-Object | |
| Get-Help Where-Object | |
| Get-Help Format-* | |
| # Things I use on the daily. | |
| # Less code = less maint, less things to go wrong, less me being a dangus. | |
| Get-DbaLastBackup -SqlInstance $SqlServer | |
| Get-DbaLastBackup -SqlInstance $SqlServer | | |
| Select-Object ComputerName, Database, LastFullBackup, LastLogBackup, LastDiffBackup | | |
| Sort-Object -Property LastFullBackup | | |
| Format-Table | |
| Test-DbaJobOwner -SqlInstance $SqlServer -Login sa | ft | |
| Test-DbaDatabaseOwner -SqlInstance $SqlServer -TargetLogin sa | ft | |
| # Set-DbaDatabaseOwner | |
| Test-DbaVirtualLogFile -SqlInstance $SqlServer | where {$_.Count -gt 50} | Out-GridView | |
| # Base command | |
| Get-DbaDatabase -SqlInstance $SqlServer | |
| # Select useful fields | |
| Get-DbaDatabase -SqlInstance $SqlServer -ExcludeAllSystemDb | select Name, SizeMB | |
| # Assign and store to a table. | |
| # Gather databases metadata | |
| $Databases = Get-DbaDatabase -SqlInstance $SqlServer -ExcludeAllSystemDb | select Name, SizeMB | |
| # Convert that metadata to a datatable object, that which SQL Server loves. | |
| $DataTable = Out-DbaDataTable -InputObject $Databases | |
| # Write this to a table, and even generate it at runtime if we want! | |
| Write-DbaDataTable -SqlInstance $SqlServer -Database tempdb -InputObject $DataTable -Table DataSize -AutoCreateTable # Only for the demo! | |
| # Query to verify it exists using some dbatools magic in the .Query command | |
| (Connect-DbaSqlServer -SqlInstance $SqlServer).Databases['tempdb'].Query( " select * from DataSize " ) | |
| # Things that I use to check on a new server I inherited. I LOVE the test commands! | |
| # SQL Server Settings | |
| Test-DbaMaxDop "sql2014" | ft # Cool output and supports testing databases and server level stuff in different versions! | |
| Test-DbaMaxMemory $SqlServer # Gives a recommend memory setting! | |
| # Network Crap. | |
| Test-DbaSpn -ComputerName $SqlServer | ft # Ugh, I hate SPNs. | |
| Test-DbaConnectionAuthScheme -SqlInstance $SqlServer # Checks if you are using Kerebos, screw NTLM! | |
| # Windows Server configuration garbo, I thought I got to focus on db things :) | |
| Test-DbaDiskAlignment -ComputerName $SqlServer | ft # Gives a recommended set of best practice flags! | |
| Test-DbaDiskAllocation -ComputerName $SqlServer | ft # Checks if our disks are allocated properly! | |
| Test-DbaPowerPlan -ComputerName $SqlServer # So annoying to get at, so easy to check. | |
| # Database stuff which might be wrong. | |
| Test-DbaDatabaseCollation -SqlInstance $SqlServer # Checking if correlation is screwy. |
by
Be First to Comment