---
tags: CICD
---
# PreRelease DB
## Concept
**1. Backup prod like DB(template DB) and restore to pre-release DB**
**1.1. Get Backup DB list**
`$dblist = Get-SqlDatabase -ServerInstance $DBServerInfo.BackupDBServer | Where-Object { ($_.Id -gt 4) -and ($_.name -ine "PERF_COLLECTION") `
**1.2 Backup DB**

foreach ($db in $dblist) {
    $counter++
    $backupfile = "$($DBServerInfo.BackupFolderPath)\$($db.name)-$($date).bak"
    Backup-SqlDatabase -ServerInstance $db.parent.DomainInstanceName -Database $db.name -BackupFile $backupfile -CompressionOption On -CopyOnly
}

**1.3 Restore to PreRelease DB**

Kill DB session
    $query = @'
    DECLARE @kill varchar(8000) = '' 
    SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';' 
    FROM sys.dm_exec_sessions WHERE database_id = db_id('dbname') 
    EXEC(@kill)
'@
    $query = $query.Replace("dbname", $DbName)
    Invoke-Sqlcmd -ServerInstance $serverInstance -Query $query

`$restoreInfo = Restore-SqlDatabase -ServerInstance $targetDBServer -Database "$targetDBname" -BackupFile "$($backupfile.FullName)" -AutoRelocateFile -PassThru -ReplaceDatabase -ContinueAfterError -ConnectionTimeout 30`

**Deploy DB package to Prelease DB by replace DBConnection**

foreach ($ticketinfo in $deploymentunit.Tickets)
{
	if ($IsDeployToPreReleaseDB) # replace DBConnection if prerelease
	{                    
		if ($ticketinfo.DBConnection -ieq $DBServerInfo.BackupArchDBServer)
		{
			$ticketinfo.DBConnection = $DBServerInfo.RestoreArchDBServer
		}
		else
		{
		  $ticketinfo.DBConnection = $PreReleaseDB
		}
	}                
	try
	{           
		$result = "success"
		$stopwatch.Start()
		$msg = sqlcmd -I -S $($ticketinfo.DBConnection) -f 65001 -i $($ticketinfo.SqlFile) | Out-String
		$SKYMessage = "$($msg.Trim())"
		$stopwatch.Stop()
		& "$($PSScriptRoot)\SendDBReleaseInfo2ELK.ps1  -environment $Env -releaseTicket $ReleaseTicket -partner $Partner -ticketId $ticketinfo.TicketId -message $SKYMessage -seq $counter -total "$($totalpackagecount)" -result $result -duration $stopwatch.ElapsedMilliseconds -elkUrl $elkUrl -elkindexname $elkindexname -releaseType $releaseType
	}
}