Performing backups of a database with Powershell

This article will show some Powershell script to perform a backup of a database with Powershell. Generating backups of a database is not that hard with Powershell. A backup is done by using the Server Management Objects or SMO. This is an individual download that can be downloaded for SQL Server 2008 (R2) or SQL Server 2012. I have used SQL Server 2012 Express, and this script also runs with the Express version of SQL Server 2012. I had to download additional packages for SQL from here:

Microsoft SQL Server 2012 SP1 Feature Pack

Obtain the SharedManagementObjects MSI-file and the PowershellTools MSI file, I downloaded the x64 version, but if you have x86 version of SQL Server 2012, use that instead. In addition, there are equivalent packages for Microsoft SQL Server 2008 (R2), Google is your friend here.. Here is the script to perform a backup. I have created a function or cmdlet called Backup-Database. There is also a function called PreLoad-SmoAssemblies, which I have not used, but this can be used to PreLoad the entire SMO Library if you want an example of how to do this.

            
function PreLoad-SmoAssemblies(){            
 $smoAssemblies = "Microsoft.SqlServer.Management.Common",            
"Microsoft.SqlServer.Smo",            
"Microsoft.SqlServer.Dmf ",            
"Microsoft.SqlServer.Instapi ",            
"Microsoft.SqlServer.SqlWmiManagement ",            
"Microsoft.SqlServer.ConnectionInfo ",            
"Microsoft.SqlServer.SmoExtended ",            
"Microsoft.SqlServer.SqlTDiagM ",            
"Microsoft.SqlServer.SString ",            
"Microsoft.SqlServer.Management.RegisteredServers ",            
"Microsoft.SqlServer.Management.Sdk.Sfc ",            
"Microsoft.SqlServer.SqlEnum ",            
"Microsoft.SqlServer.RegSvrEnum ",            
"Microsoft.SqlServer.WmiEnum ",            
"Microsoft.SqlServer.ServiceBrokerEnum ",            
"Microsoft.SqlServer.ConnectionInfoExtended ",            
"Microsoft.SqlServer.Management.Collector ",            
"Microsoft.SqlServer.Management.CollectorEnum",            
"Microsoft.SqlServer.Management.Dac",            
"Microsoft.SqlServer.Management.DacEnum",            
"Microsoft.SqlServer.Management.Utility";            
             
 foreach ($assembly in $smoAssemblies){            
  [void][System.Reflection.Assembly]::LoadWithPartialName($assembly);               
 }            
            
}            
            
            
function Backup-Database($dbinstance, $dbname, $saveToLocation = "C:\backups\")            
{            
             
 [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             
            
 $dbserver = New-Object Microsoft.SqlServer.Management.Smo.Server $dbinstance            
 $bkup = New-Object Microsoft.SqlServer.Management.Smo.Backup             
 $bkup.Database = $dbname            
            
 $date = Get-Date             
 $date = $date -replace "\.", "-"             
 $date = $date -replace ":", "-"             
 $date = $date -replace " ", "-"             
            
 $file = $saveToLocation + $dbname + "_" + $date + ".bak"            
            
 $bkup.Devices.AddDevice($file, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)             
 $bkup.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database             
            
 $bkup.SqlBackup($dbinstance)            
            
 Write-Host "Backup of database $database performed at $(Get-Date) to file location $saveToLocation"            
            
 trap [Exception]             
 {            
  Write-Host $_.Exception.Message            
  break            
 }            
            
}            
            
$dbinstanceToUse = $env:COMPUTERNAME + "\SQLEXPRESS"            
$dbnameToUse = "TestDatabase"             
            
Backup-Database $dbinstanceToUse $dbnameToUse             

The function or cmdlet Backup-Database takes three parameters. The $dbinstance is the computer name - db instance name to use for the $dbserver inside the function. The $dbname is the name of the database, while $saveToLocation is the location where to save the .bak backup file. Each .bak file will get a name of $dbname concatenated with a date stamp with the .bak extension in the end of the file name. The file location can be specified, but will default to c:\backups. Obviously, you want to either specify this or make sure that the location c:\backup exists first. The script can be adjusted by using Test-Path to make sure that the folder exists first.

This shows how Powershell can be used for a wide variety of tasks, such as performing a backup of a SQL server database.

Eventlog and Powershell

Powershell can use the EventLog from the Powershell command line easily. This article will quickly display some Powershell script for handling the Eventlog:

new-eventlog -logname Application -Source MyCoolPowershellLog            
write-eventlog -Source MyCoolPowershellLog -EventId 0001 -Message "This is an
event registered by Powershell" -EntryType Information            
write-eventlog -LogName Application -Source MyCoolPowershellLog -EventId 0001 -Message "This is an event registered by Powershell" -EntryType Information            
get-eventlog -logname Application -Newest 10

To create a new source, we use the new-eventlog cmdlet and specify the logname, set here to Application. To create a new event, we use write-eventlog cmdlet. Here we supply the LogName, Source, EventId, Message and EntryType. EntryType can be Information, Warning and Error. To get the content in the eventlog, we can use the get-eventlog cmdlet.

Finally, to remove the Eventlog Source, use:

remove-eventlog -source MyCoolPowershellLog

Make note, this will delete the eventlog source, but not its already recorded events. Now, new events can be written to this eventlog source. Actually the old events of the eventlog source still exists on the system, as you can't remove events themselves from an eventlog source, as is the convention.

Using Powershell to perform automatic Hg Bisect

The process of bisection in the source control system Mercurial or Hg is done with the command Hg Bisect. The Hg Bisect command is usually run manually and consists of manual steps done by the user where the developer running the hg bisect command looks for a certain condition should hold true and mark each revision as either good or bad. This is done in a binary search result manner, which quickly narrows down the number of revisions to look at by half for each iteration. The goal is to find the first revision containing a bad feature. An automatic function for finding such bad features will now be presented. Here is the Powershell cmdlet or function followed by an example of a call to this function:

function Get-HgChangeSet(            
){            
    param(            
    [Parameter(Mandatory=$true)]            
    [scriptblock]             
    $test,             
            
    [Parameter()]            
    $good = 0,            
            
    [Parameter()]            
    $bad = 'tip'             
    )            
            
    hg bisect --reset;            
    hg bisect --bad $bad;             
    (hg bisect --good $good) | out-null;             
            
    while ($output -notmatch 'The first bad revision is'){            
        $result = & $test;             
            
        if ($result){            
            $output = (hg bisect --good ) | out-string;             
        }            
        else {            
            $output = (hg bisect --bad) | out-string;             
        }            
    }            
            
    $output;             
}            
            
Push-Location 'C:\toaurs-he\demorepo\'             
Get-HgChangeSet { !(Test-Path 'test.txt') -or (Get-Content test.txt) -notmatch 'ultrabad' }            
Pop-Location

In this example, a simple demo repository has a file called test.txt. I want to find the first revision where the text ultrabad was inserted. The truth condition then, is that either the file does not exist (yet) in a revision, or that the file exists and does not match ultrabad. This is a simple example, but it shows how one can search for a given text or source code by specifying this in the script block passed to the function or cmdlet Get-HgChangeSet. When the script is run, it finds the first occurence of the text ultrabad (which is bad) at revision number 8.

PS C:\toaurs-he\demorepo[ default ]> 
Hit Line breakpoint on 'C:\Users\Tore Aurstad\Documents\WindowsPowerShell\Scripts\Hg\BisectTool.ps1:22'
PS C:\toaurs-he\demorepo[ default ]> 
The first bad revision is:
changeset:   8:25be1d61e90d
user:        Tore Aurstad 
date:        Tue Apr 02 22:09:15 2013 +0200
summary:     foo 4

So there you have it, an automatic way via a Powershell script to find an introduction of a bad feature in a Hg repository with the aid of a Powershell function or cmdlet. To use this script, it is important to understand that the passed in first argument is a truth condition. It will usually always be necessary to accept if the file to test does not exist yet combined with and -or condition and then specify -notmatch and the text or source code which is not desired. This will usually be more precise than running hg bisect manually, which is kind of tedious still.

Also note that the second and third parameter is set to $good equals 0 and $bad equals 'tip'. In Mercurial or Hg, 'tip' is the newest revision. Usually, for a repository with many revisions, it will be quicker if the values for $good and $bad is specified in a well-known range, say revision 5000 to 5500 or what have you.. This is just another example of how scripting can lighten our daily workload as developers.