Category: Uncategorized

Sometimes you have a need to capture the informational messages generated in sql server, for example sp_help_revlogin only prints the user information to the messages tab.
Import-Module SqlServer -DisableNameChecking
cls
try {

This isn’t a 31 days series, as I’ll undoubtedly miss a day here and there, so instead, this will just be a ’31 posts’ series instead. I’m pragmatic. And lazy.

I have noticed there is a bit of discomfort amongst most database administrators when it comes to dealing with powershell. Do yourself a favor and learn it. You’ll wonder how you ever got by without it before.

So here we go with post 1.

Importing Sql Server Cmdlets

In order to begin this series, you’ll need to import the sql server cmdlets that will enable you to work with the smo objects that you’ll use to work with sql server. If you have sql server management studio 12 and above, you’re in luck. All you need to do to import the sql server cmdlets is this:

Import-Module SqlPS -DisableNameChecking;

The –DisableNameChecking is optional, but if omitted you’ll get a warning that some of the verbs in the cmdlets are unapproved:

A word of warning; this module loads extremely slow. This has been fixed in Sql Server 2016, but as noted in the closing notes of this post, that requires that SSMS for Sql Server 2016 be installed.

If you’ve got Sql Server 2008 R2 and below, you’ll need to install a few things in order to get the sql server cmdlets working correctly. More or less, the SqlPS module requires the use of Sql Server 2012 Shared Management Objects, so you’ll need to download 3 components from the Sql Server 2012 (or 2014) feature pack:

And finally, download and install the Windows Powershell Extensions for Sql Server:

Once you have all three of these installed and restart your powershell environment, you should be able to run the Import-Module SqlPS command correctly.

Okay, post 1 complete. Sort of. As of Sql Server 2016, the SqlPS module is being replaced by a new SqlServer module, but that requires that the Sql Server 2016 SSMS must be installed to use it (at the time of this writing), so we’re just going to plod along using SqlPS for now. Most of the functionality provided in the SqlServer module will still work.

Not battle-tested at all. Use at your own risk. Works for me though. You’ll need an $accessKey and $secretKey variables set outside the scope for this to work. The $bucketName is the bucket that the file is located in. The $fileName is the name of the file in said bucket. The $dest is where you want to write the file locally. The $logFile does nothing at this point.

Just to save you the trouble, here is the best code formatter for windows live writer when publishing to wordpress. This is more of a reminder for myself so’s I don’t have to scour the web to find this again, but hopefully this will save someone else some time in trying to track down a good code formatter. You can grab it here.

It’s the one that I’m using in the latest posts on this site. If you scroll to the earlier posts, you’ll definitely notice the difference.