Script database objects with PowerShell

I recently did a presentation about documenting SQL Server with PowerShell.

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 :wink:

I hope this can help you out with your daily work. Any comment is appreciated.

稿源:SQL Stad (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Script database objects with PowerShell

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录