Day 0 of 31 days of SQL Server backup and Restore using Powershell: Why not T-SQL?

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.

Tagged ,

Leave a Reply

Your email address will not be published. Required fields are marked *