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.
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.
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.
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.
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.
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.
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.
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.
For the brevity of this post, I will only download couple of R packages from CRAN repository, but this list is indefinite.
There are ways many ways to retrieve the CRAN packages for particular R version using powershell. I will just demonstrate this by using Invoke-WebRequest cmdlet.
Pointing your cmdlet to URL: https://cran.r-project.org/bin/windows/contrib/3.5 where list of all packages for this version is available. But first we need to extract the HTML tag where information is stored.
There’s quite a bit of code here, but the upside is that you get the ability to automate server installs.
This week I had the opportunity to learn how to use dbatools to automate backups, change recovery models, and discover additional dbatools commands from dbatools contributor Jess Pomfret.
The final tip I had for Bert was how to use
Find-DbaCommandto help him find the commands he needed to complete his tasks.
A lot of the commands have tags, which is a good way to find anything relating to compression.
That was a nice collaboration.