Powershell Module for SQL Server: PSSQLLib

Page

PowerShell is very good for retrieving information from SQL Server. The problem is that most of the time we have to create the scripts every time or load them from a previous file. Because I hate to do things twice I created a PowerShell module for SQL Server which enables me to retrieve information from my instances with a few simple commands and is called PSSQLLib.

Is uses the SQL Server SMO for retrieving information from SQL Server and uses the WMI to get information from the host.

The module is definitely not done and additions can be made. If you have any functionality that could be included please send it to me and I’ll include it in the library.

Features

The library has the following features:

  • Export database objects
  • Export SQL Server objects
  • Get the host hard-disk information
  • Get the host hardware
  • Get the hosts SQL Server services
  • Get the host operating system information
  • Get the host up-time
  • Get the SQL Server Agent jobs
  • Get the SQL Server backups
  • Get the SQL Server configuration settings
  • Get the SQL Server databases
  • Get the SQL Server database files
  • Get the SQL Server database privileges
  • Get the SQL Server database users
  • Get the SQL Server disk latencies
  • Get the SQL Server instance settings
  • Get the SQL Server privileges
  • Get the SQL Server up-time

Requirements

The module needs at least PowerShell version 3.0 installed.

Additionally you need the SQL Server SMO installed. If you’ve got the SQL Server Management Studio installed on the machine your running this module from, then you don’t have to do anything.
If it’s not installed, you can download and install the SQL Server Feature pack. The version for 2014 can be found here.

Installation

To install the module open a PowerShell command window and enter the following script:

Alternative installation method

Alternatively you can download the module from here.

Unzip the file.

Make a directory (if not already present) named “PSSQLLib” in one of the following standard PowerShell Module directories:

  • $Home\Documents\WindowsPowerShell\Modules (%UserProfile%\Documents\WindowsPowerShell\Modules)
  • $Env:ProgramFiles\WindowsPowerShell\Modules (%ProgramFiles%\ WindowsPowerShell\Modules)
  • $Systemroot\System32\WindowsPowerShell\v1.0\Modules (%systemroot%\System32\ WindowsPowerShell\v1.0\Modules)

Place both the “psd1” and “psm1” files in the module directory created earlier.

Execute the following command in a PowerShell command screen:

Import-Module PSSQLLib

To check if the module is imported correctly execute the following command:

Get-Command -Module PSSQLLib or Get-Module -Name PSSQLLib

If you see a list with the functions than the module is installed successfully. If you see nothing happening something has gone wrong.

Functions and examples

The following functions make all this possible:

Get-HostHarddisk

This function will retrieve the disk, volume name, free space in MB, size in MB and the percentage used. An example of the result:

PSSQLLIB Get-HostHarddisk example

Get-HostHardware

This function will retrieve hardware information from the host like the number of logical processors, physical memory, the model etc. An example of the result:

PSSQLLIB Get-HostHardware example

Get-HostOperatingSystem

This function retrieves information of the operating system of the host like the architecture, the version, the free physical memory, the free space in the paging file etc. An example of the result:

PSSQLLIB Get-HostOperatingSystem example

Get-SQLConfiguration

This function retrieves all the configurations of the SQL Server instance with their possible  values and the current configured and running values. An example of the result:

PSSQLLIB Get-SQLConfiguration example

Get-SQLDatabases

This function get information of the present databases in the SQL Server instance. It get’s a lot of information about the databases which mostly isn’t showed i nthe example below.

PSSQLLIB Get-SQLDatabases example

Get-SQLDatabaseFiles

This function retrieves all the data and log files for all the databases with their size and location.

PSSQLLIB_Get-SQLDatabaseFiles_example

Get-SQLDatabaseUsers

This function retrieves all the logins who are user in a database.

PSSQLLIB Get-SQLDatabaseUsers example

Get-SQLDatabasePrivileges

This function retrieves all the database users with their privileges summed up for all the database roles assigned to the user.

PSSQLLIB Get-SQLDatabasePrivileges example

Get-SQLInstanceSettings

This function retrieves valuable information about the settings of the instance like the default file and log directory, the collation etc.

PSSQLLIB Get-SQLInstanceSettings example

Get-SQLAgentJobs

This function retrieves all the jobs that are present in the SQL Server Agent.

PSSQLLIB Get-SQLAgentJobs example

Get-SQLServerPrivileges

This function is similar to the database privileges function but looks up all the server roles for each login.

PSSQLLIB Get-SQLServerPrivileges example

One thought on “Powershell Module for SQL Server: PSSQLLib

Leave a Reply