The presentation was initially intended to show people how to get data like configuration settings, databases, database files etc, from SQL Server and export that to an Excel file.
At the end several people had questions how to export database objects using PowerShell. I must admit I didn’t have a direct answer and promised to come back with solution. Luckily Bob Klimes had a solution by using the Scripter functionality in the SMO and even send me one of his scripts.
I had never used this part of the SMO before and wanted to know what it did, so as I start with reading the documentation . Cool, we have a script function and I can retrieve a type which enables me to automate some things.
In the end I got a nice function which could well be implemented in my existing module,PSSQLLib, and so I did.
There is now another function in the library called Export-DatabaseObject. It works by giving a parameter for the instance and a parameter with a path to export to. The function has a lot more parameters but these are not mandatory.
When executed it looks like this:
It will create a folder structure with the instance name, database, time stamp and at last a directory containing all the files for a specific object type like a table or view.
This made my life easier and again it’s a thing that can be done much faster using a script than using the GUI in SSMS 😉
I hope this can help you out with your daily work. Any comment is appreciated.