{"id":4628,"date":"2015-05-18T10:00:35","date_gmt":"2015-05-18T08:00:35","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/"},"modified":"2015-05-18T10:00:35","modified_gmt":"2015-05-18T08:00:35","slug":"get-the-main-administration-information-from-sql-server-with-powershell","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/","title":{"rendered":"Get the main Administration Information from SQL Server with PowerShell"},"content":{"rendered":"

In my previous blog Get SQL Server services with PowerShell<\/a>, I presented you the first step of the SQL Server administration through an automated process.
\nThis blog is a follow-up of the previous one, and it will focus on retrieving information of a SQL Server instance with PowerShell
\nDisclaimer: I am not a developer but a SQL Server dba. If you find errors or some ways of improvement, I will be glad to read your comments!<\/p>\n

List all SQL Server instances<\/h3>\n

To be able to proceed for all the instances, you can easily get all your instance names with this function:<\/p>\n

\"Get-SQLInstances_function_20150504-135411_1.png\"<\/a><\/p>\n

Retrieve SQL Server instance information<\/h3>\n

In my example, I execute my script on the machine hosting my SQL Server instance.
\nI use SMO objects to access to the instance information . But you need the instance full name , as follows:<\/p>\n

\"full_instance_name.png\"<\/a><\/p>\n

I only give the instance name as parameter because I execute my script on a local server, otherwise I need to give the server name as parameter.
\nFirst I initialize my SMO object of my instance like this:<\/p>\n

\"instance_smo_object.png\"<\/a><\/p>\n

This SMO object contains SQL Server instance main information. To list all properties and the object methods, proceed as follows:<\/p>\n

\"service_properties_and_methods_20150504-141841_1.png\"<\/a><\/p>\n

To list the general information of\u00a0 the instance, you can proceed like this:<\/p>\n

\"instance_general_information.png\"<\/a><\/p>\n

To list the directory paths related to the instance, here is an example:<\/p>\n

\"instance_directories.png\"<\/a><\/p>\n

To list important instance configuration, here is an example:<\/p>\n

\"instance_configuration.png\"<\/a><\/p>\n

By formating the information you retrieve in the instance SMO object, you can generate reports, audit your environment or whatever!
\nThe following capture is an existing dashboard from our Database Management Kit (DMK)<\/a>.<\/p>\n

\"dmk_instance_information.png\"<\/a><\/p>\n

Next steps<\/h3>\n

The SMO object for the SQL Server instance has a limit number of properties and methods. Sometimes, you need information which are not present in the object. In this case, you must use the “sqlcmd” command and retrieve your information by using T-SQL.<\/p>\n

Here is the way to proceed:
\n\"invoke_sqlcmd_command.png\"<\/a><\/p>\n

To retrieve any of SQL Server instance information, the “sqlcmd” command would always work. You can also use it to modify the instance configuration.
\nI hope this blog will help you in your work. In my next blog, I will show you how to access to your database information with PowerShell.<\/p>\n","protected":false},"excerpt":{"rendered":"

In my previous blog Get SQL Server services with PowerShell, I presented you the first step of the SQL Server administration through an automated process. This blog is a follow-up of the previous one, and it will focus on retrieving information of a SQL Server instance with PowerShell Disclaimer: I am not a developer but […]<\/p>\n","protected":false},"author":14,"featured_media":4629,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[559,272,51],"type_dbi":[],"class_list":["post-4628","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","tag-dmk","tag-powershell","tag-sql-server"],"acf":[],"yoast_head":"\nGet the main Administration Information from SQL Server with PowerShell - dbi Blog<\/title>\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.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Get the main Administration Information from SQL Server with PowerShell\" \/>\n<meta property=\"og:description\" content=\"In my previous blog Get SQL Server services with PowerShell, I presented you the first step of the SQL Server administration through an automated process. This blog is a follow-up of the previous one, and it will focus on retrieving information of a SQL Server instance with PowerShell Disclaimer: I am not a developer but […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-05-18T08:00:35+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLInstances_function_20150504-135411_1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1086\" \/>\n\t<meta property=\"og:image:height\" content=\"358\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Nathan Courtine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nathan Courtine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/\"},\"author\":{\"name\":\"Nathan Courtine\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"headline\":\"Get the main Administration Information from SQL Server with PowerShell\",\"datePublished\":\"2015-05-18T08:00:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/\"},\"wordCount\":378,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLInstances_function_20150504-135411_1.png\",\"keywords\":[\"DMK\",\"PowerShell\",\"SQL Server\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/\",\"name\":\"Get the main Administration Information from SQL Server with PowerShell - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLInstances_function_20150504-135411_1.png\",\"datePublished\":\"2015-05-18T08:00:35+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLInstances_function_20150504-135411_1.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLInstances_function_20150504-135411_1.png\",\"width\":1086,\"height\":358},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Get the main Administration Information from SQL Server with PowerShell\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\",\"name\":\"Nathan Courtine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"caption\":\"Nathan Courtine\"},\"description\":\"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Get the main Administration Information from SQL Server with PowerShell - dbi Blog","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.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/","og_locale":"en_US","og_type":"article","og_title":"Get the main Administration Information from SQL Server with PowerShell","og_description":"In my previous blog Get SQL Server services with PowerShell, I presented you the first step of the SQL Server administration through an automated process. This blog is a follow-up of the previous one, and it will focus on retrieving information of a SQL Server instance with PowerShell Disclaimer: I am not a developer but […]","og_url":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/","og_site_name":"dbi Blog","article_published_time":"2015-05-18T08:00:35+00:00","og_image":[{"width":1086,"height":358,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLInstances_function_20150504-135411_1.png","type":"image\/png"}],"author":"Nathan Courtine","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Nathan Courtine","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/"},"author":{"name":"Nathan Courtine","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"headline":"Get the main Administration Information from SQL Server with PowerShell","datePublished":"2015-05-18T08:00:35+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/"},"wordCount":378,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLInstances_function_20150504-135411_1.png","keywords":["DMK","PowerShell","SQL Server"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/","url":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/","name":"Get the main Administration Information from SQL Server with PowerShell - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLInstances_function_20150504-135411_1.png","datePublished":"2015-05-18T08:00:35+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLInstances_function_20150504-135411_1.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Get-SQLInstances_function_20150504-135411_1.png","width":1086,"height":358},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/get-the-main-administration-information-from-sql-server-with-powershell\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Get the main Administration Information from SQL Server with PowerShell"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1","name":"Nathan Courtine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","caption":"Nathan Courtine"},"description":"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4628","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4628"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4628\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/4629"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4628"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4628"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4628"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4628"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}