Monthly Archives: October 2012

Powershell SQL Backup with Event Handler

A persistent problem I’ve had is capturing the output messages when executing SQL procedures. That is, until I recently learned how to capture those messages for output using an event handler.

The example function, provided below, executes an SQL Backup, uses an event handler to capture the output messages, and passes the information back to the calling routine.

Function Backup-SqlDatabase
<#
.SYNOPSIS
    Backs up an sql database to local or remote disk.

.DESCRIPTION
    This script will perform a full back up of the specified database to a local or network disk.

.PARAMETER  Server
    Specifies the sqlserver or instance name you wish to connect to and restore.

.PARAMETER Database
    Specifies the Database name you wish to connect to and backup.

.PARAMETER Path
    Specifies the path to the back up file.

.EXAMPLE
    Run-SqlBackup -Server "Server\MySql" -Database "MyDb" `
		-Path "\\server\D$\Backups\MySql.bak"
    Backs up database "MyDb" on SQL Server instance "Server\MySql" to a file on
	a network share.
.NOTES
	When backing up to a network share, permissions must be set appropriately 
	for the SQL Service Account.
#>
{
	[CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)]
        [string]$Server,
        [Parameter(Mandatory=$true)]
        [string]$Database,
        [parameter(Mandatory=$true)]
        [string]$Path
    )
	$output = ""
	$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
		param($sender, $event) 
	   	$output += $event.Message;
	} 
    $SQLConn = New-Object System.Data.SQLClient.SQLConnection
    $SQLConn.ConnectionString = "Server=$Server; Trusted_Connection=True"
    Try 
	{
		$SQLConn.add_InfoMessage($handler); 
		$SQLConn.FireInfoMessageEventOnUserErrors = $false;
        $SQLConn.Open()
    }
    Catch 
	{
		Throw $_
    }

    Write-Verbose "Connected to server $server"
    $SQLCmd = New-Object System.Data.SQLClient.SQLCommand
    $SQLcmd = $SQLconn.CreateCommand()
    $sqlcmd.commandtimeout=0
    $SQLcmd.CommandText="BACKUP DATABASE [$database] 
		TO  DISK = N'$path' 
		WITH  DESCRIPTION = N'$database Full Backup', 
		NOFORMAT, NOINIT,  
		NAME = N'$database-Full Database Backup', 
		SKIP, NOREWIND, NOUNLOAD"
    Try
	{
        $SQLcmd.Executenonquery() | Out-Null
  	}
    Catch
	{
		Throw $_
    }
    Finally
	{
        $SQLconn.close()
    }

    $backup = new-object PSObject -Property @{
        Server = $Server;
        Database = $Database;
		BackupFile = $path;
		Messages = $output}
	Return $backup
}

Calling the Function

$path = "\\MyServer\G$\SQL Backups\MyDB.bak"
Backup-SqlDatabase -Server ThisServer -Database "MyDB" -Verbose -Path $path |
	Format-List Server,Database,Path,Messages

The output

Event Messages Captured

EventMessages Captured

Powershell Impersonation – the easy way

Have you ever had a script which runs great against multiple SQL instances but then you find it failing on a new instance?

I have a script which backs up all my SQL Server databases and then copies the local backups to warehouse storage. The script runs as a Windows Scheduled task on our storage server for each SQL Instance.

Usually when I get a new SQL Server, I just add a new Windows Scheduled Task on our storage server, adjust the param to point to the new server, and the script backs up all the databases on the server\instance. Great! However, I recently found a job in which the backups were failing consistently. Not good! . It was a fairly new “test” server so I probably did not pay as close attention to it as I should have when I first set it up.

The bottom line causing the problem The only reason I knew of the failures was because of having I set up the script to write and event log for any error. However,, it was one of those “catch-all” errors with little significant information. If you don’t know already, debugging a Powershell script executed via a scheduled task is no easy task. Error codes are not easy to get. Traces are not easy to get. All in all, this type of debugging is a real PITA.

What made this even more confusing is the script ran perfectly when run from the console. No problems! BTW, I have at least 40 of these tasks running each evening without error. After a few hours of putting in numerous traps and write event logs I finally found the reason it would only fail when run as a Windows Task. The real cause of the problem was the “Log on as” user for the scheduled task did not have access to the SQL Instance! My normal testing used MY Windows credentials which has complete access rights.

Say hello to my little friend, RUNAS!

When starting a Powershell session, the run-time system sets up the environment with your Windows credentials and uses them for access to all resources. In order to have an environment whereby you don’t have to login to Windows as another user, you can use the RUNAS command (in most circumstances). Using RUNAS, I was able to start a PowerGUI session in which my credentials were the same as those being used as the logon for the Windows Task. Now, I was able to thoroughly debug the script using breaks, traces, etc. I now have a desktop Icon which starts up PowerGUI as that user for whenever I need it. You can find out more about RUNAS here, and the command I am using looks similar to this:

C:\Windows\System32\runas.exe /user:Domain\Login "C:\Program Files (x86)\PowerGUI\ScriptEditor.exe"