$bkdir = "\\serverName\Shared\Temp" # Set Backup Path! (optional "C:\Temp")
if ((test-path $bkdir) -eq $false ) # Verify folder else create it...
{
[IO.Directory]::CreateDirectory($bkdir)
}
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | out-null
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instance
$dbs = $s.Databases
foreach ($db in $dbs)
{
if(($db.Name -ne "tempdb") -and ($db.Name -ne "master") -and ($db.Name -ne "model") -and ($db.Name -ne "msdb"))
{
$dbname = $db.Name
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$dbBackup.Action = "Database"
$dbBackup.Database = $dbname
$dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + ".bak", "File")
$dbBackup.SqlBackup($s)
write-host($db.name + " has been backed up.")
}
}
If you are saving to a network location, the SQL SA account and the person running the script need to have read/write permissions to the location.Update: (03/05/2012)
Added save location verification else create folder.