PowerShell module to automate restoring SQL Server backups

PowerShellI’ve long been a proponent of automatically restoring as many of your SQL Server backups as you can. There are many benefits of doing this, amongst them:

  • Checking your backups are working, I’ve seen people with backups they can’t restore
  • Knowing how long it will take to recover, your boss isn’t going to be happy if they’re still waiting 3 hours after you said the business would be back up and running
  • Having logs of having restore them, Auditors love this sort of thing
  • Peace of mind knowing that your restores are going to work when the brown stuff hits the reciprocating draught machine.

I’ve been using a collection of scripts and functions based on my previous blogs and presentations on the subjects. But they’ve never really been in a state that I felt comfortable sharing with the world.

Until now:

I’ve spent some time rewriting them to comply with PowerShell best practice. Which means I can now present the SQLAutoRestores PowerShell module.

The first release version is now available on GitHub here – https://github.com/Stuart-Moore/SQLAutoRestores

I’ve put it on GitHub under the GPL v3 license to encourage people to have a play with it and potentially extend it. I’ve already added a number of planned enhancements to the Issues list, and if nothing will be working on them myself.

So why another solution for automatic SQL Sever restores?
Well, most of the existing ones were based in T-SQL and required the original SQL Server to be available to get the backup information. This meant they didn’t cope easily with restores that needed the database files moving, or easily building up the restores to get to a specific point in time.

This module’s basic aims were to provide the means to do the following:

  • Only require SQL Server backup files. All information about the database and the restore is taken from the backup files
  • Restore the backups to the server of the users choice, with the server only needing to have the correct version of SQL Server and enough disk space to cope with the restore.
  • Given a set of SQL Server backups pick a random one to restore
  • The module should check the SQL Server version and the disk space before failing
  • Given a set of backup files it should be able to work out the time span they cover to which the database can be restored
  • Having gotten that time span have the ability to restore to a random point in time in that span
  •  cope with easily relocating every file in the restored database to where the user wants them to go
  • Offer the option to produce the T-SQL scripts to repeat the install
  • Allow the user to check the database. This is useful for large production databases where running a full DBCC check during production hours may not be possible

I’m pleased to say that this initial release of the module covers all of the above.

I’ll follow up in the next couple of days with a blog post on what I see as best usage practices for this module

Leave a Reply

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