Database scripting and powershell

So on a regular basis we find ourselves needing to restore customer data to our staging environment to test our releases on actual data. However doing this often requires a DBA to grab the most recent backup and manually restore it to the DB server. Once the restore is finished mirroring was configured and then our business intelligence staging environment was hooked up. If staging already had an old copy of production then the mirroring had be dissolved before the DB could be dropped or replaced. While this isn’t difficult its definitely time consuming. This will be the first part of a Series of blog post aimed at simplifying this process and, using powershell-foo, automate our process a little.

So now to the how of it. We have three actions we want to perform: Break a mirror and remove an existing database, Restore a new backup of a database, reconfigure mirroring.

Breaking our mirror

Before we can Delete our existing database or restore over it we need to break the mirror that exists. This is fairly straight forward.

Function DissolveMirror {
    Param(
        [parameter(Mandatory=$true)]
        [String[]]
        $Principle,
        [parameter(Mandatory=$true)]
        [String[]]
        $Mirror,
        [parameter(Mandatory=$true)]
        [String[]]
        $Witness,
        [parameter(Mandatory=$true)]
        [String[]]
        $DBName)
    [string]$instance = 'master'
    [string]$query
 #Create Handlers to show error messages that might occur.
    $SqlInfoMessageHandler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Host "$($_.Message)";}
    $Pconnection = new-object system.data.sqlclient.sqlconnection( `
        "Data Source=$principle;Initial Catalog=$instance;Integrated Security=SSPI;");
    $PConnection.add_InfoMessage($SqlInfoMessageHandler);
    $Pconnection.open()
    $Pcmd = $Pconnection.CreateCommand()
    
    $Mconnection = new-object system.data.sqlclient.sqlconnection( `
        "Data Source=$Mirror;Initial Catalog=$instance;Integrated Security=SSPI;");
    $MConnection.add_InfoMessage($SqlInfoMessageHandler);
    $Mconnection.open()
    $Mcmd = $Mconnection.CreateCommand()
    
    "*********************************"
    "Configuring Principle"
    "*********************************"
    $query = "ALTER DATABASE "+$DBName+"
                SET PARTNER off"
    $Pcmd.CommandText = $query
    $result = $Pcmd.ExecuteNonQuery()
    "*********************************"
    "Configuring Mirror"
    "*********************************"
    $query = "ALTER DATABASE "+$DBName+"
                SET PARTNER Off"
    $Mcmd.CommandText = $query
    $query = "restore database "+$DBName
    $Mcmd.CommandText = $query
    $result =$Mcmd.ExecuteNonQuery()
    "*********************************"
    "Mirroring Dissolved"
    "*********************************"
    $PConnection.Close() 
    $MConnection.Close() 
    }

Essentially, we are establishing a connection to each database. Then running the query needed to break the mirror on the target server based on that servers role in the quorum. For the primary this is simply setting the partner to off. On the mirror we need to pull the database out of its restoring state so we perform a restore database command to make it droppable. Once dissolved we close are connections and then we are done. The parameters for the function take the hostnames of the roles in your mirror and the database to be affected by the dissolve. Of course I haven't tested this without a Witness server but logic could easily be inserted to handle that specific case if needed. Handy And accomplishes the first step in our journey to automated restores!

Excel Files and SQL

It never seems to fail that a project will come about that will require someone to enter some data into a spreadsheet for some form of import in to your database. It also never seems to fail that no matter how many times you ask you end up with a xslx or some other form of office spreadsheet that isn't friendly with bulk insert. Sometimes you do get a csv but they created it oddly and the delimiters are wrong or not in the right places. This little script opens an excel sheet and converts it to a csv. Then reads each file in and line by line turns that into a select union script. We used this in aback end process to add data to users to our system.

function ConvertFrom-XLx {
  param ([parameter(             
        Mandatory=$true,
        ValueFromPipeline=$true,
        ValueFromPipelineByPropertyName=$true)]
        [string]$path ,
        [switch]$PassThru
        )

    begin { $objExcel = New-Object -ComObject Excel.Application }
        Process { 
                    if ((test-path $path) -and ( $path -match ".xl\w*$")) {
                        $path = (resolve-path -Path $path).path
                        $savePath = $path -replace ".xl\w*$",".csv"
                        $objworkbook=$objExcel.Workbooks.Open( $path)
                        $objworkbook.SaveAs($savePath,6) # 6 is the code for .CSV
                        $objworkbook.Close($false)
                        if ($PassThru) {Import-Csv -Path $savePath }
                        }
                    else {Write-Host "$path : not found"}
                }
    end  { $objExcel.Quit() }
}

dir *.xlsx | ConvertFrom-XLx
$command = 'ls *.csv'
$array =[string] @()
$array = ls -filter "*.csv"
$out = [string] @()
foreach ( $i in $array) {
   $Sql = [string] @()
   "processing file:" + $i
   $Count = 0
   $sql += "Select * from ( select null as Column1,null as Column2,null as Column3,null as Column4,null as Column5 "
   foreach ($a in Get-Content $i) {
            $sql +=" Union All"+ "`n"
            $count += 1
            $Values = "$a".split(',')
            $sql += "select" + "'"+$values[0] + "'"+ ","+ "'"+$values[1] + "'"+ ","+ "'"+$values[2] + "'"+ ","+ "'"+$values[3] + "'"+ ","+ "'"+$values[4] + "'" #+" Union All" + "`n`r"
    }
    $sql += ") q2 where Firstname is not null"
    $out = $sql
    "Sql for File:`n`r" + $out 
    $outfile = "$i".replace(".csv",".sql")
    $out | Out-File $outfile
}
}

Powershell Exception trapping

About three months ago I wrote a handy little powershell script utilizing a variety of functions and cmdlets to download a copy of our production data, restore it to a staging environment, cleanse PCI from the data and configure mirroring. At some point this script stopped working. Unfortunately the error messages regarding why it was not working were as follows:

Exception calling "KillDatabase" with "1" argument(s): "Kill database failed for Server 'DBServer.domain.com'. "
Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server
 'DBServer.domain.com'. "
At D:\scripts\StagingDBMigrate\DBActions.ps1:60 char:20
+     $res.SqlRestore <<<< ($srv)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Unfortunately, that doesn’t tell us a whole lot.This is the highest level exception and doesn’t give us much information. Only that the command failed. Here is a handy little piece of code that will help you find the innermost exception that caused your error and spit it out to the host

Trap {
  $error = $_.Exception
  while ( $error.InnerException )
    {
    $error = $error.InnerException
    write-output $error.Message
    };
    continue
  }

This piece of code will loop through the exception chain until it gets to what should be the root cause of the failure, printing the trace along the way. This turned our previously arcane error message into a cleanly understandable error that looks something like this

Exception calling "KillDatabase" with "1" argument(s): "Kill database failed for Server 'DBServer.domain.com'. "
System.Data.SqlClient.SqlError: RESTORE cannot operate on database 'Database' because it is configured for database mirroring. Use ALTER DATABASE to remove mirroring if you intend to restore the database.

EDIT:Providing a more complete example:

Function foo {
    #Define Trap at top. Any exceptions within this function will automatically call this block
    Trap {
            Write-Host 'Caught an exception!' -ForegroundColor Red
            $error = $_.Exception
            write-Host $error.Message -ForegroundColor Red
            continue
        }
        $exception = [System.IO.IOException] "Dude, there's no file there!";

        Write-Host 'Before exception'
        throw $exception
        Write-Host 'After Exception'    
        }
#Call our Exception handling function! 
foo