Powershell script to take SQL database backup

PowerShell script to take SQL database backup.

PowerShell script to take SQL database backup. The script takes the backup of all the databases on the given SQL instance. You can schedule this on daily basis.

 

$LogTime = Get-Date -Format yyyy-MM-dd_hh-mm
$LogFile = ".\SQLBackUpPatch-$LogTime.rtf"

# Add SharePoint PowerShell Snapin

if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null ) {
    Add-PSSnapin Microsoft.SharePoint.Powershell
}

#Deleting any .rtf files in the scriptbase location
$FindRTFFile = Get-ChildItem $scriptBase\*.* -include *.rtf
if($FindRTFFile)
{
 foreach($file in $FindRTFFile)
  {
   remove-item $file
  }
}

start-transcript $logfile

$SQLInstance = read-host "Enter the SQL instance "
$BackupFolder = read-host "Enter the backup folder "

$tStamp = Get-Date -format yyyy_MM_dd_HHmmss

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLInstance
$dbs = New-Object Microsoft.SqlServer.Management.Smo.Database
$dbs = $srv.Databases

foreach ($Database in $dbs | where {$_.IsSystemObject -eq $False})
{
write-host $Database.name
$bk = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$bk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$bk.BackupSetName = $Database.Name + "_backup_" + $tStamp
$bk.Database = $Database.Name
$bk.CompressionOption = 1
$bk.MediaDescription = "Disk"
$bk.Devices.AddDevice($BackupFolder + "\" + $Database.Name + "_" + $tStamp + ".bak", "File")
TRY
{$bk.SqlBackup($srv)}
CATCH
{$Database.Name + " backup failed."
$_.Exception.Message}
}

stop-transcript