Restore All Databases With Most Recent Backup

This is a bit of a scary task.  Someone tells you about 87 databases that all need to be restored from the most recent FULL backups. You can either spend the rest of your day pointing and clicking, or you can use some PowerShell power to crank out the script and get on with your day. Here is the PowerShell way.

I don't like to fire-off the actual backups from PowerShell, rather I just use it to script out the restore script.  So, this PowerShell script will output the T-SQL restore script:

(See caution below before running this!)

Cls
## Change the starting directory to the parent folder that contains each database's backups
cd M:\Backup\Folder\Parent

$dirs = Get-ChildItem|Select FullName
foreach($dir in $dirs)`
{
	$file_name = Get-ChildItem -Path $dir.FullName|WHERE-OBJECT {$_.Name -like "*.bak"} | Sort-Object LastAccessTime -Descending | Select-Object -First 1 FullName;
	
	$parent_dir = Split-Path $dir.FullName -leaf
	write-host "USE [master]`
				RESTORE DATABASE "$parent_dir" FROM  `
				DISK = N'" $file_name.FullName "' `
				WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

GO
"
}

After that runs, you will have code that can be pasted into a SQL query window and will restore EVERY database on the instance.

Caution!!!

A requirement for this to function properly is that your folder structure must be such that every database is given its own sub-directory. This is something that is automatic if your maintenance plan is set to do so. Make sure that you do not have your system databases (master, msdb, model) in that folder. If you do then be sure to remove those restore scripts from the output or you could cause some damage.