SQL Server Backup and Restores with PowerShell Part 1: Setting up

Congratulations!

The fact that you’re reading this post means you’re ready to start exploring the power of marrying PowerShell and SQL Server. That’s great! But before we get too deep into the process, there are a few basic steps and concepts you need to be familiar with. And unfortunately, it’s not going to make a lot of sense if you’re new to PowerShell in general. But hang in there; roll up your sleeves, grab some coffee, and warm up your fingers. Pointing and clicking is for suckers, we’re gonna type our way to Awesometown.

Launching PowerShell

There’s two main ways to start PowerShell with an intent to do things with SQL Server. Both ways by themselves, however, are fraught with pitfalls. You can either launch PowerShell from the taskbar (or start menu) in Windows, or right from SQL Server Management Studio.

What’s the difference? Quite a lot, actually. The bad news is that launching PowerShell either of these ways immediately causes you some issues.

“Why launch PowerShell from Management Studio?” Well, there’s one really good reason: the shell that is launched is actually a utility called SQLPS.EXE. SQLPS.EXE is a stand-alone utility that launches PowerShell, but with one key difference: it pre-loads all the sweet, sweet verbs and “commandlets” (cmdlets) that make integration tighter with SQL Server. The other big difference is that it adds SQL as a “drive” to your shell, meaning you can traverse SQL Server like you would your filesystem (which we’ll see more of later).

“Why launch PowerShell from the OS?” Chances are, it’s a more current version of PowerShell than the version launched from SQL Management Studio. Code you write in something like the ISE (see below) might work great in the native shell, but might go cattywampus¹ when you try to use it from SQL Server.

To make things even more complicated, versions can vary greatly depending on the Server OS you’re using coupled with your version of SQL server. The chart below might help illustrate that, but depending on recent Windows Updates you might have run, this is what could be when you install SQL Server on a particular version of Windows. The left number is the native version of PowerShell for that OS, and the right number is the version of the SQLPS shell.

[table id=1 /]

*Server 2003 doesn’t have native support for PowerShell, but you can grab the latest Windows Management Framework for the OS to get it installed.
**SQL Server 2008/R2 doesn’t really have a proper PowerShell shell per se, but instead uses a mini-shell. This can cause you problems!

Clearly, there can be some gaps, and getting everything synced on our servers can be challenging. So how do know what versions we’re running where?

$PSVersionTable

To highlight the biggest differences (and cause of most pains with PowerShell and SQL Server), do yourself a favor and launch both windows: the PowerShell icon and right-clicking inside SSMS. Aside from the obvious blue vs. black console windows, there’s one very critical (and likely painful) difference between the two. In each window, type the following command:

You should see an output similar to the below picture:

Right off the bat, we can see some differences. For now, let’s focus on the console lines that say “PSVersion.” This indicates the PowerShell console version that is active. For my example, I am running Windows Server 2012R2, which is running PowerShell 4.0. However, the window launched from Management Studio is showing version 2.0. This can lead to some tremendous problems if you write code in a different shells and expect it to just “work” with SQL Server (my favorite example is the ForEach-Object command when working with single-item object arrays. Cue a few readers solemnly nodding their heads).

Getting Current

So how can you get your versions synced up across your servers? There’s a couple different ways:

1. If you’re on an older OS like Server 2003, you can grab the latest Windows Management Framework for your OS. For Server 2003 you’re stuck with WMF 2.0. But hey, support for the OS is ending! Time to upgrade anyhow! Note that installing a WMF pack will cause you downtime for a reboot.
2. If you’re running SQL Server 2008/R2 on a server, do yourself a favor and install SQL Server Management Studio 2012 or 2014. Why? Because the PowerShell support for SQL Server in 2008/R2 is… crap. Installing a newer version management studio will get you a newer version of the SQL Server PowerShell module. Which is going to be key.

Now with that out of the way, let’s get our environment set up.

Bridging the Gap: Import-Module

If you’ve dabbled with SQL Server and PowerShell before, chances are you might have seen someone tell you to type something like this:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Or, even scarier, maybe even something like this:

[System.Reflection.Assembly]::LoadWithPartialName ("Microsoft.SqlServer.Smo");

Both pieces of code above are attempting to do the same thing: telling the native PowerShell shell to load some libraries for use in your scripts. You have to do this because if you don’t, SQL-specific commands won’t work. Sure, you could go through and do the whole “AddPSSnapin” route and map to binaries, or manually load them to the Global Assembly Cache.

So how do we get the best of the latest version of PowerShell coupled with the built-in functions of SQLPS.EXE? Fortunately, Microsoft has the answer and that’s to use Import-Module. Starting with SQL Server 2012, SQLPS is now a module² that can be imported into your main native PowerShell shell. In your blue PowerShell window, type the following:

It may take a second, but once it’s done it should look like the above picture. For now, don’t worry about that warning; that’s PowerShell telling you that you might run into issues with command names becaue the SQLPS module uses some non-standard names (you can also supress this warning). Once the module is loaded, your prompt will change to the “SQLSERVER:\” drive. You can move between that drive and your system drive much like you would with a command prompt, simply type “cd C:” to move back to your file system.

