Script database objects with PowerShell

存储架构 2016-04-15

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 😉

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

SQL Stad

责编内容by:SQL Stad (源链)。感谢您的支持!


CEO update (4/4/18): TimescaleDB vs. Influx, Read-... Hi friends, Let’s start with a fun fact: On this day one year ago, TimescaleD...
Copying Histograms and Extended Column Statistics Support added a new MOS note recently that includes a couple of scripts that que...
Interviews: What is ACID? I once had a job interview with a large group of employees at a company. They we...
Django multiple dbs – administers search res... I'm using the admin search_fields functionality. The problem...
PHP应用程序在MVC模式中构建安全API 继续工作 在本系列文章的 第一部分 和 第二部分 我介绍了一些我们构建API所需要的基础库和基本概念。现在我们将进入本系列文章的第三部分,在这...