{"id":460,"date":"2024-04-05T08:00:00","date_gmt":"2024-04-05T13:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=460"},"modified":"2024-04-04T20:49:13","modified_gmt":"2024-04-05T01:49:13","slug":"capturing-sql-server-inventory-with-powershell","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=460","title":{"rendered":"Capturing SQL Server Inventory with PowerShell"},"content":{"rendered":"

Introduction<\/h2>\n

Maintaining an accurate and comprehensive inventory of your SQL Server environment is crucial for effective database management, ensuring security, and meeting compliance requirements. With PowerShell, database administrators can automate the collection of detailed information about SQL Server instances and databases, streamlining the inventory process. This blog post introduces a PowerShell script designed to efficiently gather SQL Server inventory data, separating instance-level information from database-specific details into two distinct reports. We’ll also delve into a detailed explanation of the script to enhance understanding and provide insights into its structure and PowerShell techniques employed.<\/p>\n

PowerShell Script for SQL Server Inventory<\/h3>\n

The following PowerShell script collects information about SQL Server instances and databases, organizing the data into two separate outputs: one for static, instance-level details and another for dynamic, database-specific details.<\/p>\n

The Script<\/h4>\n
# Load the SqlServer module\nImport-Module -Name SqlServer\n\n# Define the SQL Server instances to inventory\n$servers = @('Instance1', 'Instance2')\n\n# Initialize arrays for collecting inventory data\n$instanceData = @()\n$databaseData = @()\n\nforeach ($server in $servers) {\n    # Collect and store instance-level information\n    $instanceQuery = \"SELECT SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('Edition') AS Edition;\"\n    $instanceInfo = Invoke-Sqlcmd -ServerInstance $server -Query $instanceQuery\n    $loginsQuery = \"SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE type IN ('S', 'U', 'G') AND is_disabled = 0;\"\n    $logins = Invoke-Sqlcmd -ServerInstance $server -Query $loginsQuery\n    $jobsQuery = \"USE msdb; SELECT name, enabled FROM sysjobs;\"\n    $jobs = Invoke-Sqlcmd -ServerInstance $server -Query $jobsQuery\n    \n    $instanceData += [PSCustomObject]@{\n        SQLInstance = $server\n        Version = $instanceInfo.Version\n        Edition = $instanceInfo.Edition\n        Logins = ($logins.name -join ', ')\n        Jobs = ($jobs.name -join ', ')\n    }\n\n    # Collect and store database-specific information\n    $databasesQuery = \"SELECT name, database_id, (SELECT SUM(size) * 8 \/ 1024 FROM sys.master_files WHERE database_id = db.database_id AND type_desc = 'ROWS') AS SizeMB FROM sys.databases db;\"\n    $databases = Invoke-Sqlcmd -ServerInstance $server -Query $databasesQuery\n    \n    foreach ($db in $databases) {\n        $databaseData += [PSCustomObject]@{\n            SQLInstance = $server\n            DatabaseName = $db.name\n            DatabaseId = $db.database_id\n            SizeMB = $db.SizeMB\n        }\n    }\n}\n\n# Export instance and database data to CSV files\n$instanceData | Export-Csv -Path 'C:\\temp\\SQLServerInstanceInventory.csv' -NoTypeInformation\n$databaseData | Export-Csv -Path 'C:\\temp\\SQLServerDatabaseInventory.csv' -NoTypeInformation\n\nWrite-Output \"SQL Server inventory has been saved to C:\\temp\\SQLServerInstanceInventory.csv and C:\\temp\\SQLServerDatabaseInventory.csv\"<\/code><\/pre>\n
This script efficiently separates the collection and reporting of SQL Server instance details from database-specific information, facilitating targeted analysis and management tasks.<\/p>\n
Detailed Script Explanation<\/h3>\n
Let’s break down the script into its core components to provide a deeper understanding of its functionality and the PowerShell techniques it utilizes.<\/p>\n
Importing the Required Module<\/h4>\n
Import-Module -Name SqlServer<\/code><\/pre>\n
The script starts by importing the SqlServer<\/code> module, which is crucial for executing SQL Server-specific cmdlets. This step ensures that all the necessary commands for SQL Server management are readily available in the PowerShell session, highlighting the importance of leveraging specialized modules to extend PowerShell’s capabilities for specific management tasks.<\/p>\n
Defining SQL Server Instances<\/h4>\n
$servers = @('Instance1', 'Instance2')<\/code><\/pre>\n
An array $servers<\/code> is defined to list the SQL Server instances targeted for inventory. This approach demonstrates how arrays can manage multiple items, allowing the script to perform operations on each instance efficiently. Arrays are a fundamental concept in PowerShell, enabling batch processing and iterative operations across a collection of objects.<\/p>\n
Initializing Arrays for Data Collection<\/h4>\n
$instanceData = @()\n$databaseData = @()<\/code><\/pre>\n
Two arrays are initialized to hold the collected data, separating instance-level information from database-specific details. This organization strategy uses arrays to dynamically collect and structure data during script execution, showcasing the flexibility of PowerShell in data aggregation and manipulation.<\/p>\n
Looping Through Each Server Instance<\/h4>\n
foreach ($server in $servers) {\n    ...\n}<\/code><\/pre>\n
A foreach<\/code> loop iterates over each server in the $servers<\/code> array, applying the enclosed commands to each instance. This illustrates the use of loops in PowerShell to automate repetitive tasks, allowing for scalable operations across multiple SQL Server instances with minimal code duplication.<\/p>\n
Collecting and Storing Data<\/h4>\n
# Instance-level information collection\n$instanceQuery = ...\n$instanceInfo = Invoke-Sqlcmd -ServerInstance $server -Query $instanceQuery\n\n# Database-specific information collection\n$databasesQuery = ...\n$databases = Invoke-Sqlcmd -ServerInstance $server -Query $databasesQuery<\/code><\/pre>\n
Data collection is performed using Invoke-Sqlcmd<\/code> to execute T-SQL queries against the SQL Server instances. This cmdlet bridges scripting with database management, enabling direct interactions with SQL Server from within PowerShell. It exemplifies the power of PowerShell in executing database operations and retrieving information for analysis and reporting.<\/p>\n
Creating Custom Objects for Data Aggregation<\/h4>\n
$instanceData += [PSCustomObject]@{\n    SQLInstance = $server\n    ...\n}<\/code><\/pre>\n
Custom objects are created to aggregate the collected data, demonstrating PowerShell’s ability to structure data in customizable formats. This method is particularly useful for organizing and representing data logically, catering to specific reporting and analysis needs.<\/p>\n
Exporting Data to CSV<\/h4>\n
$instanceData | Export-Csv -Path 'C:\\temp\\SQLServerInstanceInventory.csv' -NoTypeInformation<\/code><\/pre>\n
The final step exports the aggregated data to CSV files, utilizing the Export-Csv<\/code> cmdlet. This process illustrates PowerShell’s capability to not only collect and process data but also to export it in a format that is widely used and easily accessible for further analysis or reporting.<\/p>\n
Conclusion<\/h3>\n
This PowerShell script streamlines the process of collecting SQL Server inventory data by efficiently separating instance-level details from database-specific information. By breaking down the script, we provide insights into the PowerShell techniques used, enhancing understanding and demonstrating the script’s value as both a practical tool for SQL Server management and a learning resource for PowerShell scripting.<\/p>\n","protected":false},"excerpt":{"rendered":"
Maintaining an accurate and comprehensive inventory of your SQL Server environment is crucial for effective database management, ensuring security, and meeting compliance requirements. With PowerShell, database administrators can automate the collection of detailed information about SQL Server instances and databases, streamlining the inventory process. This blog post introduces a PowerShell script designed to efficiently gather SQL Server inventory data, separating instance-level information from database-specific details into two distinct reports.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[55,82,53,29,21],"tags":[62,111,57,110],"class_list":["post-460","post","type-post","status-publish","format-standard","hentry","category-automation","category-database-configuration","category-powershell","category-security","category-tutorial","tag-audit","tag-instance-management","tag-scripting","tag-sql-server-inventory"],"yoast_head":"\nStreamline SQL Server Inventory Management Using PowerShell<\/title>\n<meta name=\"description\" content=\"This blog offers a guide on using PowerShell to manage SQL Server inventory, focusing on a script that distinguishes between server and database information, with an explanation of script elements for improved clarity.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqltabletalk.com\/?p=460\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Streamline SQL Server Inventory Management Using PowerShell\" \/>\n<meta property=\"og:description\" content=\"This blog offers a guide on using PowerShell to manage SQL Server inventory, focusing on a script that distinguishes between server and database information, with an explanation of script elements for improved clarity.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqltabletalk.com\/?p=460\" \/>\n<meta property=\"og:site_name\" content=\"SQL Table Talk\" \/>\n<meta property=\"article:published_time\" content=\"2024-04-05T13:00:00+00:00\" \/>\n<meta name=\"author\" content=\"Stephen Planck\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Stephen Planck\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=460#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=460\"},\"author\":{\"name\":\"Stephen Planck\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"headline\":\"Capturing SQL Server Inventory with PowerShell\",\"datePublished\":\"2024-04-05T13:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=460\"},\"wordCount\":581,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"keywords\":[\"audit\",\"Instance management\",\"scripting\",\"SQL Server Inventory\"],\"articleSection\":[\"Automation\",\"Database Configuration\",\"PowerShell\",\"Security\",\"Tutorial\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=460#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=460\",\"url\":\"https:\/\/www.sqltabletalk.com\/?p=460\",\"name\":\"Streamline SQL Server Inventory Management Using PowerShell\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\"},\"datePublished\":\"2024-04-05T13:00:00+00:00\",\"description\":\"This blog offers a guide on using PowerShell to manage SQL Server inventory, focusing on a script that distinguishes between server and database information, with an explanation of script elements for improved clarity.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=460#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=460\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=460#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqltabletalk.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Capturing SQL Server Inventory with PowerShell\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\",\"url\":\"https:\/\/www.sqltabletalk.com\/\",\"name\":\"SQL Table Talk\",\"description\":\"Breaking Down SQL Server, One Post at a Time.\",\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqltabletalk.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\",\"name\":\"Stephen Planck\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"caption\":\"Stephen Planck\"},\"logo\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/dexterwiki.com\",\"https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI\",\"https:\/\/www.youtube.com\/linuxmate\"],\"url\":\"https:\/\/www.sqltabletalk.com\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Streamline SQL Server Inventory Management Using PowerShell","description":"This blog offers a guide on using PowerShell to manage SQL Server inventory, focusing on a script that distinguishes between server and database information, with an explanation of script elements for improved clarity.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqltabletalk.com\/?p=460","og_locale":"en_US","og_type":"article","og_title":"Streamline SQL Server Inventory Management Using PowerShell","og_description":"This blog offers a guide on using PowerShell to manage SQL Server inventory, focusing on a script that distinguishes between server and database information, with an explanation of script elements for improved clarity.","og_url":"https:\/\/www.sqltabletalk.com\/?p=460","og_site_name":"SQL Table Talk","article_published_time":"2024-04-05T13:00:00+00:00","author":"Stephen Planck","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Stephen Planck","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqltabletalk.com\/?p=460#article","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/?p=460"},"author":{"name":"Stephen Planck","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"headline":"Capturing SQL Server Inventory with PowerShell","datePublished":"2024-04-05T13:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqltabletalk.com\/?p=460"},"wordCount":581,"commentCount":0,"publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"keywords":["audit","Instance management","scripting","SQL Server Inventory"],"articleSection":["Automation","Database Configuration","PowerShell","Security","Tutorial"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqltabletalk.com\/?p=460#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqltabletalk.com\/?p=460","url":"https:\/\/www.sqltabletalk.com\/?p=460","name":"Streamline SQL Server Inventory Management Using PowerShell","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/#website"},"datePublished":"2024-04-05T13:00:00+00:00","description":"This blog offers a guide on using PowerShell to manage SQL Server inventory, focusing on a script that distinguishes between server and database information, with an explanation of script elements for improved clarity.","breadcrumb":{"@id":"https:\/\/www.sqltabletalk.com\/?p=460#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqltabletalk.com\/?p=460"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqltabletalk.com\/?p=460#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqltabletalk.com\/"},{"@type":"ListItem","position":2,"name":"Capturing SQL Server Inventory with PowerShell"}]},{"@type":"WebSite","@id":"https:\/\/www.sqltabletalk.com\/#website","url":"https:\/\/www.sqltabletalk.com\/","name":"SQL Table Talk","description":"Breaking Down SQL Server, One Post at a Time.","publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqltabletalk.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0","name":"Stephen Planck","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","caption":"Stephen Planck"},"logo":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/dexterwiki.com","https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI","https:\/\/www.youtube.com\/linuxmate"],"url":"https:\/\/www.sqltabletalk.com\/?author=1"}]}},"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/460","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=460"}],"version-history":[{"count":3,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/460\/revisions"}],"predecessor-version":[{"id":477,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/460\/revisions\/477"}],"wp:attachment":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=460"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=460"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=460"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}