Category: 31 days of SQL Server Backup and Restore with PowerShell

Last day of introductory material, tomorrow will feature an actual example and some code, promise.

So why do I think that it’s better to step outside of SQL Server into the world of PowerShell for doing backups?

SQL Server Maintenance plans are pretty good for nice simple environments. A few clicks and 2 maintenance plans later you’ve got some nice scheduled jobs that back your database up once a day, and your transaction logs each hour. Excellent.

But what about if you want to treat your system databases (master, model, tempdb, msdb) differently? Ok, you create another set of maintenance plans. Those databases are pretty fixed (you hope!) so it’s not too much work.

But what if you have hundreds of databases in a single instance and you want to vary when their backups run to spare your IO system? You could build up a number of maintenance plans, but they’ll still need manual maintenance. How much easier would it be to have a set of simple scripts that so you can define rules such as ‘Backup first 10% of databases at 01:00’, ‘Backup second 10% of databases at 02:00’? Much less of your time will be spent on keeping on top maintaining maintenance plans, so you can get on with yet more of the fun parts of your role.

SQL Sever doesn’t even offer a ‘Maintenance Plan’ to help automate restoring your databases to check your backups, so you’re on your own from the start. If you’re only wanting to perform a very simple automated restore, then you could write some simple T-SQL and schedule it via SQL Server Agent. But this would be very hard to write so it could work with any database, or be able to catch unusual setups. You end up in the world of cursors and Dynamic SQL, and are limited by SQL Server’s limited access to the filesystem (assuming you’ve set your security up for security rather than ease of administration (and we all do that don’t we?)).

By using PowerShell we can create scripts that can loop through folders and build up complex restores, can access OS and filesystems to work out how best to layout files, or to abort cleanly if there’s a problem. And if we combine that with a database behind it containing schedules and information, then we can have an automated system that will cope with restoring any of our databases. So when management (or the auditors) ask me how I can be sure that our backups work and that my RTO estimates are accurate I can just pull the numbers out and show them.

Hopefully the next 31 days of posts and scripts will help you towards that goal as well.

Tomorrow, the first script!

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

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.