Blog Series: SQL Server Backup and Restores with PowerShell

Windows PowerShell is a bit of a bugaboo in the DBA world, and that’s not entirely without merit. A big part of that is the fact that SQL Server 2008 only lets you load snap-ins to the base PowerShell shell, and SQL Server 2012’s SQLPS uses Windows PowerShell 2.0, and it can be frustrating to get started with the language at all, let alone enhance or administer your instances if the code you write locally just won’t work remotely. There’s also a lot of consternation over the fact that some of the things you think should be pre-made cmdlets can be done with PowerShell, but it takes typing and some thought, and that’s too much for some people and they pooh-pooh it. Sometimes I can’t blame them.

That said, PowerShell is a phenomenal tool that all DBA’s should pick up. From configuring instances and automating tasks, and yes, even working with Windows Core servers there’s a lot of things you can do some of which you probably are relying on ridiculous T-SQL to accomplish already (stop that).

As you may have guessed from my other blog posts here, I’m a PowerShell nut, bordering on evangelist, and it’s my mission to get this awesome technology more into the wild for SQL Server professionals. There’s a lot of FUD out there already, but once you understand the basics of the PowerShell syntax and some of the nifty commands already available to you with the SQLPS module, you can make your life tremendously easy.

This will be a series of blog posts in which I will outline how you can manage your database backups and restores via PowerShell scripting. In the first post I will outline how we can set that up: how to use the SQLPS module, getting around in SQL server with PowerShell, and some awesome methods and functions that you can start using today. I will also outline a typical task common to all DBA’s: backing up your databases. I’ll outline a typical backup scenario, and about how you can use PowerShell to manage your backups, and post some code that you can test out for yourself. The third post will be more code, this time having to do with restoring databases using PowerShell and why you should never have problems keeping your non-production environments up to date. And finally, we’ll talk about that often overlooked task or verifying your backups as well.

This series has been a long time coming as it closely reflects some recent things I’ve done in my day job. I hope you get a lot of them, because I know I have. And as an added bonus, it can be one more thing you can do better than your system engineers!