functions/new-d365bacpac.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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354

<#
    .SYNOPSIS
        Generate a bacpac file from a database
         
    .DESCRIPTION
        Takes care of all the details and steps that is needed to create a valid bacpac file to move between Tier 1 (onebox or Azure hosted) and Tier 2 (MS hosted), or vice versa
         
        Supports to create a raw bacpac file without prepping. Can be used to automate backup from Tier 2 (MS hosted) environment
         
    .PARAMETER ExportModeTier1
        Switch to instruct the cmdlet that the export will be done against a classic SQL Server installation
         
    .PARAMETER ExportModeTier2
        Switch to instruct the cmdlet that the export will be done against an Azure SQL DB instance
         
    .PARAMETER DatabaseServer
        The name of the database server
         
        If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN).
         
        If Azure use the full address to the database server, e.g. server.database.windows.net
         
    .PARAMETER DatabaseName
        The name of the database
         
    .PARAMETER SqlUser
        The login name for the SQL Server instance
         
    .PARAMETER SqlPwd
        The password for the SQL Server user
         
    .PARAMETER BackupDirectory
        The path where to store the temporary backup file when the script needs to handle that
         
    .PARAMETER NewDatabaseName
        The name for the database the script is going to create when doing the restore process
         
    .PARAMETER BacpacFile
        The path where you want the cmdlet to store the bacpac file that will be generated
         
    .PARAMETER CustomSqlFile
        The path to a custom sql server script file that you want executed against the database before it is exported
         
    .PARAMETER DiagnosticFile
        Path to where you want the export to output a diagnostics file to assist you in troubleshooting the export
         
    .PARAMETER ExportOnly
        Switch to instruct the cmdlet to either just create a dump bacpac file or run the prepping process first
         
    .PARAMETER MaxParallelism
        Sets SqlPackage.exe's degree of parallelism for concurrent operations running against a database. The default value is 8.
         
    .PARAMETER ShowOriginalProgress
        Instruct the cmdlet to show the standard output in the console
         
        Default is $false which will silence the standard output
         
    .PARAMETER OutputCommandOnly
        Instruct the cmdlet to only output the command that you would have to execute by hand
         
        Will include full path to the executable and the needed parameters based on your selection
         
    .PARAMETER EnableException
        This parameters disables user-friendly warnings and enables the throwing of exceptions
        This is less user friendly, but allows catching exceptions in calling scripts
         
    .EXAMPLE
        PS C:\> Invoke-D365InstallSqlPackage
         
        You should always install the latest version of the SqlPackage.exe, which is used by New-D365Bacpac.
         
        This will fetch the latest .Net Core Version of SqlPackage.exe and install it at "C:\temp\d365fo.tools\SqlPackage".
         
    .EXAMPLE
        PS C:\> New-D365Bacpac -ExportModeTier1 -BackupDirectory c:\Temp\backup\ -NewDatabaseName Testing1 -BacpacFile "C:\Temp\Bacpac\Testing1.bacpac"
         
        Will backup the "AXDB" database and restore is as "Testing1" again the localhost SQL Server.
        Will run the prepping process against the restored database.
        Will export a bacpac file to "C:\Temp\Bacpac\Testing1.bacpac".
        Will delete the restored database.
        It will use trusted connection (Windows authentication) while working against the SQL Server.
         
    .EXAMPLE
        PS C:\> New-D365Bacpac -ExportModeTier2 -DatabaseServer localhost -DatabaseName AxDB -SqlUser User123 -SqlPwd "Password123" -NewDatabaseName Testing1 -BacpacFile C:\Temp\Bacpac\Testing1.bacpac
         
        Will create a copy the db database on the dbserver1 in Azure.
        Will run the prepping process against the copy database.
        Will export a bacpac file.
        Will delete the copy database.
         
    .EXAMPLE
        PS C:\> New-D365Bacpac -ExportModeTier2 -SqlUser User123 -SqlPwd "Password123" -NewDatabaseName Testing1 -BacpacFile "C:\Temp\Bacpac\Testing1.bacpac"
         
        Normally used for a Tier-2 export and preparation for Tier-1 import
         
        Will create a copy of the registered D365 database on the registered D365 Azure SQL DB instance.
        Will run the prepping process against the copy database.
        Will export a bacpac file.
        Will delete the copy database.
         
    .EXAMPLE
        PS C:\> New-D365Bacpac -ExportModeTier2 -SqlUser User123 -SqlPwd "Password123" -NewDatabaseName Testing1 -BacpacFile C:\Temp\Bacpac\Testing1.bacpac -ExportOnly
         
        Will export a bacpac file.
        The bacpac should be able to restore back into the database without any preparing because it is coming from the environment from the beginning
         
    .EXAMPLE
        PS C:\> New-D365Bacpac -ExportModeTier1 -BackupDirectory c:\Temp\backup\ -NewDatabaseName Testing1 -BacpacFile "C:\Temp\Bacpac\Testing1.bacpac" -DiagnosticFile "C:\temp\ExportLog.txt"
         
        Will backup the "AXDB" database and restore is as "Testing1" again the localhost SQL Server.
        Will run the prepping process against the restored database.
        Will export a bacpac file to "C:\Temp\Bacpac\Testing1.bacpac".
        Will delete the restored database.
        It will use trusted connection (Windows authentication) while working against the SQL Server.
         
        It will output a diagnostic file to "C:\temp\ExportLog.txt".
         
    .EXAMPLE
        PS C:\> New-D365Bacpac -ExportModeTier1 -BackupDirectory c:\Temp\backup\ -NewDatabaseName Testing1 -BacpacFile "C:\Temp\Bacpac\Testing1.bacpac" -MaxParallelism 32
         
        Will backup the "AXDB" database and restore is as "Testing1" again the localhost SQL Server.
        Will run the prepping process against the restored database.
        Will export a bacpac file to "C:\Temp\Bacpac\Testing1.bacpac".
        Will delete the restored database.
        It will use trusted connection (Windows authentication) while working against the SQL Server.
         
        It will use 32 connections against the database server while generating the bacpac file.
         
    .NOTES
        The cmdlet supports piping and can be used in advanced scenarios. See more on github and the wiki pages.
         
        Author: Rasmus Andersen (@ITRasmus)
        Author: Mötz Jensen (@Splaxi)
         
