Mirror-DB.ps1

param ([string]$SrcDBInstance, [string]$DestDBInstance, [string]$DBName)

#Functions
function Exec-Query ([string]$DBServer,[string]$DBName,[string]$Query) {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True"
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandTimeout = 0
	$SqlCmd.CommandText = $Query
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet) | Out-Null
    $SqlConnection.Close()
    $DataSet.Tables[0]
}

function Backup-FullBackup ([string]$DBServer, [string]$DBName, [string]$BakLoc) {
	if (Test-Path $BakLoc) {Remove-Item $BakLoc -Force | Out-Null}
	$script = ("BACKUP DATABASE [" + $DBName + "] TO DISK = N'" + $BakLoc + "' WITH NOFORMAT, NOINIT, NAME = N'" + $DBName + "_" + $(Get-Date -UFormat %Y%m%d) + "-Full', SKIP, NOREWIND, NOUNLOAD, STATS = 10")
	Set-RecoveryModel $DBServer $DBName "FULL"
	Exec-Query $DBServer "master" $script
}

function Backup-TranLog ([string]$DBServer, [string]$DBName, [string]$BakLoc) {
	if (Test-Path $BakLoc) {Remove-Item $BakLoc -Force | Out-Null}
	$script = ("BACKUP LOG [" + $DBName + "] TO DISK = N'" + $BakLoc + "' WITH NOFORMAT, NOINIT, NAME = N'" + $DBName + "_" + $(Get-Date -UFormat %Y%m%d) + "-Trans', SKIP, NOREWIND, NOUNLOAD, STATS = 10")
	Exec-Query $DBServer "master" $script
}

