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) } 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) { Write-Host "Database $databaseName already exists." 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") Write-Host "Creating database $databaseName on $databaseServerInstance" 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 -Encrypt Optional -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 -Encrypt Optional -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" } end { } } Export-ModuleMember -Function New-BCSTenantDatabase |