Category Archives: Powershell

Powershell to Robocopy files (say, to back up your computer to protect against CryptoWall trojan)

We were having a good time on DBAs@Midnight, when Sean got a weird pop up message that said all his files were encrypted, and he has to pay a large amount of money via Bitcoin to get the encryption key. The show quickly ground to a halt as he, then he and Freddie, then all three of us PLUS the chatroom, looked into what this was, how to stop it, what to do, and so on.

In short: Sean lost just about everything. He lost most of what he has on his computer. He lost all the important files that he’d made sure were copied offsite to OneDrive – CryptoWall encrypted those too. (You might want to wait a few weeks before mentioning it in his presence, seriously.)

The funny thing is, we’re data professionals, and we thought we were reasonably well covered for backup. Okay, OneDrive isn’t perfect/ideal, but we neglected to imagine the scenario that didn’t just affect one computer, or one home network, but also spread to The Cloud of Awesomeness.

It’s not the final solution, but…

You’re going to want to get yourself something like Carbonite Cloud Backup (though I wonder, if you have the constant update turned on, whether this would be an advantage against the CryptoWall encryption…probably ONLY if Carbonite keeps file versions).

AND you’re going to want to do regular (say, monthly or so) backups to an external hard drive. I have my Documents folder, my Dropbox folder, and several folders on the root, and even with Robocopy (it comes with Windows!), I found it a little bit of a pain to copy specific things over. So, Powershell!

This script – RobocopyToDest.txt* – lets you define a source, destination, and exclusions (e.g., “don’t copy over the XYZ folder”) to copy a set of directories. It’s a rough cut, I-just-made-this script, but I rant it and it works beautifully. Take it and adjust to your own needs, and for heaven’s sake back up your files!

 *Save RobocopyToDest.txt with a .PS1 file extension. WordPress doesn’t like saving script files, and I don’t like zipping up tiny text files. 

Read up on this stuff

Session: Introduction to Powershell Cmdlets for DBAs

I’ve given my new Powershell Cmdlets for DBAs session a handful of times now, and just realized I haven’t blogged about it!

Abstract: You’ve been hearing about this newfangled craze, and it’s time to learn exactly why it’s called POWERshell. You’ll want to take notes, or better yet, bring your laptop and start using Powershell immediately. We will get Posh running and then I’ll introduce you to the basic concepts, with examples to make things happen right away. We’ll work on SQL Server with Powershell, and learn some cool techniques. Come see this all-demo, hands-on session!

Comments:  This is totally a beginner session. We start by talking about what Powershell is and why it’s awesome, and get into the most basic of basics:

  • write output to the screen
  • aliases
  • connecting to SQL Server from Powershell
  • getting help
  • playing with cmdlet options
  • write to files
  • piping
  • variables

and on and on.

Enjoy, send me questions and feedback, all that jazz. Happy days!
Jen McCown
http://www.MidnightDBA.com/Jen

Download Minion Reindex FREE at MidnightSQL.com/Minion

Configure static or dynamic IP and DNS with Powershell

I work in a lot of different offices and conference venues, in addition to my home office. At home, I need a static DNS server address; everywhere else, it has to be dynamic. Tired of the click-click-clickety-click it takes to set, unset, and reset my network adapter settings, I went looking for a Powershell solution. This is the kind of problem that Powershell is made for, right? Right!

We need the Get-WmiObject cmdlet*, along with the win32_NetworkAdapterConfiguration class.

What’s my IP?

For starters, let’s use Get-WmiObject and that very long class name to get our network adapter settings:

$wmi = Get-WmiObject win32_networkadapterconfiguration -filter "ipenabled ='true'";

If we glance over what methods and properties are available in the new object ($wmi | get-member;) the IPAddress property leaps out at me. We can use that to display our current IP address**:

$wmi.IPAddress;

That was easy. You can also get your:

  • Subnet mask: $wmi.IPSubnet
  • DNS server address: $wmi.DNSServerSearchOrder
  • Gateway: $wmi.DefaultIPGateway

Set Static IP, Gateway, DNS server

Here’s where we note that we
SHOULD NOT DO THIS ON A PRODUCTION SYSTEM.
You’re messing with the network settings, remember. 

DNS_staticLet’s go ahead and set a static IP, gateway, and DNS server, using that same $wmi object from above. The script would then look like this:

$wmi = Get-WmiObject win32_networkadapterconfiguration -filter "ipenabled ='true'";
$wmi.EnableStatic("IP address", "Subnet mask");
$wmi.SetGateways("Gateway", 1);
$wmi.SetDNSServerSearchOrder("DNS server address");

EnableStatic allows you to set a specific IP address and subnet mask. SetGateways is for the default gateway. And SetDNSServerSearchOrder allows you to set static preferred and/or alternate DNS servers.

As an example:

$wmi = Get-WmiObject win32_networkadapterconfiguration -filter "ipenabled ='true'";
$wmi.EnableStatic("10.0.0.15", "255.255.255.0");
$wmi.SetGateways("10.0.0.1", 1);
$wmi.SetDNSServerSearchOrder("127.1.1.12");

This example results in the settings you see in the image on the right: all static, all the time.

Set Dynamic IP, Gateway, DNS server

DNS_dynamicWhen I’m not at home, everything must be dynamic. It took a bit of digging – not a lot of folks are blogging about setting dynamic network adapter settings – but it turns out to be easy, of course!

$wmi = Get-WmiObject win32_networkadapterconfiguration -filter "ipenabled ='true'";
$wmi.EnableDHCP();
$wmi.SetDNSServerSearchOrder();

DHCP “allows IP addresses to be dynamically allocated”, and running SetDNSServerSearchOrder*** with no parameters (or alternately, with $null) sets the network adapter to get a DNS server address dynamically. Brilliant!

That’s all there is to it, and you can feel free to stop reading here. Unless, of course, you want to get into the muddle that is Get-CimInstance in Powershell 3.0…

But what about CIM?

Some of you may be shouting about Powershell 3.0 and Get-CimInstance; let me say for now that I had no end of trouble with it, I’m still working on it, and there will be a future blog post. And yes, I did find that chapter in the Windows Powershell Cookbook that spells out the use of CIM for this, and yes, I had trouble with that as well. All I can say is, I’m still working on it, and WMI seems to work just fine, thankyouverymuch.

Happy days,
Jen McCown
MidnightDBA.com/Jen

 

* WMI stands for “Windows Management Instrumentation“…Windows administrative settings and general stuff.

** By the way, you could get also use test-connection computername, a ping equivalent, to get your IP. But today we’re talking Get-CimInstance.

*** On setting DNS to dynamic: In my searching, I found this solution, but have had zero luck with it effecting the change: SetDynamicDNSRegistration(“TRUE”)