Continuing my pre 31 Days  of SQL Server backup and Restore using Powershell into, today we’ll look at getting the PowerShell SQL Server components, and a quick mention of GUIs

All Microsoft operating systems since Windows XP have shipped with PowerShell either installed or as an option. At the time of writing the current version is 3.0 (Download PowerShell v3.0). (PowerShell 4.0 is currently in public beta, but won’t be covered in this series)

Version 3 introduced enough new features to make it worthwhile upgrading to, especially for automating jobs. These include:

  • Better Scheduled Job Integration
  • Improved Integrate Scripting Environment
  • Workflows

A basic install of PowerShell doesn’t include the SQL PowerShell module which allows you to access the SQL Management Objects. These are installed when you install the client tools from your SQL Server media. They are also now freely available as part of SQL Server 2012 Express Service Pack 1 (download here).

The SMO capabilities are the same across the PowerShell module in each SQL Server edition, the limitations in usage are controlled by the edition of SQL Server you connect to. This means you can install the free SQL Express version on your desktop to control your Enterprise edition Servers, and that scripts developed on Express will also work with Standard and Enterprise editions.

SMO also relies on  standard T-SQL calls, so as long as the version of SQL Server supports the functionality you can use it. For instance, all the scripts in this series will work back to SQL Server 2005 (and the basic backups will also work backwards to SQL Server 7 and 2000). But just because PowerShell support AlwaysOn availability groups, it doesn’t mean you can enable them on SQL Server 2005

PowerShell 3.0 ships with a fairly usable  Integrated Scripting Environment GUI. This gives you Intellisense, some basic debugging functionality and an easier to read interface to Get-Help

If you start developing more complex PowerShell scripts then a more fully featured IDE may be preferable. There are a number available, both free and paid for. Commonly used ones are:

Which one you prefer will mostly come down to personal preference, so I’d suggest trying them all and then choosing one.

Tomorrow I’ll lay out some of the reasons why I’ve moved away from T-SQL for a lot of my backup and restore automation.

This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.