function Restore-FullBackup ([string]$DBServer, [string]$DBName, [string]$BakLoc) {
	$RestoreTo = Get-DfltLoc $DBServer

	foreach ($NameSpace in $Global:NameSpace) {
		switch ($NameSpace.file_type) {
			"mdf" {[string]$files += (", MOVE N'" + $NameSpace.name + "' TO N'" + ($RestoreTo.DefaultFile + "\" + $DBName) + ".mdf'")}
			"ndf" {[string]$files += (", MOVE N'" + $NameSpace.name + "' TO N'" + ($RestoreTo.DefaultFile + "\" + $DBName) + ".ndf'")}
			"ldf" {[string]$files += (", MOVE N'" + $NameSpace.name + "' TO N'" + ($RestoreTo.DefaultLog + "\" + $DBName) + ".ldf'")}
		}
	}

	$script = ("RESTORE DATABASE [" + $DBName + "] FROM DISK = N'" + $BakLoc + "' WITH FILE = 1" + $files +", NORECOVERY, NOUNLOAD, STATS = 10")
	Exec-Query $DBServer "master" $script
}

function Restore-TranLog ([string]$DBServer, [string]$DBName, [string]$BakLoc) {
	$script = ("RESTORE LOG [" + $DBName + "] FROM DISK = N'" + $BakLoc + "' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10")
	Exec-Query $DBServer "master" $script
}

function Get-DfltLoc ([string]$DBServer) {
	$script = "declare @SmoDefaultFile nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT declare @SmoDefaultLog nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]"
	Exec-Query $DBServer "master" $script
}

function Get-FileNamespace ([string]$DBServer, [string]$DBName) {
	$script = "SELECT LOWER(RIGHT(physical_name, 3)) AS file_type, name FROM SYS.DATABASE_FILES"
	Exec-Query $DBServer $DBName $script
}

function Set-RecoveryModel ([string]$DBServer, [string]$DBName, [string]$model) {
	$script = ("ALTER DATABASE " + $DBName + " SET RECOVERY " + $model)
	Exec-Query $DBServer "master" $script
}

function Check-Endpoint ([string]$DBServer) {
	$script = "SELECT * FROM SYS.DATABASE_MIRRORING_ENDPOINTS WITH (NOLOCK) WHERE NAME = 'HADR_ENDPOINT'"
	Exec-Query $DBServer "master" $script
}

function Create-Endpoint ([string]$DBServer) {
	$script = "CREATE ENDPOINT [HADR_ENDPOINT] STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM AES)"
	if (!(Check-Endpoint $DBServer)) {Exec-Query $DBServer "master" $script}
}

function Get-DBSvcUser ([string]$DBName) {
	$script = "DECLARE @SrvAccount varchar(100) set @SrvAccount ='' EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', N'ObjectName', @SrvAccount OUTPUT, N'no_output' SELECT @SrvAccount as SQLAgent_ServiceAccount"
	Exec-Query $DBName "master" $script
}

function Check-DBUser ([string]$DBName, [string]$UserName) {
	$script = ("SELECT * FROM sys.server_principals WHERE name = '" + $UserName + "'")
	Exec-Query $DBName "master" $script
}

function Create-DBUser ([string]$DBName, [string]$UserName) {
	$script = ("CREATE LOGIN [" + $UserName + "] FROM WINDOWS")
	if (!(Check-DBUser $DBName $UserName)) {Exec-Query $DBName "master" $script}
}

function Grant-EndpointACL ([string]$DBName, [string]$UserName) {
	$script = ("GRANT CONNECT ON ENDPOINT::HADR_ENDPOINT TO [" + $UserName + "]")
	Exec-Query $DBName "master" $script
}

function Get-InstanceFQDN([string]$DBName) {
	$script = "DECLARE @Domain NVARCHAR(100) EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT SELECT Cast(SERVERPROPERTY('MachineName') as nvarchar) + '.' + @Domain AS FQDN"
	Exec-Query $DBName "master" $script
}

function Create-Mirror ([string]$SrcDBInstance, [string]$DestDBInstance, [string]$DBName) {
	try {
		Write-Host -NoNewline "Attempting to Create Mirror..."
		Exec-Query $DestDBInstance "master" ("ALTER DATABASE [" + $DBName + "] SET PARTNER = 'TCP://" + $(Get-InstanceFQDN $SrcDBInstance).FQDN + ":5022'")
		Exec-Query $SrcDBInstance "master" ("ALTER DATABASE [" + $DBName + "] SET PARTNER = 'TCP://" + $(Get-InstanceFQDN $DestDBInstance).FQDN + ":5022'")
		Write-Host "DONE!"
		return $true
	}
	catch {
		Write-Host "FAILED!"
		return $false
	}
}

#Runtime
if (!$SrcDBInstance) {$SrcDBInstance = Read-Host "Source Instance"}
if (!$DestDBInstance) {$DestDBInstance = Read-host "Destination Instance"}
if (!$DBName) {$DBName = Read-Host "Database Name"}

$BackupTo = ("\\" + $DestDBInstance + "\Database\Backups\")
$Global:NameSpace = Get-FileNamespace $SrcDBInstance $DBName

Write-Host -NoNewline "Creating Endpoints..."
Create-Endpoint $SrcDBInstance
Create-Endpoint $DestDBInstance
Write-Host "DONE!"

Write-Host -NoNewline "Creating Users and ACLs..."
Create-DBUser $SrcDBInstance $(Get-DBSvcUser $DestDBInstance).SQLAgent_ServiceAccount
Create-DBUser $DestDBInstance $(Get-DBSvcUser $SrcDBInstance).SQLAgent_ServiceAccount
Grant-EndpointACL $SrcDBInstance $(Get-DBSvcUser $DestDBInstance).SQLAgent_ServiceAccount
Grant-EndpointACL $DestDBInstance $(Get-DBSvcUser $SrcDBInstance).SQLAgent_ServiceAccount
Write-Host "DONE!"

Write-Host -NoNewline "Performing Full Backup..."
Backup-FullBackup $SrcDBInstance $DBName ($BackupTo + $DBName + ".bak")
Restore-FullBackup $DestDBInstance $DBName ($BackupTo + $DBName + ".bak")
Write-Host "DONE!"

do {
	Write-Host -NoNewline "Performing Transactional Backup..."
	Backup-TranLog $SrcDBInstance $DBName ($BackupTo + $DBName + ".trn")
	Restore-TranLog $DestDBInstance $DBName ($BackupTo + $DBName + ".trn")
	Write-Host "DONE!"
}
until (Create-Mirror $SrcDBInstance $DestDBInstance $DBName)

Get-ChildItem $BackupTo | Where {$_.Name -like ($DBName + ".*")} | Remove-Item -Force