We’re often asked for some hints for a SQL Server Backup Script. The bare bones of one is shown in this post. This script has the following steps:
1. Remove all previous backup items more than 10 days old
2. Remove the current backup
3. Run the backup
4. Copy the backup to a datestamped file, so that multiple backups exist.
5. Compress the backup file
===================================================
# Step 1 – Remove all items more than 10 days old in the backup media
Add-Type -Assembly “System.IO.Compression.FileSystem” ;
$limit = (Get-Date).AddDays(-10)
$path = “G:”
Get-ChildItem -Path $path -Attributes !Directory+!System -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item -Force
# Step 2 – Remove previous mydatabasename.bak and zip file
Remove-Item “G:\Current\mydatabasename.*” -Recurse
Remove-Item “G:\mydatabasename.*” -Recurse
# Step 3 – Run the backup
#Sqlcmd -S 127.0.0.1 -U myusername -P mypassword -Q “BACKUP DATABASE mydatabasename TO EXTERNALDISK”
$backupFolder = ‘…’
$additionToName = ‘…’
$user = myusername
$pass = ‘mypassword’
$inst = ‘127.0.0.1’
$db = ‘mydatabasename’
$file = “$backupFolder${db}_db_$additionToName.bak”
$sql = @”
USE $db;
GO
BACKUP DATABASE $db TO EXTERNALDISK;
GO
“@
Invoke-Sqlcmd -QueryTimeout 3600 -Query $sql -ServerInstance $inst –Username $user –Password $pass
# Step 4 – Copy the backup to a date-stamped filename
$fileName = “G:\Current\mydatabasename.bak”
$fileObj = get-item $fileName
# Get the date
$DateStamp = get-date -uformat “%Y-%m-%d@%H-%M-%S”
$extOnly = $fileObj.extension
if ($extOnly.length -eq 0) {
$nameOnly = $fileObj.Name
copy-item “$fileObj” “$nameOnly-$DateStamp”
}
else {
$nameOnly = $fileObj.Name.Replace( $fileObj.Extension,”)
copy-item “$fileName” “G:\$nameOnly-$DateStamp$extOnly”
}
# Step 5 – Compress the backup file
[System.IO.Compression.ZipFile]::CreateFromDirectory(“G:\Current”, “G:\mydatabasename.zip”) ;
[Environment]::Exit(1)
