Welcome to day -2 of 31 days of posts talking about using PowerShell to automate SQL Backup and Recovery.
Yes, it is day minus 2. I thought before I got onto the how tos and scripts, and to save wasting some of the 31 days I’d post some background information about why I’d use PowerShell for these tasks, how to set yourself up if you’re not already using PowerShell and also explain some of the assumptions I’ll be making in my scripts throughout this series.
So before we talk about the how, let’s think the why?
SQL Server database backups are pretty simple to setup and schedule. If you’re running a simple setup, then you can use the Maintenance Plan wizard to quickly get your databases backing up on a schedule. But how about if you want to do something not so simple? How about being able to choose where a particular database is being backed up to? Or catch newly created databases for a full backup during the day? And what can you do about scheduling SQL Express backups when it doesn’t come with SQL Server Agent? The scripts and ideas presented should help you to come up with simple solutions to achieve all this
We all know we should be regularly restoring our databases to make sure that our backups are valid, and that we have a proper idea of our Recovery Time Objective. But it’s not the most riveting part of our job, and SQL Server doesn’t offer any way of easily automating database restores without having to write scripts for each individual case. Using PowerShell and it’s SQLPS module you can easily write scripts that will cope all restore eventualities. This means you can automate the testing, and only have to intervene when a problem is found.
And once you can automate backup and restores it becomes easy to create scripts to migrate environments between servers, or restore production data into a dev environment or speed up creating Mirrored or AlwaysOn High Availability setups.
Tomorrow’s post will discuss the components needed to perform these backups – Day -1 of 31 days of SQL Server backup and Restore using Powershell: SQL Server Powershell Components
This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.
