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