Use PowerShell to Collect Server Data and Write to SQL


Summary: Use Windows PowerShell to collect server data and automatically store that information in a Microsoft SQL Server.


Hey, Scripting Guy! QuestionHey, Scripting Guy! How is Windows PowerShell usage by database professionals different from the way that other IT Pros use Windows PowerShell?


-- MC


Hey, Scripting Guy! AnswerHello MC, Microsoft Scripting Guy Ed Wilson here. Next week, Nov 8-11, 2010, is the annual SQL PASS Summit in Seattle. In honor of this event, we will have guest bloggers from the SQL side of life. Today we will begin with Chad Miller.


Chad Miller(Blog|Twitter) is a SQL Server DBA and Senior Manager of Database Administration at Raymond James Financial. In his spare time, he is the Project Coordinator/Developer of the Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at SQL Saturdays and Code Camps.

Take it away Chad!


We are data people and we believe data should exist in databases. We spend a good part of our day writing Transact-SQL scripts to query and load data from other sources. Therefore, not surprisingly when we use Windows PowerShell we want to run a command, capture the output and then store the data in a database. After the data is in a database, we can use Transact-SQL to do additional reporting and analysis. We might even use SQL Server Reporting Services to provide Web-based access to the data.

Extract Data

SQL Server 2008 and 2008 R2 provide the Invoke-SqlCmd cmdlet, but only on computers where the sqlps (the SQL Server mini-shell) is installed. As an alternative solution, you can implement your own function which does not require loading external snap-ins. I have copied the invoke-sqlcmd2 function to the Scripting Guys Script Repository. Save the code as invoke-sqlcmd2.ps1 and then dot source our new function into your Windows PowerShell console by using the following command:

. ./invoke-sqlcmd2.ps1

The following example dot sources the invoke-sqlcmd2.ps1 script that contains the invoke-sqlcmd2 function, connects to the pubs database on a SQL server named SQL1 and runs a basic Transact-SQL query. The last command is a single command, but has wrapped to the third line in the output.

PS C:\> . C:\data\ScriptingGuys\2010\HSG_11_1_10\invoke-Sqlcmd2.ps1

PS C:\> Invoke-Sqlcmd2 -ServerInstance sql1 -Database pubs -Query "Select * from auth




au_id    : 172-32-1176

au_lname : White

au_fname : Johnson

phone    : 408 496-7223

address  : 10932 Bigge Rd.

city     : Menlo Park

state    : CA

zip      : 94025

contract : True


<...OUTPUT Truncated>


This example reads a file that contains T-SQL statements, runs the file, and writes the output to another file.

Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"


This example uses the Windows PowerShell -Verbose parameter to return the message output of the PRINT command.

Invoke-Sqlcmd2  -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose

VERBOSE: hello world


Getting a Server List

When you have to run a Windows PowerShell command across multiple servers, you will frequently see examples in which the list of servers are stored in a text file and read by using the Get-Content cmdlet. However, most database professionals maintain a list of SQL Servers they manage in either a table they create or they may use Central Management Server (CMS) introduced in SQL Server 2008.  Instead of using a text file, for database professionals, it makes more sense to read a SQL table. Let's take a look at an example. For the purposes of this demonstration we'll create a table. However, you could just as easily substitute the msdb.dbo.sysmanagement_shared_registered_servers view from your CMS server.

From SQL Server Management Studio, create a SQL table.

CREATE TABLE server_instance

(server_name varchar(255) NOT NULL);

Next populate the table with a list of SQL Servers:

INSERT server_instance VALUES('Z001');

INSERT server_instance VALUES('Z002\SQ2K8');

INSERT server_instance VALUES('Z003\R2');


With our server_instance table populated, we can use our Invoke-SqlCmd2 function to retrieve a list of servers and then call Windows PowerShell command for each. The following example retrieves the version information.

Invoke-sqlcmd2 -ServerInstance "Z003\R2" -Database dbautility -Query "Select server_name FROM server_instance" | foreach-object {Invoke-SqlCmd2 -ServerInstance $_.server_name -Database master -Query "SELECT @@version"}


Extract and Load data

A common task for a database professional is collecting and loading data from multiple servers into central utility database. Using the invoke-sqlcmd2 function, we can extract data from a SQL Server data source, but to load data we have to introduce a new function called Write-DataTable. I have uploaded the Write-DataTable Windows PowerShell function to the Scripting Guys Script Repository. Save the Windows PowerShell function from the Script Repository as write-datatable.ps1 and then dot source our new function into the Windows PowerShell console by using the following command.

. ./write-datatable.ps1

The Write-DataTable function uses the .NET Data.SqlClient.SqlBulkCopy class to load an in-memory DataTable or DataRow array into a SQL Server table. This works out well because the cmdlet invoke-sqlcmd or our function invoke-sqlcmd2 returns a datatable object. Let us examine an example database named space usage collection for all databases in your environment to use in forecasting growth.

You have to create a SQL Server table using SQL Server Management Studio. To do this, run the following Transact-SQL script:

CREATE TABLE [dbo].[db_space](

      [server_name] [varchar](128) NOT NULL,

      [dbname] [varchar](128) NOT NULL,

      [physical_name] [varchar](260) NOT NULL,

      [dt] [datetime] NOT NULL,

      [file_group_name] [varchar](128) NOT NULL,

      [size_mb] [int] NULL,

      [free_mb] [int] NULL,



      [server_name] ASC,

      [dbname] ASC,

      [physical_name] ASC,

      [dt] ASC




The T script and management studio are seen in the following figure.



Next we'll use a Transact-SQL query to collect the server name, database name and file information. We'll save the query to a plain old .sql file that is named get-dbspace.sql. This is the same kind of SQL script that you would execute in SQL Server Management Studio. However, we will call the script from the Invoke-SqlCmd2 function:

$dt = invoke-sqlcmd2 -ServerInstance "Z003\R2" -Database "master" -InputFile ./get-dbspace.sql  -As 'DataTable'


Examining the type information about $dt variable we can see the type is of a DataTable as shown in the following figure.



To load the DataTable into our SQL Server table we'll call the Write-DataTable function:

Write-DataTable -ServerInstance "Z003\R2" -Database "dbutility" -TableName "db_space" -Data $dt


Using invoke-sqlcmd2 and piping the output to Out-GridView we can see the data has in fact been loaded.

invoke-sqlcmd2 -ServerInstance "Z003\R2" -Database "dbutility" -Query "SELECT * FROM db_space" |  Out-GridView

The results from this command are shown in the figure below.


Extract, Transform, and Load Data

As we've seen, by using a couple of simple functions - invoke-sqlcmd2 and write-datatable - we can easily load data from any SQL Server data source, but what about any Windows PowerShell command? As an example, we want to collect disk space utilization by using Get-WMIObject Win32_LogicalDisk across a group of SQL Servers into a central database for reporting trending. If we can convert the output of our WMI call into a DataTable then we can use our Write-DataTable function. Using a function called Out-DataTable adapted from a script by Marc van Orsouw (Blog|Twitter) we can do just that. I saved the modified script to the Scripting Guys Script Repository.

Save the following code as Out-DataTable.ps1 and source our new function

. ./ Out-DataTable.ps1


To load convert the output of a WMI call to a DataTable, we'll pipe to our newly created out-datatable function and assign the output to a $dt.

$dt = Get-WmiObject Win32_LogicalDisk -filter "DriveType=3" | Select @{n='UsageDT';e={get-date -Format "yyyy-MM-dd"}}, '

SystemName, DeviceID, VolumeName, '

@{n='Size';e={$([math]::round(($_.Size/1GB),2))}}, @{n='FreeSpace';e={$([math]::round(($_.FreeSpace/1GB),2))}} | out-datatable


Having assigned the output to a datatable you can call the write-datatable to load the collected data into a SQL Server table. This makes out-datatable very powerful in that the output of any Windows PowerShell command can be easily loaded into a SQL Server table. Using write-datatable requires a SQL Server destination table to exist, but instead of manually creating a SQL Server table, we'll use a new function called Add-SqlTable. The Add-SqlTable.ps1 script is also uploaded to the Scripting Guys Script Repository. Save the code as Add-SqlTable.ps1 and source the new function.

The following command will create a new empty SQL Server table named diskspace based on the structure of our DataTable variable $dt:

Add-SqlTable -ServerInstance "Z003\R2" -Database dbutility -TableName diskspace -DataTable $dt


Note: Using this technique to create a SQL Server table is not as precise as manually creating a table as certain elements including defining primary keys and data types are not handled. Add-SqlTable works best for a quick data dump of Windows PowerShell data to a SQL Server table.

Finally, with the destination SQL Server table created, we can call our write-datatable function to load the collected data:

Write-DataTable -ServerInstance "Z003\R2" -Database "dbutility" -TableName "diskspace" -Data $dt


Using invoke-sqlcmd2 we can see the data was loaded into the SQL Server table:

invoke-sqlcmd2 -ServerInstance "Z003\R2" -Database "dbutility" -Query "SELECT * FROM diskspace" | Out-GridView




This post demonstrated how to query and load the output of any Windows PowerShell command into a SQL Server table. The functions invoke-sqlcmd2, write-datatable, out-datatable and add-sqltable can be used as building blocks for many of your Windows PowerShell-based data loading needs.

MC, that is all there is to using Windows PowerShell and SQL Server. SQL week will continue tomorrow when SQL guest blogger Aaron Nelson will talk about how to work with SQL snap-ins.  Thank you Chad, for sharing your time and knowledge with us.


I invite you to follow me on Twitter or Facebook. If you have any questions, send email to me at or post them on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.


Ed Wilson, Microsoft Scripting Guy

Comments (27)

Cancel reply

  1. Anonymous says:


    See discussion under Out-Datatable for how to handle text files:…/4208a159-a52e-4b99-83d4-8048468d29dd

  2. mbourgon says:

    JV – cool!  Now I'm getting somewhere.  Is there an easy way to say "copy field + datatable1 into datatable2"?  

    I'm trying to take the results of an SP and add the servername to it, before writing to a table. All help appreciated!

  3. mbourgon says:

    JV – sorry, how do you do that in powershell?  (should have been more clear).  

    Say I did this:

    $dt = invoke-sqlcmd2 -serverinstance myservername -query "exec sp_who" -As 'Datatable'

    I now have a datatable.  I want to copy the details of $dt into a new DataTable (so that I can add the ServerName field), then write using write-datatable.  But since I'm multithreading it, I need to do this within the PS script; I can't use

  4. Anonymous says:


    You're welcome. For simple data loads PowerShell rocks!

  5. Anonymous says:

    I want to run my PS script that dumps all details of all WEBs, Lists, Libraries, Folders, Documents, ITems and Attachments from SharePoint and send that output to a SQL Server database table. Q: Can do? Q: Am I dreaming too big? Imquiring minds need to know jeannine.menger@recommind    (dot)    com

  6. mbourgon says:

    (grr – stupid post button)

    JV – sorry, how do you do that in powershell?  (should have been more clear).  

    Say I did this:

    $dt = invoke-sqlcmd2 -serverinstance myservername -query "exec sp_who" -As 'Datatable'

    I now have a datatable.  I want to copy the details of $dt into a new DataTable (so that I can add the ServerName field), then write using write-datatable.  But since I'm multithreading it, I need to do this within the PS script; I can't use t-sql or interim tables for that part.

  7. mbourgon says:

    Say I run a query using invoke-sqlcmd2, then need to add a field to the datatable (preferably at the beginning of the row) prior to using write-datatable.  How do I do this?  I've been futzing with add-member without any success.  Any help greatly appreciated!

  8. mbourgon says:

    Answering my own question, sorta – asked on Stackoverflow and Chad Miller (the guy who wrote the above code) answered.  

    Two ways to do it, provided you ran something like this:

    $dt = invoke-sqlcmd2 -serverinstance $_.server -query "exec master.dbo.sp_who" -As 'Datatable'

    and you want to add the Servername (yes, sp_who returns the server name, but I wanted an SP everyone had)


    $Col =  new-object Data.DataColumn

    $Col.ColumnName = "ServerName"


    $dt | %{$_.ServerName = $server}

    Or (abbreviated version):


    $dt | %{$_.ServerName = $server}  #in my main foreach I say $server = $_.server

  9. Anonymous says:


    Thanks for the feedback. I've updated the out-datatable function to include setting the datatype property.

  10. Anonymous says:

    Good stuff!  This helped me a lot and worked perfectly!  Thanks!

  11. j_yao says:

    I tried to run the ./Invoke-SqlCmd2.ps1 in the PS command line window, and it "succeeded" without any error.

    However, when I try to run the following command

    Invoke-Sqlcmd2 -ServerInstance "MyServer" -Query "print getdate()"

    I will get error saying

    The term "Invoke-Sqlcmd2" is not recognized as the name of a cmdlet, function, script file or orperable program.

    However, if I load the Invoke-SqlCmd2.ps1 into PS IDE environment, and then run it, and after that if I run the same command, i.e. Invoke-SqlCmd2 -ServerInstance "MyServer" -Query "print getdate()", inside the PS IDE environment, I can get the result, i.e no error at all.

    Any explanation is greatly appreciated.

  12. j_yao says:

    I know why now. It is caused by not doing the 'dot source' in powershell term.

  13. inanutshell says:

    I'm using get-content on a text file, and then using the select object on those results.  Finally I'm piping that to out-datatable, but get nothing but the length column.  Since I guess what is getting passed is a string array the actual string value is not grabbed from $object.PsObject.get_properties() in the out-datatable definition.  Is this by design? Does out-datatable not work with string arrays?

  14. JV says:


    You can't a a column to the beginning of a table without dropping and recreating the table.

  15. JV says:


    insert into datatablea2 select 'myserver as ServreName, * from datatable1

  16. mbourgon says:

    wheresjeannine, yes, that's definitely possible.  I've cobbled together Chad's code into what I've dubbed the Extensible Powershell Repository (EPR).  (…/servers-extensible-powershell.html).  

    But for yours, all you should have to do is:

    #command is your script filename

    # dot-source the write-datatable.  This assumes the table already exists; otherwise use add-sqltable to create

    # fill out the variables with your info, or just hard-code

    $quer = invoke-expression $command | out-datatable

    Write-DataTable -ServerInstance $server_repository -Database $database_repository -TableName $tablename -Data $quer

  17. psnew1 says:

    Hello is this possible

    $quer=Invoke-sqlcmd2 -ServerInstance "test" -Database dba -Query "Select InstanceName FROM SQLInstances1" | foreach-object {Invoke-SqlCmd2 -ServerInstance $_.server_name -Database master -Query "SELECT @@version"}

    Add-SqlTable -ServerInstance "test" -Database dba -TableName version1 -DataTable $dt

    i get an error as below, .my goal is to use a query and load data to a table. from a list of servers in a table.

    Invoke-Sqlcmd2 : Cannot bind argument to parameter 'ServerInstance' because it is an empty string.

    At line:1 char:165

  18. psnew1 says:

    Never mind i figured it out

    modified another method to get it to work.

    $serverlist =Invoke-sqlcmd2 -ServerInstance "test" -Database dba -Query "TRUNCATE TABLE db_space;Select InstanceName FROM SQLInstances1"

    $serverlist|Split-Job {%{

    #because the scope is separate in here, you need to re-dot-source and reinit your parameters.

       . D:sql_toolsinvoke-sqlcmd2.ps1;

       . D:sql_toolssplit-job.ps1;

       . D:sql_toolswrite-datatable.ps1;

       $server2 = $_.InstanceName

       write-host $server2

        $quer = Invoke-SqlCmd2 -ServerInstance  $server2 -Database "master" -InputFile D:sql_toolsget-dbspace.sql  -As 'DataTable'

    Write-DataTable -ServerInstance "test" -Database "dba" -TableName "db_space" -Data $quer


  19. Josh says:

    I am curious of how you would manipulate this into a SQL Agent job so it can run on a scheduled basis?

  20. Satheesh Kumar.k says:

    Hi i am using the following code. Import-Module -Name ‘D:satheeshWindows Event Loginvoke-sqlcmd2.ps1’ Import-Module -Name ‘D:satheeshWindows Event Logwrite-datatable.ps1’ Import-Module -Name ‘D:satheeshWindows Event LogAdd-SqlTable.ps1’ Import-Module
    -Name ‘D:satheeshWindows Event LogOut-DataTable.ps1’ $dt = Get-WinEvent -ComputerName ‘’ -LogName ‘System’ -MaxEvents 10 | out-datatable Add-SqlTable -ServerInstance "XXXXX" -Database "YYYY" -TableName tTestEventLog -DataTable $dt Write-DataTable
    -ServerInstance "XXXXX" -Database "YYYY" -TableName "tTestEventLog" -Data $dt when i execute this i am getting error as ( I am using SQL Server 2012) do i need to change anything in Add-SQLTable.ps1 file. kindly help me to fix this issue. Add-SqlTable : Cannot
    read property AnsiPaddingEnabled.This property is not available on SQL Server 7.0. At D:satheeshWindows Event LogAuditCheckTest.ps1:8 char:1 + Add-SqlTable -ServerInstance ",56554" -Database DB_DIRSVC_M … because of this i could
    not get the data table structure to create table. kindly help me to fix this issue.

  21. Pipita says:

    Hi great scripts! with the invoke2 i can export a file to a disk. But how about importing it?

    I’m trying to get a file from the server and use your write-datable function.

    $d = Get-Content C:_dm-rmInterfacesinicialOINV3.dat
    Write-DataTable -ServerInstance localhost -Database DM-RM-LATAM -TableName dbo.OINV -Data $d

    My guess is that i’m failing to to assigned the file to a datatable i can call the write function.


  22. Geek says:

    My DataTable has a PrimaryKey and the combined script i.e. Add-SqlTable and Write-DataTable does not set the PrimaryKey in the newly created SQL table; is there a fix?

  23. JV says:

    @geek – it is here where the table is created:


  24. Tier1Support says:

    When loading data into SQL from powershell, how do we avoid duplicate entries? Typically in SQL I could do a "not exists" in the where statement OR in SSIS a lookup no match. Not immediately seeeing how to do this in ps…

  25. richtheh says:

    I am building and testing a simple script and have run into a problem.

    $files = Get-ChildItem -path c:rich -Filter *xlsx | Select-Object -ExpandProperty name
    foreach ($f in $files)
    $xl = New-Object -ComObject "Excel.Application"
    $xl.visible = $true
    $wb = $"c:richbook2.xlsx")
    $dt = $wb.sheets.item(1) | Out-DataTable

    if i run $dt | gm, I am getting "datarow" rather than "datatable"

    Function Out-DataTable is v1.8
    My excel file is just to test the collection of datatypes for each column. I have text, decimal, int and datetime columns.

    Any ideas why? Let me know if additional information is needed.

  26. Richard says:

    If I were to use the write to table function as a powershell logon script, at minimum the script would run-as domain user. In the SQL DB, would I add domain users with write access?

Skip to main content