Patch/Cmdlets/NAV/Export-NAVLicense.ps1
|
<#
.SYNOPSIS Exports the license from the specified Microsoft Dynamics NAV database. .DESCRIPTION Exports the license from the specified Microsoft Dynamics NAV database. The license is stored in the $ndo$tenantproperty table in the database. The conversion from a BLOB to a binary file is handled by the bcp tool, which is part of the SQL Server Management Tools. For more information, see http://msdn.microsoft.com/en-us/library/ms162802.aspx .PARAMETER DatabaseServer Specifies the SQL Server database server. .PARAMETER DatabaseInstance Specifies the SQL Server instance. .PARAMETER LicenseFilePath Specifies the location where the license information must be exported to. .PARAMETER DatabaseName Specifies the database containing the table with the license information that must be exported. #> function Export-NAVLicenseFromApplicationDatabase { [CmdletBinding()] param ( [parameter(Mandatory=$true)] [string]$DatabaseServer, [parameter(Mandatory=$false)] [string]$DatabaseInstance = "", [parameter(Mandatory=$true)] [string]$LicenseFilePath, [parameter(Mandatory=$true)] [string]$DatabaseName ) PROCESS { Write-Verbose "Export license from the application database of $DatabaseName" Export-NAVLicense -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -LicenseFilePath $LicenseFilePath -DatabaseName $DatabaseName -TableName '$ndo$dbproperty' } } function Export-NAVLicenseFromTenantDatabase { [CmdletBinding()] param ( [parameter(Mandatory=$true)] [string]$DatabaseServer, [parameter(Mandatory=$false)] [string]$DatabaseInstance = "", [parameter(Mandatory=$true)] [string]$LicenseFilePath, [parameter(Mandatory=$true)] [string]$DatabaseName ) PROCESS { Write-Verbose "Export license from the tenant database of $DatabaseName" Export-NAVLicense -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -LicenseFilePath $LicenseFilePath -DatabaseName $DatabaseName -TableName '$ndo$tenantproperty' } } function Export-NAVLicenseFromMaster { [CmdletBinding()] param ( [parameter(Mandatory=$true)] [string]$DatabaseServer, [parameter(Mandatory=$false)] [string]$DatabaseInstance = "", [parameter(Mandatory=$true)] [string]$LicenseFilePath ) PROCESS { Write-Verbose "Export license from the master database of $DatabaseServer\$DatabaseInstance" Export-NAVLicense -DatabaseServer $DatabaseServer -DatabaseInstance $DatabaseInstance -LicenseFilePath $LicenseFilePath -DatabaseName 'master' -TableName '$ndo$srvproperty' } } function Export-NAVLicense { [CmdletBinding()] param ( [parameter(Mandatory=$true)] [string]$DatabaseServer, [parameter(Mandatory=$false)] [string]$DatabaseInstance = "", [parameter(Mandatory=$true)] [string]$LicenseFilePath, [parameter(Mandatory=$true)] [string]$DatabaseName, [parameter(Mandatory=$true)] [string]$TableName ) BEGIN { if(!(Test-Path -Path $LicenseFilePath -IsValid)) { Write-Error "Destination license file path $LicenseFilePath is not valid" return } $licenseDirectory = [System.IO.Path]::GetDirectoryName($LicenseFilePath) if(!(Test-Path $licenseDirectory -PathType Container)) { New-Item -Path $licenseDirectory -ItemType Container | Out-Null } if(!(Test-Path -Path $LicenseFilePath -PathType Leaf)) { New-Item -Path $LicenseFilePath -ItemType File | Out-Null } $SqlServerInstance = Get-SqlServerInstance $DatabaseServer $DatabaseInstance if(!(Test-LicenseExistsInTable -SqlServerInstance $SqlServerInstance -DatabaseName $DatabaseName -TableName $TableName)) { return } # Generate a format file for the license column content to be exported later on $FormatFilePath = (Join-Path $licenseDirectory "licenseFormat.fmt") if(Get-Item $FormatFilePath -ErrorAction SilentlyContinue) { Remove-Item -Path $FormatFilePath | Out-Null } # Try extract the format from the master table $ExtractFormatArguments = "`"`[`$ndo`$srvproperty`"`] format nul -T -n -f `"$FormatFilePath`" -T -S $SqlServerInstance -d master" Start-ProcessWithErrorHandling -FilePath "bcp" -ArgumentList $ExtractFormatArguments # If there are errors (the SQL Server instance does not have the master table), create the exported format if(!(Test-Path -Path $FormatFilePath)) { # Create the format to extract the license file, since it could not be extracted from the master table $sqlVersion = Invoke-Sqlcmd "select left(cast(serverproperty('productversion') as varchar), 4)" -ServerInstance $SqlServerInstance $stream = [System.IO.StreamWriter] $FormatFilePath $stream.WriteLine($sqlVersion.Column1) $stream.WriteLine("1") $stream.WriteLine("1 SQLIMAGE 0 0 "" 1 license """); $stream.close() } # Modify the file to have 0 for prefix length, instead of 4 (Get-Content $FormatFilePath) ` | Foreach { $_ -Replace "1 SQLIMAGE 4", "1 SQLIMAGE 0" } ` | Set-Content $FormatFilePath; # Extract the license $ArgumentList = "`"SELECT license FROM [$TableName`]`" queryout ""$LicenseFilePath"" -T -S $SqlServerInstance -d `"$DatabaseName`" -f `"$FormatFilePath`"" Start-ProcessWithErrorHandling -FilePath "bcp" -ArgumentList "$ArgumentList" if(Get-Item $FormatFilePath -ErrorAction SilentlyContinue) { Remove-Item -Path $FormatFilePath | Out-Null } return $LicenseFilePath } } function Test-LicenseExistsInTable { [CmdletBinding()] param ( [parameter(Mandatory=$true)] [string]$SqlServerInstance, [parameter(Mandatory=$true)] [string]$DatabaseName, [parameter(Mandatory=$true)] [string]$TableName ) PROCESS { $CurrentLocation = Get-Location try { if(!(Test-TableExistsInSQL -SqlServerInstance $SqlServerInstance -DatabaseName $DatabaseName -TableName $TableName)) { return $false; } # Check the license column exists $licenseColumnExists = Invoke-Sqlcmd "IF EXISTS(SELECT * from sys.columns where Name = N'license' and Object_ID = Object_ID(N'$TableName')) SELECT 1 as res else select 0 as res" -ServerInstance $SqlServerInstance -Database $DatabaseName if($licenseColumnExists.res -eq 0) { Write-Verbose "Table [$DatabaseName].[$TableName] does not contain column 'license'." return $false } # Check the license column is not empty $licenseFieldIsNull = Invoke-Sqlcmd "IF EXISTS (SELECT license from [$DatabaseName].[dbo].[$TableName] WHERE license is NULL) SELECT 1 as res else select 0 as res" -ServerInstance $SqlServerInstance -Database $DatabaseName if($licenseFieldIsNull.res -eq 1) { Write-Verbose "Table [$DatabaseName].[$TableName] does not contain license information." return $false } return $true } finally { Set-Location $CurrentLocation } } } function Test-TableExistsInSQL { [CmdletBinding()] param ( [parameter(Mandatory=$true)] [string]$SqlServerInstance, [parameter(Mandatory=$true)] [string]$DatabaseName, [parameter(Mandatory=$true)] [string]$TableName ) PROCESS { $tableExists = Invoke-Sqlcmd "IF EXISTS (SELECT 1 FROM [$DatabaseName].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='$TableName') SELECT 1 AS res ELSE SELECT 0 AS res;" -ServerInstance $SqlServerInstance -Database $DatabaseName if($tableExists.res -eq 0) { Write-Verbose "Table [$DatabaseName].[dbo].$TableName was not found in SQL Server $SqlServerInstance" return $false } return $true } } Export-ModuleMember -Function Export-NAVLicense, Export-NAVLicenseFromApplicationDatabase, Export-NAVLicenseFromTenantDatabase, Export-NAVLicenseFromMaster, Test-LicenseExistsInTable |