Module/Administration/New-BCSTenantDatabase.ps1
|
<#
.SYNOPSIS Create new tenant database .DESCRIPTION Creates a new tenant database on SQL server using a template database .PARAMETER databaseServer SQL Server name .PARAMETER databaseServerInstance Name of the SQL Server and Instance, default BCNUP .PARAMETER databaseName Name of the new tenant/database .PARAMETER tempalteDatabase Name of the database on the server to use as a template when creating the new tenant database .PARAMETER templateDatabase Name of the template database to be restored, deafult Template (19-0) .PARAMETER tempFolder TempFolder where temporary database backup is saved, default c:\temp\ .PARAMETER databaseCredentials Your SQL login to the SQL Server, Credential information in the form of a System.Management.Automation.PSCredential object .NOTES Author: Mathias Stjernfelt Website: http://www.brightcom.se .EXAMPLE New-BCSTenantDatabase -databaseName "My New Tenant" New-BCSTenantDatabase -serverInstance myServerInstance -databaseName "My New Tenant" -templateDatabase myTemplateDatabase -credential $credentials #> function New-BCSTenantDatabase { Param ( [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] $databaseServer, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $databaseServerInstance = "BCNUP", [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] $databaseName, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $templateDatabase = "Template (19-0)", [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $tempFolder = "c:\temp\", [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] [System.Management.Automation.PSCredential]$databaseCredentials ) begin {} process { $userProfile = Get-BCSModuleProfile if (-not $databaseCredentials) { $databaseCredentials = Get-BCSCredential -userName $userProfile.userName -securePassword (ConvertTo-SecureString -String $userProfile.databasePassword) } $securePassword = ConvertTo-SecureString -String $userProfile.AzurePassword $credential = New-Object System.Management.Automation.PSCredential -argumentList $userProfile.AzureUserName, $securePassword Write-Host "Connecting to server $databaseServer as user $($userProfile.AzureUserName)" $vmSession = New-DeploymentRemoteSession -HostName $databaseServer -Credential $credential $sessionArgument = @{ "Session" = $vmSession } $tenant = $databaseName; Invoke-Command @sessionArgument -ScriptBlock { Param($databaseServerInstance, [System.Management.Automation.PSCredential]$databaseCredentials, $tenant, $templateDatabase, $tempFolder) Install-Module BCSPowershellModule -Force $ErrorActionPreference = "Stop" if (-not(Get-Module -ListAvailable -Name SQLServer)) { Install-Module SQLServer -Force } if ([string]::IsNullOrEmpty($databaseServerInstance)) { $databaseServerInstance = $databaseServer } $tempFolder = Join-Path $tempFolder $(New-Guid) New-Item -Path $tempFolder -ItemType Directory | Out-Null $TempBackupFile = "$tempFolder\template.bak" Write-Host "Backup database $templateDatabase on $databaseServerInstance to folder $TempBackupFile" Backup-SqlDatabase -ServerInstance $databaseServerInstance -Database $templateDatabase -BackupFile $TempBackupFile -Credential $databaseCredentials | Out-Null Write-Host "Relocating Data file Template_Data" $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("Template_Data", "F:\DATA\$tenant.mdf") Write-Host "Relocating Data file Template_Log" $RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("Template_Log", "G:\LOG\$tenant.mdf") Restore-SqlDatabase -ServerInstance $databaseServerInstance -Database $tenant -BackupFile $TempBackupFile -RelocateFile @($RelocateData, $RelocateLog) -Credential $databaseCredentials $dbLogicalFileName = $tenant.replace(' ','') Write-Host "Changing database Logical Name Template_Data to $($dbLogicalFileName)_Data" Invoke-Sqlcmd -ServerInstance $databaseServerInstance -Database $tenant -Query "ALTER DATABASE [$tenant] MODIFY FILE ( NAME = Template_Data, NEWNAME = $($dbLogicalFileName)_Data );" -Credential $databaseCredentials Write-Host "Changing database Logical Name Template_Log to $($dbLogicalFileName)_Log" Invoke-Sqlcmd -ServerInstance $databaseServerInstance -Database $tenant -Query "ALTER DATABASE [$tenant] MODIFY FILE ( NAME = Template_Log, NEWNAME = $($dbLogicalFileName)_Log );" -Credential $databaseCredentials Write-Host "Database $tenant has been created succesfully on SQL Server $databaseServerInstance" if (Test-Path -Path $TempBackupFile -PathType Leaf) { Remove-Item -Path $TempBackupFile } } -ArgumentList $databaseServerInstance, $databaseCredentials, $tenant, $templateDatabase, $tempFolder } end { } } Export-ModuleMember -Function New-BCSTenantDatabase |