Powershell: backup and compress SQL databases

The following Powershell script code backups all databases for a specific SQL Server and then compress those files (*.bak) into a ZIP file.

The last line is very important, because it lists the “.bak” files in the directory, ordered by length (ascending), so the compression process doesn’t get interrupted.

Lately, you can call this script from a BAT file and set it up as a scheduled task, so, let’s say, backup all your databases from your server everyday at midnigth, and compress them to a zipped file. Next morning when you arrive to your office, you can download it. Even more, you can schedule it to download it automatically to an FTP server at your data center.

$assemblyInfo = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
if ($assemblyInfo.GetName().Version.Major -ge 10)
# sql server version is 2008 or later, also load these other assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | Out-Null

$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") "(local)" #this can also be "SERVERNAME\INSTANCENAME"

$bkdir = "E:\Backups" #We define the folder path as a variable
$dbstobackup = @(“DB1", “DB2", “DB3")

$dbs = $s.Databases
foreach ($db in $dbs)
if($dbstobackup -contains $db.Name)
$dbname = $db.Name
$dt = get-date -format yyyyMMddHHmm
$dbBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup")
$dbBackup.Action = "Database"
$dbBackup.Database = $dbname
$dbBackup.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", "File")

# Create a new zip file from pipeline
function Create-Zip()

New-Zip -zipfileName $zipFile
$zip = Get-Zip -zipfileName $zipFile

#loop through files in pipeline
foreach($file in $input)
#add file to zip and sleep 1/2 second
Start-sleep -milliseconds 15000


#create a new zip file
function New-Zip
set-content $zipfilename ("PK" + [char]5 + [char]6 + ("$([char]0)" * 18))
(dir $zipfilename).IsReadOnly = $false

#get the zip file
function Get-Zip
$shellApplication = new-object -com shell.application
$zipFullFilename = (get-childitem $zipfilename).FullName

$dtzip = get-date -format yyyyMMddHHmm
$zipfilename = $bkdir + "\DB_Backups_" + $dtzip + ".zip"
Get-ChildItem -path $bkdir -filter *.bak* | Sort-Object length | create-zip $zipfilename


Backup SQL Server Databases with a Windows PowerShell Script

Loading SMO Assemblies into PowerShell

PowerShell Tips


3 responses to this post.

  1. In many cases, a person will reach a point where they
    desperately seek relief and solutions for their issues.
    There are approximately 17 million people in the US that suffer from some type of yeast infection.

    licitations with regards to making ones cachet
    that is most certainly Talbots at present.
    , one of the chiropractors in Fayetteville, is pleased to announce
    the implementation of programs for natural detoxification therapy.
    The delivery of Linda Mc – Cartney sausage rolls package
    is usually in frozen form.


  2. Posted by Cornell on 6 marzo, 2013 at 14:38

    At that time, I was a very ordinary promoter at a small company, of course, I earned not much
    money which was just enough to support myself. Unlike many other
    medical facilities, the Fayette Chiropractic Center offers walk-in therapy.
    Thanksgiving day is coming, I should buy a thanksgiving gift for Linda.
    Yet again, new advances and engineering make these
    procedures rapid and helpful, and this cuts down on the value tremendously.
    You must be introduced with a display screen with several alternatives
    one particular of which will be safe mode with networking.



Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )


Conectando a %s

A %d blogueros les gusta esto: