dbatools

Start-DbaAgentJob, fixes #7444 by @niphlod
Added connection timeout. Fixes #7417 by @sanderstad
Install-DbaInstance - Test for allowed ACTION before adding SQLTEMPDBFILECOUNT by @andreasjordan
Resolve-NetBiosName - Return ComputerNamePhysicalNetBIOS if clustered by @andreasjordan
appveyor runner, test dependants by @niphlod
PULL_REQUEST_TEMPLATE.md - Just fixing a typo by @andreasjordan
Added SQL 2019 CU11 by @MikeyBronowski
Remove-DbaAgentSchedule - Complete rewrite by @andreasjordan
Replication commands - document commands as unsupported by @andreasjordan
Fix typo in parameter ReplaceInName by @Zhunya
buildref, fixes #7418 by @niphlod
SQL Watch commands cannot be used in PowerShell 7 by @wsmelton
Get-DbaService - Skip after first success / added more verbose by @andreasjordan
Copy-DbaDbTableData - Alter regex to include ALTER TABLE by @andreasjordan
New-DbaDbUser - Remove wrong example by @andreasjordan
Restore-DbaDatabase - Changed WhatIf message / added deep copy of FileList by @andreasjordan
New-DbaDbSequence - Don't use Test-Bound with defaults by @andreasjordan
Completely rewritten Add-DbaAgDatabase / Added Test-DbaAvailabilityGroup by @andreasjordan
Invoke-DbaAdvancedRestore (Restore-DbaDatabase) - Format time for .ToPointInTime as iso string by @andreasjordan
Export-DbaRegServer - Changed date format in filename to ISO Ymd by @andreasjordan
Export-DbaScript - Invoke .Script() as a workaround while exporting Availability Groups by @andreasjordan
Find-DbaLoginInGroup throws error when adding multiple groups to output by @gbargsley
New command for removing a linked server by @lancasteradam
New command for adding a linked server by @lancasteradam
Get-/Set-DbaTcpPort - Rewriting the commands and using Get-/Set-DbaNetworkConfiguration by @andreasjordan
Remove-DbaDbSnapshot - should ask for confirmation by @jpomfret
New-DbaDbSnapshot fix for Linux paths by @jpomfret
[#7383] Added parameter CommandTimeOut to the function Copy-DbaDbTableData by @mrlmachado
Get-DbaAgBackupHistory - Use Where-Object instead of .Where() for PS3 by @andreasjordan
Get-DbaLogin - Fixed parameter types by @andreasjordan
Get-/Set-DbaNetworkConfiguration - New commands for managing network configuration of instance by @andreasjordan
Update New-DbaDbTable.ps1 - example formatting by @MikeyBronowski
Start-DbaMigration - Fixed documentation by @andreasjordan
Write-DbaDbTableData - Added ColumnMap like in Import-DbaCsv by @andreasjordan
Test-DbaDiskAlignment - corrected indentation by @andreasjordan
Set-DbaAgentJob - Added parameter test by @andreasjordan
Invoke-DbaQuery.ps1: Parameter documentation by @NReilingh
Get-DbaHelp, gfm compatibility by @niphlod
Remove extra bogus.dll by @potatoqualitee
Copy-DbaLogin - Added AzureUnsupported by @andreasjordan
Test-DbaLastBackup - Added ReuseSourceFolderStructure by @andreasjordan
Test-DbaConnection - added -SkipPSRemoting / fixed getting Port by @andreasjordan
Get-DbaDbRoleMember - Changed output to align with ServerRoleMember by @andreasjordan
Connect-SqlInstance / Connect-DbaInstance - Move test for AzureUnsupported to Connect-DbaInstance by @andreasjordan
New commands: New-DbaDbFileGroup, Set-DbaDbFileGroup, and Remove-DbaDbFileGroup by @lancasteradam

More good stuff

Good stuff

it's been a while, like 2019 a while. Thanks to everyone who has contributed over the years 🙇

Big ol shoutout to @lancasteradam, @andreasjordan, @jpomfret, @wsmelton, @nvarscar and @niphlod and everyone else who has continued to make dbatools awesome "in these uncertain times".

Fixed a bug with Add-DbaAgDatabase and named instances.

Renamed and subsequently fixed an issue with the module manifest that removed Get-DbaBackupHistory. Now it is Get-DbaDbBackupHistory.

Fixed a bug with Invoke-DbatoolsRenameHelper which renamed Get-DbaLogin to Get-DbaErrorLog

Please visit dbatools.io/1.0 for more information

see dbatools.io/changelog

• Added xplat support!
• Updated dbatools config system
• Added Update-DbaInstance

dbatools.io/changelog

Detailed at more 1.0 progress and our new changelog!

Marching onward to dbatools 1.0, a ton of commands have been renamed to align with our now mature naming scheme. These changes were made in today's release, version 0.9.411 aka regularlegs, now available on GitHub and the PowerShell Gallery.

Here's the general idea:

• DbaDatabase has mostly been renamed to DbaDb with a couple exceptions
• DbaSql has been changed to just Dba, no exceptions. Think of Dba as "Sql".
• DbaConfig has been renamed to DbatoolsConfig
• TempDbConfiguration has been renamed to TempdbConfig
• All Configuration commands are Config except SpConfigure
• DbaDacpac has been renamed to DbaDacPackage. Dac is the prefix for our data-tier application commands.
• DbaDbQueryStoreOptions has been renamed to DbaDbQueryStoreOption

If you use the old name, it'll still work but will show a warning that the command name has been changed and its alias will be removed in 1.0

renaming

If you'd like help renaming dbatools scripts within your command, use the newly created function Invoke-DbatoolsRenameHelper, available in regularlegs. The screenshot below shows is what it looked like when I ran the this:

Thanks, PowerShell! 😎

other updates

There are other cool updates with this release. In preparation for my doomsday prepping with dbatools presentation at SQLGLA, I created a new command, Export-DbaInstance, which I'll write more about in a future post.

In order for Export-DbaInstance to work well, I had to create a number of underlying commands, some of which may be of interest to you.

• Get-DbaDbMail
• Get-DbaDbMailAccount
• Get-DbaDbMailProfile
• Get-DbaDbMailConfig
• Get-DbaDbMailServer
• Export-DbaCredential
• Export-DbaLinkedServer
• Get-DbaResourceGovernor
• Get-DbaRgResourcePool
• Get-DbaRgWorkloadGroup
• Get-DbaRgClassifierFunction
• Get-DbaPbmCategory
• Get-DbaPbmCategorySubscription
• Get-DbaPbmCondition
• Get-DbaPbmObjectSet
• Get-DbaPbmPolicy
• Get-DbaPbmStore

Also, Get-DbaTrigger has been removed and Get-DbaServerTrigger and Get-DbaDbTrigger have been added in its place. Unfortunately, there was no appropriate alias, so it was removed without an alias.

new website documentation coming soon

We'll soon be moving our documentation to a system that automatically generates docs from markdown! Expect to see that by next week. It'll be a great relief once released because keeping the website manually updated with all these changes is a near impossible task.

The command index will remain and be updated manually since it requires some categorization. That page also needs updating; I should have that done by Monday.

dbachecks

Recall that using old command names will show big ol yellow warnings and dbachecks uses a number of the renamed commands. So, dbachecks will be updated today, too, a couple hours after this email is sent out ✔

- Chrissy

It's been a while since the last release because we primarily focus on the PowerShell Gallery.

sleepygary is a nice and stable release that has a tons of bug fixes, over 400 commands, and newly beefed up snapshot commands. You can read more at dbatools.io/snapshots

Since the last release, we also created a bunch of awesome Extended Events commands. You can read more about the included these at dbatools.io/xevents. Chrissy and Gianluca also did a presentation at SQLBits about it. You can find the slides at sqlps.io/xecode.

Also included are a ton of perfmon commands (DbaPf), but those don't have an associated blog post yet.

Please see dbatools.io/picklerick. The most important change here is that we fixed our SQL connection pooling.

Please read more on our blog

Please see dbatools.io/realfakedoors for more info.

New Commands

Of the 23 new commands in this release, one command, Get-DbaSqlBuildReference, fulfills a need that I've seen talked about in the community for quite some time. Major Contributor Simone Bizzotto parsed a ton of SQL Server build information, created a JSON database then coded Get-DbaSqlBuildReference to parse it. We'll also be placing the data on sqlcollaborative.github.io once we get that going. Thank you, Simone!

• Get-DbaLocaleSetting
  Gets the Locale settings on one or more computers.

• Get-DbaNetworkActivity
  Gets the Current traffic on every Network Interface on a computer.

• Get-DbaQueryExecutionTime
  Displays Stored Procedures and Ad hoc queries with the highest execution times. Works on SQL Server 2008 and above.

• Get-DbaSqlBuildReference
  Returns info about the specific build of a SQL instance, including the SP, the CU and the reference KB, wherever possible.

• Get-DbaTempdbUsage
  Gets Tempdb usage for running queries.

• Find-DbaDatabaseGrowthEvent
  Finds any database AutoGrow events in the Default Trace.

• New-DbaSsisCatalog
  Enables the SSIS Catalog on a SQL Server 2012+

• Remove-DbaDatabase
  Drops a database, hopefully even the really stuck ones.

• Test-DbaIdentityUsage
  Displays information relating to IDENTITY seed usage. Works on SQL Server 2008 and above.

• Test-DbaLinkedServerConnection
  Test all linked servers from the sql servers passed.

• Read-DbaTransactionLog
  Reads the live Transaction log from specied SQL Server Database

General commands

Based on the popularity of Get-DbaDatabase and a general agreement within the team, 1.0 will include a bunch of basic Gets and Sets within the module that return SMO objects. Here's the first batch!

• Get-DbaAgentJob
  Gets SQL Agent Job information for each instance(s) of SQL Server.

• Get-DbaAgentJobOutputFile
  Returns the Output File for each step of one or many agent job with the Job Names provided dynamically if required for one or more SQL Instances.

• Get-DbaBackupDevice
  Gets SQL Backup Device information for each instance(s) of SQL Server.

• Get-DbaCredential
  Gets SQL Credential information for each instance(s) of SQL Server.

• Get-DbaCustomError
  Gets SQL Custom Error Message information for each instance(s) of SQL Server.

• Get-DbaDatabaseAssembly
  Gets SQL Database Assembly information for each instance(s) of SQL Server.

• Get-DbaDatabaseEncryption
  Returns a summary of encryption used on databases based to it. Shows if a database has Transparent Data encryption, any certificates, asymmetric keys or symmetric keys with details for each.

• Get-DbaJobCategory
  Gets SQL Agent Job Category information for each instance(s) of SQL Server.

• Get-DbaServerAuditSpecification
  Gets SQL Security Audit Specification information for each instance(s) of SQL Server.

• Get-DbaTable
  Shows table information around table row and data sizes and if it has any table type information.

• Set-DbaAgentJobOutputFile
  Sets the OutPut File for a step of an agent job with the Job Names and steps provided dynamically if required.

• Set-DbaSpConfigure
  This function changes the configured value for sp_configure settings. If the setting is dynamic this setting will be used, otherwise the user will be warned that a restart of SQL is required.

This is really long and I'm sorry, I'm lazy 👎 Please see dbatools.io/pieceoftoast.

This release is all about Backup and Restore - but mostly about Restore - you should see that 🏆 . Read more at dbatools.io/snowball.

New Commands

• Backup-DbaDatabase
• Get-DbaDatabase
• Restore-DbaDatabase

(many others that will be featured in an upcoming release)

This release is all about Service Principal Names. Read more at dbatools.io/schwifty

New Commands

• Get-DbaSpn
• Set-DbaSpn
• Test-DbaSpn
• Remove-DbaSpn

New Commands

• Export-SqlUser
  Exports SQL Server Database Users creation script with all database permissions (database and object level) to a T-SQL file. This is a function that is not available in SQL Server Management Studio.
• Find-DbaAgentJob
  Finds agent job(s) that fit certain search criteria.
• Find-DbaDatabase
  Search SQL Server instances for database that have either the same name, owner or service broker guid.
• Find-DbaStoredProcedure
  Returns all stored procedures that contain a specific string or regex pattern.
• Get-DbaAgentAlert
  Return all SQL Agent alerts on a SQL Server Agent.
• Get-DbaAgentOperator
  Returns all SQL Agent operators on a SQL Server Agent.
• Get-DbaDatabaseState
  Gets some common “states” on databases like Read-Write (READ_ONLY or READ_WRITE), Status (ONLINE, OFFLINE, EMERGENCY) and Access options (SINGLE_USER, RESTRICTED_USER, MULTI_USER)
• Get-DbaHelpIndex
  This function will return detailed information on indexes (and optionally statistics) for all indexes in a database, or a given index should one be passed along.
• Get-DbaMemoryUsage
  Get amount of memory in use by all SQL Server components and instances. SSAS and SSIS are included.
• Get-DbaMsdtc
  Displays information about the Distributed Transactioon Coordinator (MSDTC) on a server.
• Get-DbaPageFileSetting
  Returns detailed information about the Windows page file.
• Get-DbaTrigger
  Get all existing triggers at instance and database level.
• Get-DbaUptime
  Returns the uptime of the SQL Server instance. If you want you can get the value for the hosting windows server too.
• Get-DbaXEventsSession
  Retrieves a list of Extended Events Sessions.
• Invoke-SqlCmd2
  Runs a T-SQL script. But only captures the first selected result set, such as the output of PRINT statements when Verbose parameter is specified.A big plus when comparing with native Invoke-SqlCmd cmdlet is that this one supports parameterized queries.
• New-DbaDatabaseSnapshot
  Do you need to create a database snapshot? This command will do it without hassles.
• Out-DbaDataTable
  Will creates a DataTable based on an objects properties.
  This allows you to easily write to SQL Server tables.
• Rename-DbaLogin
  It can be a pain to update all of the mappings for a specific user when you rename a SQL login this does it for you, will rename login and database mapping for a specified login.
• Restore-DbaFromDatabaseSnapshot
  Restores the database from the snapshot, discarding every modification made to the database.
• Set-DbaDatabaseState
  Want to change a database state? With this command you can chose between ReadOnly, ReadWrite, Online, Offline, Emergency, plus a special “Detached”, SingleUser, RestrictedUser, MultiUser.
• Test-DbaOptimizeForAdHoc
  Displays information relating to SQL Server Optimize for AdHoc Workloads setting.
• Write-DbaDataTable
  Quickly and efficiently writes data to a SQL Server Table using a .NET DataTable to a SQL Server table using SQL Bulk Copy.

Improvements

• Test-SqlMigrationConstraint
  Added support for SQL Server 2016 SP1 outstanding news! Almost all editions supports what was, until now, Enterprise features only. (#512)
• Get-SqlServerKey
  Added support for SQL Server 2016 (#610)
• Copy-SqlLogin
  If you try to transfer a local account (non-domain) you get a "Skipped", because is a local machine account. (#275)

Notable bug fixes

• Copy-SqlDatabase
  Fixed an issue where -WithReplace was not working properly (#601)
• Expand-SqlTLogResponsibly
  Fixed an issue where Expand-SqlTLogResponsibly grew TLog all at once. (#503)
• Remove-SqlOrphanUser
  Fixed an issue where Remove-SqlOrphanUser fails when the user was the owner of one or more schemas. (#296)
• Remove-SqlDatabaseSafely
  Remove-SqlDatabaseSafely failed to perform final drop w/ no additional information. (#238)