A very practical use for automated SQL Server backups and restores with PowerShell is setting up High Availability solutions. Usually these will involve bringing 2 or more SQL Server instances into sync with each other, the usual solution being backups and restores. SQL Server Mirroring may have been deprecated in SQL Server 2012 in favour of AlwaysOn Availability Groups, but I still see plenty of installs that haven’t migrated yet so it’s still heavily in use. To setup SQL Server mirroring successfully there are a number of conditions that must be fulfilled.

  • All SQL Server instances should be of the same version. While you can set up mirroing from lower version to a high version (ie; 2008 -> 2008R2), if you fail over you get a free database upgrade thrown in and can never mirror back.
  • All SQL Server instances should be of the same edition (Enterprise, Standard, etc). You can get away with this if you are NOT running any edition specific features, but the minute you enable an “Enterprise” feature then your database won’t start on any instance that doesn’t support that version
  • Instances should have the same start ‘Log Sequence Number’ in their active transaction log. This ensures that all the ‘new’ transaction transmitted in the intial sync are working from the same start point. Prior to SQL Server 2008R2 you could just restore a current backup, now you need to restore a full backup and at least 1 transaction log
  • There should be a valid TCP mirroring endpoint on each instance
  • There should be a valid SQL Login with permissions to access the above Endpoints

All of this we can handle within PowerShell to make our lives nice and easy. The following is going to assume that you have a shared folder which both SQL Server instances have access to. If not, then you’ll just need to use Copy-Item cmdlet to manually copy the backup files across. We’re also going to assume that both instances are running under the same domain accounts so there’s no need to grant permissions on the endpoints

Import-Module SQPS -DisableNameChecking

#Setup some basic variables
$ServerPrimaryName = "Server1"
$ServerSecondaryName = "Server2"

#Let's do multiple databases
$Databases = ("db1","ProdDB2", "MirrorDB1")

#Both SQL Server instances should have read/write to here
$FileShare = "\\FileServer\DBShare\"

#Get all dbs onto the same log:
foreach($db in $Databases){
    Backup-SQLDatabase -Database $db -ServerInstance $ServerPrimaryName -BackupFile $FileShare+$db+"_full.bak"
    Backup-SQLDatabase -Database $db -ServerInstance $ServerPrimaryName -BackupFile $FileShare+$db+"_log.trn"
    Restore-SQLDatabase -Database $db -ServerInstance $ServerPrimaryName -BackupFile $FileShare+$db+"_full.bak" -NoRecovery
    Restore-SQLDatabase -Database $db -ServerInstance $ServerPrimaryName -BackupFile $FileShare+$db+"_log.trn" -NoRecovery
}

#Now we need to create a TCP Mirroring EndPoint on each Server
$ServerPrimary = New-Item $ServerPrimaryName
$ServerSecondary = New-Item $ServerSecondaryName
$EPName = "DBMirror-PS"
$EPPort = 7022

        $PrimaryEP = new-object ('Microsoft.SqlServer.Management.Smo.EndPoint -Argument $ServerPrimary, $EPName
        $PrimaryEP.ProtocolType = [Microsoft.SqlServer.Management.Smo.ProtocolType]::Tcp
        $PrimaryEP.EndpointType = [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring
        $PPrimaryEP.Protocol.Tcp.ListenerPort = $EPPort
        $PrimaryEP.Payload.DatabaseMirroring.ServerMirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::Partner
        $PPrimaryEP.Create()
        $PrimaryEP.Start()

        $SecondaryEP  = new-object ('Microsoft.SqlServer.Management.Smo.EndPoint -Argument $ServerPrimary, $EPName
        $SecondaryEP.ProtocolType = [Microsoft.SqlServer.Management.Smo.ProtocolType]::Tcp
        $SecondaryEP.EndpointType = [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring
        $SecondaryEP.Protocol.Tcp.ListenerPort = $EPPort
        $SecondaryEP.Payload.DatabaseMirroring.ServerMirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::Partner
        $SecondaryEP.Create()
        $SecondaryEP.Start()

foreach ($db in $Databases){
    $ServerPrimary.Databases.item($db).MirroringPartner = "TCP://"+$ServerPrimary.NetName+":"+$EPPort
    $ServerPrimary.Databases.item($db).alter()
    $ServerSecondary.Databases.item($db).MirroringPartner = "TCP://"+$ServerSecondary.NetName+":"+$EPPort
    $ServerSecondary.Databases.item($db).alter()
}

As you can see this is pretty simple, and easily reusable whenever you need to set DB Mirroring up. For this example we entered the database names explicitly, but this could be easily modified to mirror every database on an instance with the following changes:

Import-Module SQPS -DisableNameChecking

#Setup some basic variables
$ServerPrimaryName = "Server1"
$ServerSecondaryName = "Server2"
$ServerPrimary = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($ServerPrimaryName)
$ServerSecondary = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($ServerSecondaryName)

#Load all the databases in an instance
$Databases = $ServerPrimary.databases

#Both SQL Server instances should have read/write to here
$FileShare = "\\FileServer\DBShare\"

#Get all dbs onto the same log:
foreach($db in $Databases){
    Backup-SQLDatabase -Database $db -ServerInstance $ServerPrimaryName -BackupFile $FileShare+$db.name+"_full.bak"
    Backup-SQLDatabase -Database $db -ServerInstance $ServerPrimaryName -BackupFile $FileShare+$db.name+"_log.trn"
    Restore-SQLDatabase -Database $db -ServerInstance $ServerPrimaryName -BackupFile $FileShare+$db.name+"_full.bak" -NoRecovery
    Restore-SQLDatabase -Database $db -ServerInstance $ServerPrimaryName -BackupFile $FileShare+$db.name+"_log.trn" -NoRecovery
}

#Now we need to create a TCP Mirroring EndPoint on each Server

$EPName = "DBMirror-PS"
$EPPort = 7022

        $PrimaryEP = New-Object -TypeName Microsoft.SqlServer.Management.Smo.EndPoint($ServerPrimary, $EPName)
        $PrimaryEP.ProtocolType = [Microsoft.SqlServer.Management.Smo.ProtocolType]::Tcp
        $PrimaryEP.EndpointType = [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring
        $PPrimaryEP.Protocol.Tcp.ListenerPort = $EPPort
        $PrimaryEP.Payload.DatabaseMirroring.ServerMirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::Partner
        $PPrimaryEP.Create()
        $PrimaryEP.Start()

        $SecondaryEP  = New-Object -TypeName Microsoft.SqlServer.Management.Smo.EndPoint($ServerPrimary, $EPName)
        $SecondaryEP.ProtocolType = [Microsoft.SqlServer.Management.Smo.ProtocolType]::Tcp
        $SecondaryEP.EndpointType = [Microsoft.SqlServer.Management.Smo.EndpointType]::DatabaseMirroring
        $SecondaryEP.Protocol.Tcp.ListenerPort = $EPPort
        $SecondaryEP.Payload.DatabaseMirroring.ServerMirroringRole = [Microsoft.SqlServer.Management.Smo.ServerMirroringRole]::Partner
        $SecondaryEP.Create()
        $SecondaryEP.Start()

foreach ($db in $Databases){
    $db.MirroringPartner = "TCP://"+$ServerPrimary.NetName+":"+$EPPort
    $$db.alter()
    $ServerSecondary.Databases.item($db.name).MirroringPartner = "TCP://"+$ServerSecondary.NetName+":"+$EPPort
    $ServerSecondary.Databases.item($db.name).alter()
}

And this certainly speeds things up if you need to set up a large number of mirror dbs in one fell swoop

This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.