Use PowerShell to Backup All User Databases

This script will back up all user databases - you need to change the BWOODY1\SQL2K8 part to your server\instance name, and of course, you should only run this on a test system until you completely understand it.

Unfortunately, the SQL Server PowerShell Provider doesn't make this very easy - and there may be a better way to do this once I research it more.

Also - this script was adapted from one given to me earlier, and unfortunately I don't recall the source. If this is partly your work, please add a comment here for attribution:

 

 # Performs a Full backup followed by a transaction log backup on all user databases
  
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null
 $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'BWOODY1\SQL2K8'
 $bkdir = $s.Settings.BackupDirectory
 $dbs = $s.Databases
 $dbs | foreach-object {
     $db = $_
     
     if ($db.IsSystemObject -eq $False -and $db.IsMirroringEnabled -eq $False) {
         $dbname = $db.Name
         $dt = get-date -format yyyyMMddHHmmss
         $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
         $dbbk.Action = 'Database'
         $dbbk.BackupSetDescription = "Full backup of " + $dbname
         $dbbk.BackupSetName = $dbname + " Backup"
         $dbbk.Database = $dbname
         $dbbk.MediaDescription = "Disk"
         $dbbk.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", 'File')
         $dbbk.SqlBackup($s)
         if ($db.DatabaseOptions.RecoveryModel -ne 'Simple') {
             $dt = get-date -format yyyyMMddHHmmss
             $dbtrn = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
             $dbtrn.Action = 'Log'
             $dbtrn.BackupSetDescription = "Trans Log backup of " + $dbname
             $dbtrn.BackupSetName = $dbname + " Backup"
             $dbtrn.Database = $dbname
             $dbtrn.MediaDescription = "Disk"
             $dbtrn.Devices.AddDevice($bkdir + "\" + $dbname + "_tlog_" + $dt + ".trn", 'File')
             $dbtrn.SqlBackup($s)
             }
         }
  
     }