$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.
Great to hear that the PowerShell article was helpful to you.
ReplyDeleteWhen you said SQL mirrors, are you referring to SQL Server database mirroring?
Yes, sorry for the slang... I was referring to SQL Server database mirroring.
ReplyDelete