#>

function New-D365Bacpac {
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")]
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseProcessBlockForPipelineCommand", "")]
    [CmdletBinding(DefaultParameterSetName = 'ExportTier2')]
    param (
        [Parameter(Mandatory = $true, ParameterSetName = 'ExportTier1', Position = 0)]
        [switch] $ExportModeTier1,

        [Parameter(Mandatory = $true, ParameterSetName = 'ExportTier2', Position = 0)]
        [switch] $ExportModeTier2,

        [Parameter(Position = 1 )]
        [string] $DatabaseServer = $Script:DatabaseServer,

        [Parameter(Position = 2 )]
        [string] $DatabaseName = $Script:DatabaseName,

        [Parameter(Mandatory = $false, Position = 3 )]
        [Parameter(Mandatory = $true, ParameterSetName = 'ExportTier2', ValueFromPipelineByPropertyName = $true, Position = 3)]
        [string] $SqlUser = $Script:DatabaseUserName,

        [Parameter(Mandatory = $false, Position = 4 )]
        [Parameter(Mandatory = $true, ParameterSetName = 'ExportTier2', ValueFromPipelineByPropertyName = $true, Position = 4)]
        [string] $SqlPwd = $Script:DatabaseUserPassword,

        [Parameter(ParameterSetName = 'ExportTier1', Position = 5 )]
        [string] $BackupDirectory = "C:\Temp\d365fo.tools\SqlBackups",

        [Parameter(Position = 6 )]
        [string] $NewDatabaseName = "$Script:DatabaseName`_export",

        [Parameter(Position = 7 )]
        [Alias('File')]
        [string] $BacpacFile = "C:\Temp\d365fo.tools\$DatabaseName.bacpac",

        [Parameter(Position = 8 )]
        [string] $CustomSqlFile,

        [string] $DiagnosticFile,

        [switch] $ExportOnly,

        [string] $MaxParallelism = 8,

        [switch] $ShowOriginalProgress,

        [switch] $OutputCommandOnly,

        [switch] $EnableException
    )
    
    Invoke-TimeSignal -Start

    $UseTrustedConnection = Test-TrustedConnection $PSBoundParameters
    
    if ($PSBoundParameters.ContainsKey("CustomSqlFile")) {
        if (-not (Test-PathExists -Path $CustomSqlFile -Type Leaf)) { return }
        $ExecuteCustomSQL = $true
    }

    if ($BacpacFile -notlike "*.bacpac") {
        Write-PSFMessage -Level Host -Message "The path for the bacpac file must contain the <c='em'>.bacpac</c> extension. Please update the <c='em'>BacpacFile</c> parameter and try again."
        Stop-PSFFunction -Message "The BacpacFile path was not correct."
        return
    }

    if ($PSBoundParameters.ContainsKey("BackupDirectory") -or $ExportModeTier1) {
        if (-not (Test-PathExists -Path $BackupDirectory -Type Container -Create)) { return }
    }
    
    if (-not (Test-PathExists -Path (Split-Path $BacpacFile -Parent) -Type Container -Create)) { return }

    # Work around to make sure to keep Storage when using the non-core version of the SqlPackage
    $executable = $Script:SqlPackagePath
    $classicPattern = "C:\Program Files*\Microsoft SQL Server\1*0\DAC\bin\SqlPackage.exe"

    [System.Collections.ArrayList] $Properties = New-Object -TypeName "System.Collections.ArrayList"

    $null = $Properties.Add("VerifyFullTextDocumentTypesSupported=false")

    if($executable -like $classicPattern) {
        Write-PSFMessage -Level Verbose -Message "Looks like we are running against the non-core version of SqlPackage.exe. Then we need to support the Storage=File property."
        $null = $Properties.Add("Storage=File")
    }

    $BaseParams = @{
        DatabaseServer = $DatabaseServer
        DatabaseName   = $DatabaseName
        SqlUser        = $SqlUser
        SqlPwd         = $SqlPwd
    }

    $ExportParams = @{
        Action     = "export"
        FilePath   = $BacpacFile
        Properties = $Properties.ToArray()
    }

    if (-not [system.string]::IsNullOrEmpty($DiagnosticFile)) {
        if (-not (Test-PathExists -Path (Split-Path $DiagnosticFile -Parent) -Type Container -Create)) { return }
        $ExportParams.DiagnosticFile = $DiagnosticFile
    }

    if ($ExportOnly) {
        
        Write-PSFMessage -Level Verbose -Message "Invoking the export of the bacpac file only."

        Write-PSFMessage -Level Verbose -Message "Invoking the sqlpackage with parameters" -Target $BaseParams
        Invoke-SqlPackage @BaseParams @ExportParams -ShowOriginalProgress:$ShowOriginalProgress -OutputCommandOnly:$OutputCommandOnly

        if ($OutputCommandOnly) { return }

        if (Test-PSFFunctionInterrupt) { return }

        [PSCustomObject]@{
            File     = $BacpacFile
            Filename = (Split-Path $BacpacFile -Leaf)
        }
    }
    else {
        if ($ExportModeTier1) {
            $Params = @{
                BackupDirectory   = $BackupDirectory
                NewDatabaseName   = $NewDatabaseName
                TrustedConnection = $UseTrustedConnection
            }
            
            if (-not $OutputCommandOnly) {
                Write-PSFMessage -Level Verbose -Message "Invoking the Tier 1 - SQL backup & restore process"
                $res = Invoke-SqlBackupRestore @BaseParams @Params

                if ((Test-PSFFunctionInterrupt) -or (-not $res)) { return }

                $Params = Get-DeepClone $BaseParams
                $Params.DatabaseName = $NewDatabaseName

                Write-PSFMessage -Level Verbose -Message "Invoking the Tier 1 - Clear SQL objects"
                $res = Invoke-ClearSqlSpecificObjects @Params

                if ((Test-PSFFunctionInterrupt) -or (-not $res)) { return }

                if ($ExecuteCustomSQL) {
                    Write-PSFMessage -Level Verbose -Message "Invoking the Tier 1 - Execution of custom SQL script"
                    $res = Invoke-D365SqlScript @Params -FilePath $CustomSqlFile

                    if (Test-PSFFunctionInterrupt) { return }
                }
            }
            else {
                $Params = Get-DeepClone $BaseParams
                $Params.DatabaseName = $NewDatabaseName
            }

            Write-PSFMessage -Level Verbose -Message "Invoking the Tier 1 - Export of the bacpac file from SQL"
            Invoke-SqlPackage @Params @ExportParams -ShowOriginalProgress:$ShowOriginalProgress -OutputCommandOnly:$OutputCommandOnly
            
            if ($OutputCommandOnly) { return }

            if (Test-PSFFunctionInterrupt) { return }

            Write-PSFMessage -Level Verbose -Message "Invoking the Tier 1 - Remove database from SQL"
            Remove-D365Database @Params

            [PSCustomObject]@{
                File     = $BacpacFile
                Filename = (Split-Path $BacpacFile -Leaf)
            }
        }
        else {
            $Params = @{
                NewDatabaseName = $NewDatabaseName
            }

            if (-not $OutputCommandOnly) {
                Write-PSFMessage -Level Verbose -Message "Invoking the Tier 2 - Creation of Azure DB copy"
                $res = Invoke-AzureBackupRestore @BaseParams @Params
            
                if ((Test-PSFFunctionInterrupt) -or (-not $res)) { return }
            
                $Params = Get-DeepClone $BaseParams
                $Params.DatabaseName = $NewDatabaseName
                Write-PSFMessage -Level Verbose -Message "Invoking the Tier 2 - Clear Azure DB objects"
                $res = Invoke-ClearAzureSpecificObjects @Params

                if ((Test-PSFFunctionInterrupt) -or (-not $res)) { return }

                if ($ExecuteCustomSQL) {
                    Write-PSFMessage -Level Verbose -Message "Invoking the Tier 2 - Execution of custom SQL script"
                    $res = Invoke-D365SqlScript @Params -FilePath $CustomSqlFile -TrustedConnection $false

                    if (!$res) { return }
                }
            }

            Write-PSFMessage -Level Verbose -Message "Invoking the Tier 2 - Export of the bacpac file from Azure DB"
            Invoke-SqlPackage @Params @ExportParams -TrustedConnection $false -ShowOriginalProgress:$ShowOriginalProgress -OutputCommandOnly:$OutputCommandOnly

            if ($OutputCommandOnly) { return }

            if (Test-PSFFunctionInterrupt) { return }
            
            Write-PSFMessage -Level Verbose -Message "Invoking the Tier 2 - Remove database from Azure DB"
            Remove-D365Database @Params

            if (Test-PSFFunctionInterrupt) {
                $messageString = "The bacpac file was created correctly, but there was an error while <c='em'>removing</c> the cloned database."
                Write-PSFMessage -Level Host -Message $messageString
            }

            [PSCustomObject]@{
                File     = $BacpacFile
                Filename = (Split-Path $BacpacFile -Leaf)
            }
        }
    }

    Invoke-TimeSignal -End
}