Playing around with PowerShell for some tasks that we currently use Dynamic SQL for at work. Namely we have a few occasions when we need to backup all databases to disk then restore them all to either that box or a new one.

I wondered how many lines i could get it down to. (Pretty sure you can knock down the Restore to few less) but these work for me!

Im using the SQLPS module (Backup-SqlDatabase and Restore-SqlDatabase) so you’ll need to load that first.

Import-Module sqlps -ErrorAction SilentlyContinue

Backup all user databases using Backup-SqlDatabase

foreach ($db in (Get-ChildItem))
$dbname = $db.Name
Backup-SqlDatabase -Database $dbname -BackupFile "$dbname.bak"

Restore all user databases using Restore-SqlDatabase

$files = Get-ChildItem -Path "F:\Backups\" -filter *.bak

foreach ($f in $files)
$fs = $f.Name
$s = $fs.split(".")
Restore-SqlDatabase -ServerInstance . -Database $s[0] -BackupFile $f -ReplaceDatabase