If you’re trying this on a SQL 2008R2 instance, you probably got an error saying it couldn’t find the module. That’s because, as mentioned above, the SQLPS utility is simply a mini-shell. It’s not a module you can load. There are ways³ to use Import-Module on a computer that has SQL Server 2008R2 installed, but it’s hacky and doesn’t include a lot of the cmdlets you’re going to find out there. Just bite the bullet and get a newer version of management studio installed.

Create a Profile and Workspace

Okay, now that we’ve cleared some brush, it’s time to lay a foundation. It might not seem like much but sooner or later you’re going to get annoyed that you have to type the same import-module command every time you start PowerShell. And depending on where you store your code, you might also become quickly annoyed that your autocomplete isn’t working because you aren’t in the right directory. You can alleviate this by doing three simple things:

Setting the Execution Profile

If you start writing your own scripts, or you start downloading scripts from the internet, you might get an error when you try and run them. That’s because of the Windows PowerShell Execution Policy⁴ security setting. I’d advise you to take a moment and run the following on your local machine:

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

Create a working directory

One really good habit to get into with PowerShell is create yourself a place to store your code, whether that be locally and/or where your scripts will be in production. Let’s say that will be a folder on the root of my system drive, such as C:\Scripts. Go ahead and create that folder now.

Create a PowerShell Profile

Next, we can help make your life easier when launching PowerShell by creating a profile. By default, the console will attempt to load a profile. The path to this profile is stored in a local variable called $profile. If you just type $profile into your PowerShell window, you can see where it thinks your profile is:

If you’re new to PowerShell, you probably don’t have a profile yet. Fortunately, it’s pretty easy to create one (with PowerShell, no less). In your Powershell window, type the following code:

New-item –type file –force $profile
notepad $profile

What is this code doing? Well, in a nutshell, it’s creating the profile file just in the place it thinks it should be: in your user folder. The -force part of the script is there to overcome any pesky user permission issues, or existing files. We’ll see more use of the -force parameter later on, but for now, know this: don’t use it unless you have to. For example, if you already have a profile, omitting the -force parameter will generate an error about the file already existing, whereas adding it will just blow away your existing profile and add a new one.

Once the profile file is created (line 1), then you can notepad.exe and use the $profile variable to open the file you just created. You should see a blank notepad window open up (since the file is empty). Your PowerShell profile is basically a canvas where you can type commands that the shell will execute each time it runs. So, for our DBA purposes, let’s add the following lines:

Import-Module sqlps
cd C:\Scripts

Essentially, we’re issuing commands to the shell to import the SQLPS module each time we open a new window, and then change our directory our working directory. Save your file, close notepad, and then close your PowerShell window and relaunch it. You should then see something like this:

 The PowerShell ISE

One common thing I see people asking is: where do I actually write PowerShell code? Do I need some sort of IDE? Would notepad work? The answer to those questions is “yes” but before you go spending any money, you may want to check out the PowerShell ISE (or Integrated Scripting Environment). It comes with PowerShell (and most recent versions of Windows and Windows Server, but you can find it here if you need to download it). The ISE is great because it gives you a place to type your code, a place to execute code, and command references so you can either manually type them or use the command shortcuts to populate values and them insert them.

Those sharp-eyed among you might have noticed that when you loaded the ISE, it didn’t load your profile. That’s because the ISE has a separate $profile than the standard shell. You can still create one, but as a quick quiz I’m not going to give you the code right away; see if you can figure it out⁵!

A common habit to get into when starting to work with PowerShell is to just forgo the standard shell and always use the ISE to do you work. While that technically works, you’re much better off coding and developing and debugging in the ISE, but actually doing work in the shell. Write your scripts in the ISE, but use your scripts daily in the shell.

A note of caution before we continue

Much like your open management studio windows, when working with PowerShell be mindful of what server or computer you’re working on, and where you’re going to be connecting. Like T-SQL, PowerShell isn’t going to hold your hand while you execute something destructive; it’s just going to do what it’s told. If you have concerns about what’s going to be running where, you could create some Active Directory or Windows service accounts that don’t have the permissions that your account might already have, and then run the shell that way so nothing doesn’t happen where it’s not supposed to. For the purposes of this blog series, I recommend that everything you do be run locally, so load up a copy of developer edition or SQL Express on your machine. Once you get more comfortable you can start using more of these commands and methods in your non-production and maybe even production environments.

Ready… get set…

That’s about all we need to get our process started. In this post we learned how to launch powershell (and how not to), and how to create a profile for faster acting with the shell. With that in place, we’re ready to move onto exploring our main topic: backing up SQL Server Databases! Stay tuned!

1. http://dictionary.reference.com/browse/cattywampus
2. https://msdn.microsoft.com/en-us/library/dd878324%28v=vs.85%29.aspx?f=255&MSPPError=-2147217396
3. http://sev17.com/2010/07/10/making-a-sqlps-module/
4. https://technet.microsoft.com/en-us/library/hh849812.aspx
5. You’re going to do it the EXACT same way you did it in the standard shell, but if you type $profile in the ISE command window you’ll see it is looking for a different file