new best practices commands now available

So many SQL Server and PowerShell pros have joined the dbatools team and we’re producing well designed PowerShell commands like mad!

dbatools is not only intended to be a great migration tool, but also a toolset to help DBAs follow best practices. We like to think of these commands as fully automated Wizards that are executed from the command line instead of the GUI.

Best Practices Commands

Ever read a really great article about how to do something properly but then kinda forgot and you have to revisit each time? Well, we’ve codified some those practices for you, making them easy to both follow and remember.

  1. Performs a DBCC CHECKDB
  2. Backs up the database WITH CHECKSUM
  3. Restores with VERIFY ONLY on the source
  4. Creates and Agent job to easily restore from that backup
  5. Drops the database
  6. The Agent Job restores the database
  7. Performs a DBCC CHECKDB and drops the database for a final time

     Simply amazing! You can read more about Remove-DbaDatabaseSafely on Rob’s blog.

  • Set-DbaTempdbConfig
    These commands, created by Mike Fal, sets tempdb data and log files according to best practice calcluations. Mike writes more about this function on his blog.
  • Find-DbaDbUnusedIndex
    This command, created by Aaron Nelson, will help you to find unused indexes on a database or a list of databases. It also tells how much space you can save by dropping the index. For now only supported for CLUSTERED and NONCLUSTERED indexes.

Commands that make your life easier

  • Invoke-DbaWhoIsActive
    We wrote a PowerShell command to output results of Adam Machanic’s beloved sp_WhoIsActive to a GridView (default) or DataTable. If sp_WhoIsActive is not installed in the system, it will be downloaded and installed to a database you specify with either -Database or a database you select from Show-DbaDbList.

    This is v0.1 of Invoke-DbaWhoIsActive. We’ll be working on formatting options and auto-population soon.

  • Repair-DbaDbOrphanUser
    This command, created by Cláudio Silva, helps DBAs find and repairs orphaned users in one, multiple or all databases.

  • Remove-DbaDbOrphanUser
    Another homerun written by Cláudio Silva, which removes orphaned database users with no matching logins.

  • Get-DbaDiskSpace
    Displays Disk information for all local drives on one or more servers. Returns server name, name of disk, label of disk, total size, free size and percent free. Considering a new column in the future that designates if any SQL data/log files are stored on the returned disks. What do you think?

  • Get-DbaRegServer
    Gets list of SQL Server names stored in SQL Server Central Management Server. Filtering by one or more server groups supported.

  • Show-SqlServerFileSystem
    Similar to the remote file system popup you see when browsing a remote SQL Server in SQL Server Management Studio, this command allows you to traverse the remote SQL Server’s file structure.

    Show-SqlServerFileSystem uses SQL Management Objects to browse the directories and what you see is limited to the permissions of the account running the command. This will complement the upcoming Move-SqlDatabaseFile command.


  • Show-DbaDbList
    Shows a list of databases in a GUI. Returns a simple string. Hitting cancel returns null.


Test Commands

  • Test-DbaTempdbConfig
    Test your TempDb Configuration
  • Test-DbaNetworkLatency
    This function is intended to help measure SQL Server network latency by establishing a connection and making a simple query. This is a better alternative than ping because it actually creates the connection to the SQL Server, and times not only the entire routine, but also how long the actual queries take vs how long it takes to get the results.

  • Test-DbaPath
    Use this command to determine whether the SQL Server service account can “see” a directory. This command uses master.dbo.xp_fileexist under the hood and returns $true or $false.

  • Test-DbaMigrationConstraint
    Shows if you can migrate the database(s) between the servers. When you want to migrate from a higher edition to a lower one there are some features that can’t be used. This function will validate if you have any of this features in use and will report to you. The validation will be made ONLY on on SQL Server 2008 or higher using the ‘sys.dm_db_persisted_sku_features’ DMV.

Join us!

Some of these commands are in their infancy. Want to help make them better? Come join the coding party! We’re all hanging out on the SQL Server Community Slack in the #dbatools channel.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.