#####################################################################################
# mssql_backup.ps1 | script by Jaky, 2011 | jakab.zsolt@codespring.ro | jaky@upm.ro #
#####################################################################################
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
# We Need SmoExtended for smo.backup / options like BackupActionType belongs to this assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

 $server = "db1_server"
# create a new server object with the apropiate service instance : "COMPUTERNAME\MSSQLINSTANCE"
 $so = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQLEXPRESS"
 $bkdir = "c:\local_backup\" + $server + "\"  # local folder for backup process db-files output
 $logfile = $bkdir + "sql_backup.log"  #  local file for backup process log-output
 $bkshare = "\\remote_backup\WindowsBackup\MsSQLdb\" + $server
 $remotepath = $bkshare + "\"  # need a backslash for copy-item command to succeed
# Mounting the remote share with apropiate permissions
 net use $bkshare userPassword /user:DOMAIN.LOCAL\BACKUP_USR 
 $dbs = $so.Databases

foreach ($db in $dbs)  {   # Using a cycle to dump each database in a separate backup file
 if($db.Name -ne "tempdb") # We don't want to backup the tempdb database 
   { # setting the properties for databases, creating for each a new instances of SMO.Backup object
	$dbname = $db.Name
	$datetime = get-date -format yyyyMMddHHmmss #We use this to create a file name based on the timestamp
	$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
	$dbBackup.Action = "Database" 
	#BackupAction Type specifies the type of backup. Options are: Database, Files, Log
	$dbBackup.Database = $dbname
	$dbBackup.BackupSetDescription = "Full Backup of " + $dbName
	$dbBackup.BackupSetName = $dbName + " Backup"
	$localfile = $bkdir + $dbname + "_db_" + $datetime + ".bak"
	$dbBackup.Devices.AddDevice($localfile, "File")
	$dbBackup.MediaDescription = "Disk"	
	$dbBackup.SqlBackup($so)  # The actual db-backup = calling the SqlBackup method of the Backup object
	write-Host "Database : $dbname -> backup to localfile : $localfile" 
	copy-item $localfile $remotepath
  } # end if
     $outstr = $dbname + "_db_" + $datetime + ".bak --> successfull backup of < " + $dbname + " > from server: " + $server
     $outstr  | Out-File -Append $logfile 
 } #end foreach
 
 write-Host "Appending timestamp information to log (logfile) :  $logfile" # ouput for testing purpose
 $dat = get-date -format yyyy-MM-dd-HHmmss
 $outstr = "------------- backup operations end / time: " + $dat + "-----------------" 
 $outstr  | Out-File -Append $logfile
 copy-item $logfile $remotepath  # Copy the logfile to the remote share
 net use $bkshare /delete 	# Unmount the remote folder