SQL DBTools Module

SQL DBTools Module

Purpose: briefly describes the incredibly useful DBATools module and how to load it.

Background

The DBATools PowerShell module is a suite of functions that automates many database administration tasks (such as migrating databases and logins). The code is available on GitHUB at https://github.com/ctrlbold/dbatools.

Prerequisites

  1. PowerShell 3.0 or higher
  2. Microsoft.SqlServer.SMO and Microsoft.SqlServer.SMO.Extended assemblies loaded (click here – Importing the SQLPS module is the easiest option if you have SQL 2012 or SQL 2014 installed).
  3. Install-Module -Name SqlServer
    Add-Type -Path ‘C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll’
    Add-Type -Path ‘C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.SmoExtended.dll’

Loading DBATools Option 1

According to the online documentation, you can run this command and it will download, unzip, and load the latest version from GitHub

Invoke-Expression (Invoke-WebRequest  http://git.io/vn1hQ).Content

I have never been able to get this to function correctly as it errors on the unzipping portion. I would also not recommend it as it obviously requires internet connectivity and the download and unzip operations add to the load time.  It also means you may be pulling a more recently updated version of the code than is otherwise being used.

Loading DBATools Option 2

Download the attached zip file which contains the version downloaded on 9/29/2015. Unzip the contents to a folder. Run the following commands, updating the $path parameter to your unzip location.  You may also add them to your PowerShell Profile.

#Load DBA tools from assumed path
$path = “”
if ((Get-Command -Module dbatools).count -eq 0) { Import-Module “$path\dbatools.psd1” }
Get-Command -Module dbatools

This will enumerate the functions loaded from the module.  This options works fine, but it does not however load all of the items available (e.g. the SharedFunctions), which may be useful to you for your own PowerShell development. However, all enumerated functions will work.

Loading DBATools Option 3

Download the attached zip file which contains the version downloaded on 9/29/2015. Unzip the contents to a folder. Add the functions subfolder to your Autoload Folder. This option works fine and loads all the items available.  However, you may needlessly load items you won’t actually use.

Loading DBATools Option 4 – All users

Download the attached zip file which contains the version downloaded on 9/29/2015. Unzip the contents to the following folder: C:\Users\Public\Documents\WindowsPowerShell\Modules, creating as necessary. Edit the Common PowerShell ISE Profile to include the following code

#Load SQL Objects
Import-Module SQLPS -DisableNameChecking

# directory or directories where my scripts are stored

$psdir=@(“C:\Users\Public\Documents\WindowsPowerShell\Modules\dbatools-master\Functions”)

# load all ‘autoload’ scripts

foreach ($dir in $psdir)
{
Get-ChildItem “${dir}\*.ps1” | %{.$_}
}

#Change the directory back to a windows path so that default functions works
cd C:\

write-host “Custom All user PowerShell ISE profile loaded”

Notes:

  1. Use option 2 or 3 for loading the module on your personal machine
  2. Use option 4 for new SQL servers
  3. You may encounter an issue where windows has blocked the files. You can resolve this by right clicking the file, opening the properties, and clicking unblock (see below). Alternatively, you can run the following PowerShell command on the directory where the files reside (replacing <Folder> with your folder of course): Get-ChildItem <Folder> | Unblock-File

Leave a Reply