-
Notifications
You must be signed in to change notification settings - Fork 243
/
Remove-BcDatabase.ps1
89 lines (79 loc) · 3.08 KB
/
Remove-BcDatabase.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
<#
.Synopsis
Remove Business Central Database(s) from SQL Server
.Description
Remove Business Central Database(s) from SQL Server
Windows Authentication to the SQL Server is required.
.Parameter databaseServer
database Server from which you want to remove the database(s)
.Parameter databaseInstance
database Instance on the database Server from which you want to remove the database(s)
.Parameter databaseName
database name of the database(s) you want to remove
#>
function Remove-BcDatabase {
Param(
[Parameter(Mandatory=$true)]
[string] $databaseServer,
[Parameter(Mandatory=$false)]
[string] $databaseInstance = "",
[Parameter(Mandatory=$true)]
[string] $databaseName
)
$telemetryScope = InitTelemetryScope -name $MyInvocation.InvocationName -parameterValues $PSBoundParameters -includeParameters @()
try {
if ($databaseServer -eq 'host.containerhelper.internal') {
$databaseServer = 'localhost'
}
$databaseServerInstance = $databaseServer
if ($databaseInstance) {
$databaseServerInstance += "\$databaseInstance"
}
if ($databaseName.Contains('%')) {
$op = "like"
}
else {
$op = "="
}
$dbFiles = Invoke-SqlCmd `
-ServerInstance $databaseserverinstance `
-Query "SELECT f.physical_name FROM sys.sysdatabases db INNER JOIN sys.master_files f ON f.database_id = db.dbid WHERE db.name $op '$DatabaseName'" | ForEach-Object { $_.physical_name }
$databases = Invoke-SqlCmd `
-ServerInstance $databaseserverinstance `
-Query "SELECT * FROM sys.sysdatabases WHERE name $op '$DatabaseName'" | ForEach-Object { $_.name }
$databases | ForEach-Object {
Write-Host "Setting database $_ offline"
Invoke-SqlCmd `
-ServerInstance $DatabaseServerInstance `
-Query "ALTER DATABASE [$_] SET OFFLINE WITH ROLLBACK IMMEDIATE"
Write-Host "Removing database $_"
Invoke-SqlCmd `
-ServerInstance $DatabaseServerInstance `
-Query "DROP DATABASE [$_]"
}
$path = ''
$dbFiles | ForEach-Object {
if ($databaseServer -ne "localhost") {
$qualifier = $_ | Split-Path -Qualifier
$newQualifier = '\\{0}\{1}' -f $databaseServer, $qualifier.Replace(':','$').ToLower()
$path = $_.Replace($qualifier, $newQualifier)
} else {
$path = $_
}
if (Test-Path $path) { Remove-Item -Path $path -Force }
}
if ([string]::IsNullOrEmpty($path) -eq $false -and
(Test-Path ($path | Split-Path -Parent)) -eq $true -and
[string]::IsNullOrEmpty((Get-ChildItem -Path ($path | Split-Path -Parent))) -eq $true) {
Remove-Item -Path ($path | Split-Path -Parent) -Force -ErrorAction Continue
}
}
catch {
TrackException -telemetryScope $telemetryScope -errorRecord $_
throw
}
finally {
TrackTrace -telemetryScope $telemetryScope
}
}
Export-ModuleMember -Function Remove-BcDatabase