Press "Enter" to skip to content

Category: Powershell

Restoring A Database To A Different Location

Published 2017-01-26 by Kevin Feasel

Mike Fal shows how to restore a database to a different location using Powershell:

The cmdlet is straightforward in its use. Fundamentally, all we need to declare is an instance, database name, and backup file. However, if we don’t declare anything else, the cmdlet will try and restore the database files to their original locations. Keep in mind this is no different than how a normal RESTORE DATABASE command works.

This is where we make our lives easier with PowerShell. First off, to move files using Restore-SqlDatabase, we need to create a collection of RelocateFile objects. Don’t let the .Net-ness of this freak you out. All we’re doing is creating something that has the logical file name and the new physical file name. In other words, it’s just an abstraction of the MOVE statement in RESTORE DATABASE.

Read the whole thing.

Comments closed

Installing DBATools Without Powershell 5

Published 2017-01-24 by Kevin Feasel

Steve Jones has to install dbatools without the benefit of Powershell 5.0:

I have a number of Windows Server 2012 R2 VMs. I need to get rid of these and upgrade to WS2016, since installing SQL 2016 is a pain on these VMs. However, for the time being, I’m stuck with them.

I was looking to try some of the dbatools modules. I popped open an elevated command prompt and [error]

This is because that module isn’t in this version of PoSh. I checked, and I could see this is v4, and Install-Module was added in v5.

Click through for the solution.

Comments closed

Streaming Data To Power BI

Published 2017-01-23 by Kevin Feasel

Sacha Tomey builds a quick Powershell script to feed streaming data into Power BI:

Peter showed off three mechanisms for streaming data to a real time dashboard:

  • The Power BI Rest API
  • Azure Stream Analytics
  • Streaming Datasets

We’ve done a fair bit at Adatis with the first two and whilst I was aware of the August 2016 feature, Streaming Datasets I’d never got round to looking at them in depth. Now, having seen them in action I wish I had – they are much quicker to set up than the other two options and require little to no development effort to get going – pretty good for demo scenarios or when you want to get something streaming pretty quickly at low cost.

Click through for more details and a sample script.

Comments closed

The Importance Of Powershell

Published 2017-01-20 by Kevin Feasel

Kevin Hill explains why he’s advocating that DBAs learn Powershell:

2 very solid reasons (there are others) that every DBA should be learning and using PowerShell:

1 – Its very useful for admin at the O/S level.

At my current client I am team lead of System and SQL Admins, along with doing any of the work that comes our way.  This means we need to be able to manage the modest server farm we have.  Its big enough that we can’t log onto every server every day, but small enough nobody wants to buy a proper monitoring toolset.  So…PS to the rescue!

Read on for the other reason.  I think the relatively poor Powershell tooling with SQL Server (with respect to other groups like Exchange) limited general acceptance, but they’ve made some big improvements over the past year and there are some sharp minds in the community working to make Powershell even more important for DBAs.

Comments closed

ApplicationName On Invoke-SqlCmd2

Published 2017-01-20 by Kevin Feasel

Andy Levy improves the Invoke-SqlCmd2 cmdlet:

I decided to change this around so that it no longer uses string formatting, but instead a SqlConnectionStringBuilder. I had a couple reasons for this:

  • It will eliminate redundant code. There are several common elements in each of the ConnectionStrings above. If more complex logic is needed, there are potentially more copies of this ConnectionString kicking around.

  • It’s prone to copy/paste and other editing errors. If there’s a change that affects both versions of the ConnectionString and the developer just copies the line from one branch of the if statement to the other, code will be lost or invalid values will be substituted because of positioning.

This is something I’d like to see make it to the main cmdlet.

Comments closed

Reading Extended Event Data From Powershell

Published 2017-01-10 by Kevin Feasel

Dave Mason builds a Powershell script to parse Extended Events information:

Powershell takes center stage for this post. Previously, I showed how to handle a SQL Server Extended Event in C# by accessing the event_stream target. We can do the same thing in PowerShell. The code translates mostly line-for-line from C#. Check out the last post if you want the full back story. Otherwise, continue on for the script and some Posh-specific notes.

Read on for the code.

Comments closed

Deleting SSAS Partitions

Published 2017-01-06 by Kevin Feasel

Chris Koester shows how to use TMSL and Powershell to delete an Analysis Services tabular model partition:

The sample script below shows how this is done. The sequence command is used to delete multiple partitions in a single transaction. This is similar to the batch command in XMLA. In this example we’re only performing delete operations, but many different operations can be performed in sequence (And some in parallel).

Click through for a description of the process as well as a script to do the job.

Comments closed

Always Encrypted With Powershell

Published 2017-01-06 by Kevin Feasel

Jakub Szymaszek shows how to configure Always Encrypted support from Powershell:

Note: In a production environment, you should always run tools (such as PowerShell or SSMS) provisioning and using Always Encrypted keys on a machine that is different than the machine hosting your database. The primary purpose of Always Encrypted is to protect your data, in case the environment hosting your database gets compromised. If your keys are revealed to the machine hosting the database, an attacker can get them and the benefit of Always Encrypted will be defeated.

That’s a good warning.

Comments closed

Loading SMO In Powershell

Published 2016-12-23 by Kevin Feasel

Chrissy LeMaire shows how to load SMO with full names when you don’t know which version is installed:

In a recent version of PowerShell, Publish-Module, which publishes modules to the Gallery began requiring fully qualified Assembly names such as “Microsoft.SqlServer.Smo, Version=$smoversion, Culture=neutral, PublicKeyToken=89845dcd8080cc91”.

Previously, it was sufficient just to use short names such as Microsoft.SqlServer.Smo. This had similar behavior to LoadWithPartialName.

I get that LoadWithPartialName is sketchy, but the solution that Chrissy has to do seems overly complicated to me.  Nonetheless, those are the rules of the game now, I suppose.

Comments closed