Module/Administration/Copy-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 Copy-BCSTenantDatabase { Param ( [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] $fromDatabaseServer, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $fromDatabaseServerInstance, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] $fromDatabaseName, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] $fromDatabaseServerBackupFilepath, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $fromDatabaseDataLogicalFileName, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $fromDatabaseDataFolder, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $fromDatabaseLogLogicalFileName, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $fromDatabaseLogFolder, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] [System.Management.Automation.PSCredential]$fromDatabaseCredentials, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] $toDatabaseServer, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $toDatabaseServerInstance, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] $toDatabaseName, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $true)] $toDatabaseServerBackupFilepath, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] [System.Management.Automation.PSCredential]$toDatabaseCredentials, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $toDatabaseDataLogicalFileName, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $toDatabaseLogLogicalFileName, [Parameter(ValueFromPipelineByPropertyName, Mandatory = $false)] $blobStorageBackupFileURL ) begin {} process { if (-not $fromDatabaseCredentials) { $fromDatabaseCredentials = Get-BCSCredential -userName $userProfile.fromDatabaseUserName -securePassword (ConvertTo-SecureString -String $userProfile.fromDatabasePassword) } if (-not $toDatabaseCredentials) { $toDatabaseCredentials = 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($fromDatabaseServerInstance)) { $fromDatabaseServerInstance = $fromDatabaseServer } if ([string]::IsNullOrEmpty($toDatabaseServerInstance)) { $toDatabaseServerInstance = $toDatabaseServer } $databaseExists = $true try { Get-SqlDatabase -ServerInstance $toDatabaseServerInstance -Name $toDatabaseName -Credential $toDatabaseCredentials -ErrorAction Stop } catch { if ($_.CategoryInfo.Reason -like "SqlPowerShellObjectNotFoundException") { $databaseExists = $false } else { Write-Error "An error occurred: $($_.Exception)" } } if ($databaseExists) { Write-Error "Database $($toDatabaseName) already exists, aborting." } [String]$tenantId = $toDatabaseName.replace(' ', '') $tenantId = $tenantId.ToLower() Write-Host "Backup database $fromDatabaseName on $fromDatabaseServerInstance to folder $fromDatabaseServerBackupFilepath" Backup-SqlDatabase -ServerInstance $fromDatabaseServerInstance -Database $fromDatabaseName -BackupFile $fromDatabaseServerBackupFilepath -Credential $fromDatabaseCredentials -CopyOnly $toDatabaseServerBackupFileDirectory = Split-Path -Path $toDatabaseServerBackupFilepath -Parent Get-FileFromURLOnRemote -remoteServerName $toDatabaseServer -sourceURL $blobStorageBackupFileURL -destinationFolder $toDatabaseServerBackupFileDirectory -credentials $toDatabaseCredentials # Load the SQL Server Management Objects (SMO) assembly [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") # Connect to the SQL Server instance $serverInstance = New-Object Microsoft.SqlServer.Management.Smo.Server($toDatabaseServerInstance) $serverInstance.ConnectionContext.LoginSecure = $false $serverInstance.ConnectionContext.Login = $toDatabaseCredentials.GetNetworkCredential().UserName $serverInstance.ConnectionContext.Password = $toDatabaseCredentials.GetNetworkCredential().Password # Retrieve the default data and log file locations $toDatabaseDataFolder = $serverInstance.Settings.DefaultFile $toDatabaseLogFolder = $serverInstance.Settings.DefaultLog Write-Host "Relocating Data file Template_Data" $RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($fromDatabaseDataLogicalFileName, "$($toDatabaseDataFolder)$($tenantId).mdf") Write-Host "Relocating Data file Template_Log" $RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($fromDatabaseLogLogicalFileName, "$($toDatabaseLogFolder)$($tenantId).ldf") Restore-SqlDatabase -ServerInstance $toDatabaseServerInstance -Database $toDatabaseName -BackupFile $toDatabaseServerBackupFilepath -RelocateFile @($RelocateData, $RelocateLog) -Credential $toDatabaseCredentials Write-Host "Changing database Logical Name $fromDatabaseDataLogicalFileName to $($toDatabaseDataLogicalFileName)_Data" Invoke-Sqlcmd -ServerInstance $toDatabaseServerInstance -Database $toDatabaseName -Query "ALTER DATABASE [$toDatabaseName] MODIFY FILE ( NAME = $fromDatabaseDataLogicalFileName, NEWNAME = $($toDatabaseDataLogicalFileName)_Data );" -Credential $toDatabaseCredentials Write-Host "Changing database Logical Name $fromDatabaseLogLogicalFileName to $($toDatabaseLogLogicalFileName)_Data" Invoke-Sqlcmd -ServerInstance $toDatabaseServerInstance -Database $toDatabaseName -Query "ALTER DATABASE [$toDatabaseName] MODIFY FILE ( NAME = $fromDatabaseLogLogicalFileName, NEWNAME = $($toDatabaseLogLogicalFileName)_Data );" -Credential $toDatabaseCredentials Write-Host "Database $toDdatabaseName has been created succesfully on SQL Server $toDatabaseServerInstance" #} -ArgumentList $databaseServerInstance, $databaseCredentials, $tenantId, $templateDatabase, $backupFilepath } end { } } Export-ModuleMember -Function Copy-BCSTenantDatabase |