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.
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
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.
To install the module open a PowerShell command window and enter the following script:
(new-object Net.WebClient).DownloadString("https://github.com/sanderstad/PSSQLLib/raw/master/GetPSSQLLib.ps1") | iex
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:
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:
This function will retrieve the disk, volume name, free space in MB, size in MB and the percentage used. An example of the result:
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:
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:
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:
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.
This function retrieves all the data and log files for all the databases with their size and location.
This function retrieves all the logins who are user in a database.
This function retrieves all the database users with their privileges summed up for all the database roles assigned to the user.
This function retrieves valuable information about the settings of the instance like the default file and log directory, the collation etc.
This function retrieves all the jobs that are present in the SQL Server Agent.
This function is similar to the database privileges function but looks up all the server roles for each login.