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!