As I promised in my previous blog Get the main Administration Information from SQL Server with PowerShell, I will present you this time how to retrieve the SQL Server databases information with PowerShell.
Disclaimer: 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!
Retrieve SQL Server databases information
I use the SMO objects to access the instance information. In my previous blog, I explaind how to initialize the SMO object from a specific instance.
Moreover, I also described how to list all installed SQL Server instances on Windows Server.
So, I assume the SMO object from the SQL Server instance has already been initialized, referring to this blog.
To display the main information of your databases, proceed as follows:
You can also obtain the data files information details about your databases. However, be aware that the data files are organized in File Groups. Here is the code which shows how to proceed:
To obtain the log files information, it is a little bit faster. Indeed, log files are not hierarchized in File Groups.
There are some important configuration options in SQL Server for the databases. These options are important to know. I show you how to retrieve them:
If you format your output, you can obtain report, dashboards, aso… Here is a capture of an existing dashboard from our Database Management Kit (DMK).
Next steps
If you want to learn more about the methods and properties included in the database SMO object, you should use the “Get-Member” command:
I hope this blog will help you. Of course, the SMO object is not the only approach. It can easily be done with the “sqlcmd” command for example.