Using Powershell SQL Database Backup and Compression

Recently I have been working with Powershell, alot. My goal is to create powershell scripts to replace the maintenance plans we have at work. I know maintenance plans, yuck! But they are better than having nothing at all.

Anyway, I wanted to demonstrate a really cool feature only recently add to SQL server in 2008 version. You may have read about database compression before but I am going to show you some hard numbers and let you decide if this is a feature you want to start using.

This script is from SQLvarient aka, Aaron Nelson’s ( blog | twitter ) powershell presentations.

Using this script:

##################################################################################
## This script backs up all user databases
## The orginial code for this came from Aaron Nelson @SQLvarient
## Modified 9/25/2010
## By AJ Mendo @SQLAJ
##################################################################################

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMOExtended”) | out-null
# define the instance name
$instance = ‘Server_Name’
$s = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $instance

#We define the folder path as a variable
$bkdir = “D:\Backup”
$dbs = $s.Databases
foreach ($db in $dbs)
{
if($db.IsSystemObject -eq $False) #We don’t want to backup the tempdb database
{
$dbname = $db.Name
$dt = get-date -format yyyy_MM_dd_HH_mm #We use this to create a file name based on the timestamp
$dbBackup = new-object (“Microsoft.SqlServer.Management.Smo.Backup”)
$dbBackup.Action = “Database”
$dbBackup.Database = $dbname
$dbBackup.Devices.AddDevice($bkdir + “\” + $dbname + “_db_” + $dt + “.bak”, “File”)
$dbBackup.SqlBackup($s)
}
}

Here is a screen shot of the size of the files after we run this script.

Now we will add the compression Property to the script.

If you add this line:

$dbBackup.CompressionOption = “1”

above the last line in the code

$dbBackup.SqlBackup($s)

Then run the code again and look at the sizes of the databases now.

Much smaller like 25% smaller. Now that is impressive and can save on disc space.

Hopefully you can or already use this feature on your SQL Server 2008 or 2oo8 R2.

Cheers!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s