Load data from PowerShell

• harby_sandhu

  SSC Enthusiast

  Points: 116

  More actions

  December 8, 2010 at 8:58 am

  #230178

  Dear all,

  I am new to PowerShell and SQLServer2008.

  What I am trying to do is, query SQLServer2008 via PowerShell to get the database sizes and then load that data into a SQLServer2008 table.

  I have managed to drop the output of the database sizes down to a .csv and can load that file, but I was looking for a more efficient way, where by I could pipe the output straight into a load command

  My code so far:

  #==========================================================================

  # Function: testfunction

  # Description: Test function

  #==========================================================================

  function testfunction()

  {

  $outFileName = "c:\temp\outputfile1.csv"

  $connectionString = "Server=TEST_SERVER;Integrated Security='SSPI'"

  $srv = new-object System.Data.SqlClient.SqlConnection $connectionString

  ##This loads the SMO DLLs.

  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

  $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $srv

  $dt = new-object “System.Data.DataTable”

  $SMOserver.Databases | select-objectName, `

  Size,DataSpaceUsage, `

  IndexSpaceUsage, `

  SpaceAvailable, `

  LastBackupDate,`

  LastLogBackupDate `

  | export-CSV -NoTypeInformation $outFileName

  invoke-item $outFileName

  }

• Mike - CI

  SSCarpal Tunnel

  Points: 4146

  More actions

  January 26, 2011 at 12:09 pm

  #1278704

  Using your code as the base, the easiest way that I can think of to do this is using the Out-Datatable function that Chad Miller wrote and is out at the following link.

  http://poshcode.org/2119

  Once you have that function loaded you can easily create your table in this sort of format

  create table spaceusage

  (

  Name varchar(500),

  Size float,

  DataSpaceUsage bigint,

  IndexSpaceUsage bigint,

  SpaceAvailable bigint,

  LastBackupDate varchar(40),

  LastLogBackupDate varchar(40)

  )

  And then you can run the following from within powershell to load this into your spaceusage table that you just created

  $connectionString = "Server=yourserver;Integrated Security='SSPI'"

  $srv = new-object System.Data.SqlClient.SqlConnection $connectionString

  ##This loads the SMO DLLs.

  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

  $SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $srv

  $dt = new-object “System.Data.DataTable”

  $dt = $SMOserver.Databases | select-objectName, `

  Size,DataSpaceUsage, `

  IndexSpaceUsage, `

  SpaceAvailable, `

  LastBackupDate, `

  LastLogBackupDate `

  | out-DataTable

  $connectionString = "Data Source=yourserver;Integrated Security=true;Initial Catalog=yourdb;";

  $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString;

  $bulkCopy.DestinationTableName = "spaceusage";

  $bulkCopy.WriteToServer($dt);

  I removed your create function piece but it would work if you wanted it to be via function as well.

  This will get it loaded in a very simple manner. Note that the reason I made the two datetime values as character is because on my test server that I ran this they do not have log backups, and consequently the value 01/01/0001 does not fit into Datetime. If yours are all good, you can make both of those datetime.

  Again for this to work you have to have the Out-Datatable function loaded in the link mentioned above but once you do it is as simple as that and you can gather this data for many servers by putting this in a loop. It makes things very easy, IMO.