Press "Enter" to skip to content

Category: Powershell

Exporting SQL Server Tables to Excel with Powershell

Published 2019-03-20 by Kevin Feasel

Aaron Nelson shows how you can export the tables in a SQL Server database to Excel, using a warehouse as an example:

Obviously, you have to have the module installed, and a copy of AdventureWorksDW2017 db restored to a SQL Server.  After that,  all you have to do is loop through the tables, ‘query’ them with the Read-SqlTableData cmdlet, and pipe the results to the Export-Excel cmdlet.

I did some trial and error with this yesterday.  I settled on exporting all of the Dimension tables to separate Worksheets within the same Excel file, and exporting all of the Fact tables to their own individual files (since they tend to be much larger).

Click through for Aaron’s script.

Comments closed

Desired State Configuration: Managed Object Format Files

Published 2019-03-19 by Kevin Feasel

Jess Pomfret explains what Managed Object Format (MOF) files are and why they’re useful for Desired State Configuration:

When I run this script I see the output in the screenshot below, a MOF file has been created in my output folder. Managed Object Format (MOF) files are used to describe Common Information Model (CIM) classes, these are industry standards which gives us flexibility in working with DSC. In DSC this is important as the MOF file is the artefact that will actually be used to configure our nodes. This MOF will be delivered to our target node and enacted by the Local Configuration Manager (LCM).

The LCM will be covered in more detail in a later post, but for now know that it can be configured to be in either ‘Push’ mode or ‘Pull’ mode.  Pull mode is more complicated to set up but perhaps more appropriate for managing a large number of servers.  For now, we will look at the ‘Push’ mode where we will deliver the MOF manually to the target node for the LCM to enact.

There are a lot of TLAs to watch out for within Desired State Configuration.

Comments closed

Retaining a Few Tables From a Large Set

Published 2019-03-18 by Kevin Feasel

Jana Sattainathan has a Powershell-based solution to eliminate all but a few tables in a database:

Recently, I received a request to backup a dozen tables or so tables out of 12 thousand tables. I had to retain all the indexes, statistics etc. The goal was to hand this over to the vendor for analysis as a database backup.

I could have copied the selected tables over to a new database using the PowerShell function I had published earlier and backed that up but since the tables to backup were quite large, I skipped that route

Read on to see Jana’s solution.

Comments closed

SqlServer Module Now with Invoke-Sqlcmd

Published 2019-03-12 by Kevin Feasel

Aaron Nelson alerts us to a new preview of the SqlServer Powershell module:

If you’re still on an earlier version of PSCore and are unable to install PSCore 6.2 right now, you can still download preview of the SqlServer module to get the latest fixes and new features.  You just won’t be able to use the Invoke-Sqlcmd cmdlet.

Another quick thing to note is that this is like a v.0.0.1 of Invoke-SqlCmd on PSCore; it does not have all the bells & whistles of the version of Invoke-Sqlcmd for [full blown] Windows PowerShell.  Obviously, more features will be added over time, but the basic functionality was ready to for customers to start “kicking the tires”.

Read on for more notes and the link to check this all out.

Comments closed

Failing A Powershell Step In SQL Agent

Published 2019-03-06 by Kevin Feasel

Stuart Moore shows us how we can get a SQL Agent job running a Powershell step to recognize failure:

You might want the same response to make sure your monitoring is letting you know when jobs fail. On that note, it would also be nice if you could raise an error or failure message in your PowerShell step and have that propagate back up to SQL Server

Unfortunately the usual scripting standbys of returning 0 or $false don’t work.

Stuart does have a solution, though, so read on to learn what it is.

Comments closed

Group Managed Service Accounts

Published 2019-02-27 by Kevin Feasel

Jamie Wick explains Group Managed Service Accounts and uses Powershell to create them for use on a new SQL Server instance:

Service Accounts are a requirement for installing and running a SQL Server. For many years Microsoft has recommended that each SQL Server service be run as a separate low-rights Windows account. Where possible, the current recommendation is to use Managed Service Accounts (MSA) or Group Managed Service Accounts (gMSA
). Both account types are ones where the account password is managed by the Domain Controller. The primary difference being that MSA are used for standalone SQL instances, whereas clustered SQL instances require gMSA. In this post, we’re going to use PowerShell to create Group Managed Service Accounts, and then deploy them for use on multiple SQL servers that will be hosting an Availability Group.

Click through for more explanation as well as several scripts showing how to create and use them.

Comments closed

The Anatomy of a Pester Test

Published 2019-02-25 by Kevin Feasel

Shane O’Neill takes us through using Pester to test self-contained scripts:

Where things differ…
…could be when you try to accommodate different people and create a .ps1 file that both defines and calls a function. Self Contained scripts, if you would call them that.
Normally the reason that I’ve heard from this is you’re trying to help a non-technical minded person and they just want a file that they can open, hit “run”, and everything is done for them.
Have you ever tried to Pester test those files though? It’s not recommended, especially if your function removes or modifies objects.

Click through for a solution and read Shane’s update as well for a scenario where it doesn’t quite work as hoped.

Comments closed

Regression Testing With Pester

Published 2019-02-22 by Kevin Feasel

Ust Oldfield continues a series on Pester testing:

In a previous post, I gave an overview to regression tests. In this post, I will give a practical example of developing and performing regression tests with the Pester framework for PowerShell. The code for performing regression tests is written in PowerShell using the Pester Framework. The tests are run through Azure DevOps pipelines and are designed to test regression scenarios. The PowerShell scripts, which contain the mechanism for executing tests, rely upon receiving the actual test definitions from a metadata database. The structure of the metadata database will be exactly the same as laid out in the Integration Test post.

There’s a hefty test script here too, so check it out.

Comments closed

Formatting Powershell Outputs

Published 2019-02-21 by Kevin Feasel

Jeffery Hicks shows us how we can format Powershell files through XML:

My PSScriptTools module (which you can install from the PowerShell Gallery) now includes a command called New-PSFormatXML. The command is designed to analyze an object and by default create a table view of all properties, although you can specify which properties to include. The format.ps1xml file will autosize the table but you can remove the directive and use the widths which are best guesses. Expect some trial and error when defining a new view.

Read on for a couple demonstrations.

Comments closed

Tooling For SQL Server Automation With Powershell

Published 2019-02-20 by Kevin Feasel

Max Trinidad shares some tools you can use to automate SQL Server processes with Powershell:

For script automation we could install either or both version of PowerShell Core: (As of February 19th, 2019)
PowerShell Core GA version 6.1.3
PowerShell Core Preview 6.2.0 Preview 4

Here are some important PowerShell Modules to use for SQL Server management scripting:
*SQLServer – This module currently can be use on SQL Server 2017 and greater.
*DBATools – This a community supported module that will work with SQL Server 2000 and greater.
DBAReports – Supports for Windows SQL Server.
DBCheck – Support for Windows SQL Server.

Automation is a great DBA’s best weapon. Knowing the tools which help you automate your tasks is critical.

Comments closed