Welcome to Day 22 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

Yesterday we backed up all of our databases. However, what good is a backup if it can’t be restored? Now we’ll restore all those backups into a test database. This script will restore the most recent full backup for each database (into a database named “Verify”), moving all files to a separate directory to avoid trying to restore on top of the existing files. It will then restore the most recent differential backup, and then (if the database is not in the simple recovery model) all transaction log backups since the later of the full / differential backups. This script does assume that if the latest differential backup is more recent that the latest full, that it belongs to that full backup, and the script uses the paths where the databases were backed up to in yesterday’s backup script.

If you are utilizing SMO from SQL Server 2012, a new method has been added: Restore-SqlDatabase. This cmdlet has parameters to control all aspects of the restore.