Automated Backups That Rock the SQL

PowerShell takes a mundane task like SQL backups and automates it.

• By Joshua Jones
• 12/14/2009

One of the very mundane, but extremely valuable tasks that any DBA must perform is the backup and restore of databases. In this post, we’ll look at a sample script that can backup databases on a given SQL Server.

The key to backing up databases via PowerShell is the backup class Microsoft.SqlServer.Management.Smo.Backup. This class gives you access to all the objects and properties related to SQL Server backups. Using this class you can backup entire databases, transaction logs, and filegroups. You can execute full or differential backups as well. Executing SQL Server backups with PowerShell can give administrators script based control over backups, which is helpful when combining the backup process with other, non-SQL Server oriented tasks (such as querying and storing server information via WMI).

First, let’s take a look at the code, and then we’ll break it down:

#AllDBBackup.ps1
#Performs a full backup on all user databases
#Accepts server name as an input parameter.
#Load the DLLs if not using SQLPS
[System.Reflection.Assembly]::LoadWithPartialName
  ('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName
  ('Microsoft.SqlServer.SmoExtended') | out-null
#input paramters
$servername = $args[0]
#Set variables
$server = new-object
  ('Microsoft.SqlServer.Management.Smo.Server') $servername
$databases = $server.Databases
$bkdir = $server.Settings.BackupDirectory
# Iterate through all databases and backup
# each user database
$databases | foreach-object {
  $db = $_
  if ($db.IsSystemObject -eq $False) {
    $dbname = $db.Name
    $dt = get-date -format yyyyMMddHHmmss
    $dbbk = new-object
      ('Microsoft.SqlServer.Management.Smo.Backup')
    $dbbk.Action = 'Database'
    $dbbk.BackupSetDescription = "FULL - " + $dbname
    $dbbk.BackupSetName = $dbname + " FULL Backup"
    $dbbk.Database = $dbname
    $dbbk.MediaDescription = "Disk"
    $dbbk.Devices.AddDevice($bkdir + "\"
      + $dbname + "_db_" + $dt + ".bak", 'File')
    $dbbk.SqlBackup($server)
    write-host "Backed up " $dbname " to "
      $bkdir  "\"  $dbname  "_db_"  $dt ".bak"
    }
  }
write-host "Backup Operation(s) Complete"

The first thing this script does is load the appropriate DLLs if you are NOT using SQLPS to execute the script. The out-null simply suppresses output. Then, we define the input parameter using $args[].

When using $args[] to bring in parameter values, the order of values mapped to parameters is defined in numerically ascending order. For example, if we have 3 input parameters, we would use $args[0], $args[1], and $args[2]. Then when we call the script, we must make sure to pass the values in the same order they are expected by the script: ./MyScript.ps1 val1 val2 val3. This will assign val1 to $args[0], val2 to args[1], and val3 to args[2].

After capturing the input value, we immediately place it in to a new variable for the rest of the script to use, $servername. Next, we define a few more internal variables:

• $server: This is the actual variable we will use later on. The $servername variable is used to instantiate the Microsoft.SqlServer.Management.Smo.Server class with the correct server name.
• $databases: Here we’re assigning an array variable; we’re using the .Databases property of the server object to enumerate and store the list of databases on the server.
• $bkdir: Finally, we’re going to capture the default backup directory using the Settings.BackupDirectory property.

Next, we use a foreach-object commandlet to loop through the databases in the $databases variable. We use the .IsSystemObject property to make sure we exclude system databases (you could include them or write a separate script). Then we create an assign a name variable for the database we’re getting ready to backup by using $db.Name. Then we grab the current system datetime ($dt = get-date etc.), and instantiate a backup object. Now we can script the action we’re taking (.Action) and setup the various parameters for a backup such as the set name and description. We also define that we are using a disk device, and create the device using .Devices.AddDevice. Finally, we execute the backup using .SqlBackup. In this script, we use write-host to provide some visual feedback; this is customizable, or you could even remove it altogether.

This code should provide an easy starting place for customizing your own scripts to manage backups. The next post will show you how to take a backup file and restore it to a database using PowerShell, completing the backup/restore lifecycle. Until then, have fun!