Skip to content

Instantly share code, notes, and snippets.

@AdamLJohnson
Created February 9, 2016 17:57
Show Gist options
  • Save AdamLJohnson/c336bb6d28131f8fc2aa to your computer and use it in GitHub Desktop.
Save AdamLJohnson/c336bb6d28131f8fc2aa to your computer and use it in GitHub Desktop.
Powershell script to backup all SQL Databases on a server. Useful for SQL Express.
Following the directions here (http://ss64.com/ps/syntax-run.html) in an administrator powershell execute:
Set-ExecutionPolicy RemoteSigned
Create a Scheduled task:
Program/script box enter "PowerShell
Add arguments (optional) box enter the value ".\FullDBBackup.ps1 | Out-File <PATH TO LOG>log.txt -Append"
Start in (optional) box, add the location of the folder that contains your PowerShell script
$serverName = ".\SQLExpress"
$backupDirectory = "D:\backupSQL"
$daysToStoreDailyBackups = 7
$daysToStoreWeeklyBackups = 28
$monthsToStoreMonthlyBackups = 3
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[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
$mySrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$mySrvConn.ServerInstance=$serverName
$mySrvConn.LoginSecure = $false
$mySrvConn.Login = "USERNAME"
$mySrvConn.Password = "PASSWORD"
$server = new-object Microsoft.SqlServer.Management.SMO.Server($mySrvConn)
$dbs = $server.Databases
$startDate = (Get-Date)
"$startDate"
Get-ChildItem "$backupDirectory\*_daily.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreDailyBackups)} |% {Remove-Item $_ -force }
"removed all previous daily backups older than $daysToStoreDailyBackups days"
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
{
$dbName = $database.Name
$timestamp = Get-Date -format yyyy-MM-dd-HHmmss
$targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_daily.bak"
$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($targetPath, "File")
$smoBackup.SqlBackup($server)
"backed up $dbName ($serverName) to $targetPath"
}
if([Int] (Get-Date).DayOfWeek -eq 0)
{
Get-ChildItem "$backupDirectory\*_weekly.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreWeeklyBackups)} |% {Remove-Item $_ -force }
"removed all previous daily backups older than $daysToStoreWeeklyBackups days"
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
{
$dbName = $database.Name
$timestamp = Get-Date -format yyyy-MM-dd-HHmmss
$targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_weekly.bak"
$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($targetPath, "File")
$smoBackup.SqlBackup($server)
"backed up $dbName ($serverName) to $targetPath"
}
}
if([Int] (Get-Date).Day -eq 1)
{
Get-ChildItem "$backupDirectory\*_monthly.bak" |? { $_.lastwritetime -le (Get-Date).AddMonths(-$monthsToStoreMonthlyBackups)} |% {Remove-Item $_ -force }
"removed all previous monthly backups older than $monthsToStoreMonthlyBackups days"
foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
{
$dbName = $database.Name
$timestamp = Get-Date -format yyyy-MM-dd-HHmmss
$targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_monthly.bak"
$smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full Backup of " + $dbName
$smoBackup.BackupSetName = $dbName + " Backup"
$smoBackup.Database = $dbName
$smoBackup.MediaDescription = "Disk"
$smoBackup.Devices.AddDevice($targetPath, "File")
$smoBackup.SqlBackup($server)
"backed up $dbName ($serverName) to $targetPath"
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment