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.
This command, created by Cláudio Silva helps DBAs automate Virtual Log File (VLF) management. Claudio covers this command in depth in a guest post on my blog.
- Performs a DBCC CHECKDB
- Backs up the database WITH CHECKSUM
- Restores with VERIFY ONLY on the source
- Creates and Agent job to easily restore from that backup
- Drops the database
- The Agent Job restores the database
- Performs a DBCC CHECKDB and drops the database for a final time
Simply amazing! You can read more about Remove-SqlDatabaseSafely on Rob’s blog.
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.
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
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-SqlDatabaseList.
This is v0.1 of Show-SqlWhoIsActive. We’ll be working on formatting options and auto-population soon.
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?
Gets list of SQL Server names stored in SQL Server Central Management Server. Filtering by one or more server groups supported.
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.
Shows a list of databases in a GUI. Returns a simple string. Hitting cancel returns null.
Test your TempDb Configuration
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.
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.
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.
Commands that are coming soon
And we’ve got more planned!
Move database files from disk to disk
Gets information about space avaialable inside the database
Routine to test your backups
Rotuine to restore databases from directories (think the way that Ola Hallengren’s outputs his by default)
Write the results of Show-SqlSpWhoIsActive to table!
Auto-installer for sp_WhoIsActive
Like Reset-SqlAdmin, this is a command that won’t be used often, but when it is, its a lifesaver!
Returns the network name of the active node in a cluster
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.