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, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] $databaseName, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $templateDatabase = "Template (21-0)", [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $backupFilepath = "/datadrive/mssql/temp/backup.bak", [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $TemplateDataFolder = "/datadrive/mssql/data/", [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $TemplateLogFolder = "/datadrive/mssql/data/", [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] [System.Management.Automation.PSCredential]$databaseCredentials ) begin {} process { $userProfile = Get-BCSModuleProfile if (-not $databaseCredentials) { $databaseCredentials = Get-BCSCredential -userName $userProfile.databaseUserName -securePassword (ConvertTo-SecureString -String $userProfile.databasePassword) } # Write-Host "Connecting to server $databaseServer as user $($userProfile.AzureUserName)" # $vmSession = New-DeploymentRemoteSession -HostName $databaseServer -Credential $credential # $sessionArgument = @{ "Session" = $vmSession } # Invoke-Command @sessionArgument -ScriptBlock { # Param($databaseServerInstance, # [System.Management.Automation.PSCredential]$databaseCredentials, # $tenantId, # $templateDatabase, # $backupFilepath) if (-not(Get-Module -ListAvailable -Name BCSPowershellModule)) { Install-Module BCSPowershellModule -Force } $ErrorActionPreference = "Stop" if (-not(Get-Module -ListAvailable -Name SQLServer)) { Install-Module SQLServer -Force } if ([string]::IsNullOrEmpty($databaseServerInstance)) { $databaseServerInstance = $databaseServer } $result = Get-SqlDatabase -ServerInstance $databaseServerInstance -Name $databaseName -Credential $databaseCredentials -ErrorAction Ignore if ($null -ne $result) { #throw "[$databaseName] already exists, please remove the database and try again." Write-Host "[$databaseName] already exists, continuing using present database." return } [String]$tenantId = $databaseName.replace(' ', '') $tenantId = $tenantId.ToLower() Write-Host "Backup database $templateDatabase on $databaseServerInstance to folder $backupFilepath" Backup-SqlDatabase -ServerInstance $databaseServerInstance -Database $templateDatabase -BackupFile $backupFilepath -Credential $databaseCredentials Write-Host "Relocating Data file Template_Data" $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("Template_Data", "$($TemplateDataFolder)$($tenantId).mdf") Write-Host "Relocating Data file Template_Log" $RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("Template_Log", "$($TemplateLogFolder)$($tenantId).ldf") Restore-SqlDatabase -ServerInstance $databaseServerInstance -Database $databaseName -BackupFile $backupFilepath -RelocateFile @($RelocateData, $RelocateLog) -Credential $databaseCredentials Write-Host "Changing database Logical Name Template_Data to $($tenantId)_Data" Invoke-Sqlcmd -ServerInstance $databaseServerInstance -Database $databaseName -Query "ALTER DATABASE [$databaseName] MODIFY FILE ( NAME = Template_Data, NEWNAME = $($tenantId)_Data );" -Credential $databaseCredentials Write-Host "Changing database Logical Name Template_Log to $($tenantId)_Log" Invoke-Sqlcmd -ServerInstance $databaseServerInstance -Database $databaseName -Query "ALTER DATABASE [$databaseName] MODIFY FILE ( NAME = Template_Log, NEWNAME = $($tenantId)_Log );" -Credential $databaseCredentials Write-Host "Database $databaseName has been created succesfully on SQL Server $databaseServerInstance" #} -ArgumentList $databaseServerInstance, $databaseCredentials, $tenantId, $templateDatabase, $backupFilepath } end { } } Export-ModuleMember -Function New-BCSTenantDatabase |