PSDB is a PowerShell module which acts as a wrapper for New-AzSqlDatabaseExport and New-AzSqlDatabaseImport cmdlets. It provides tab completion for Resource Groups, Sql servers, Key Vault and storage account name which are essential to perform import and export operation.
Azure Sql database can be copied or exported based on our requirement. One of the features provided by Azure is that the database can be exported as .bacpac file and saved in storage account which later can be imported to Azure Sql database. This file holds metadata and database data.
Az.Sql module is packed with useful cmdlets which can be used to automate the export and import functionality of database. To make the full use of export and import functionality and to automate it end to end right from exporting the database as .bacpac file, storing it in a storage account till it is imported to another database which could potentially be in another subscription could be quite tedious. This is where PSDB module comes into picture. It acts as a wrapper for these cmdlets and provides tab completion for few mandatory parameters and helps you to replicate the functionality of export and import operation that you would perform in Azure portal.
Installation
PSDB module is available in PowerShell Gallery which means you can directly install it if you are using PowerShell v5.1 and above.
PS C:\> Install-Module -Name PSDB -Scope CurrentUser
Module Deep Dive
Now that the module is installed, lets see what are the functions available.
PS C:\> Import-Module -Name PSDB
PS C:\> Get-Command -Module PSDB
CommandType Name Version Source
----------- ---- ------- ------
Function Export-PSDBSqlDatabase 0.1.13 PSDB
Function Get-PSDBDatabaseData 0.1.13 PSDB
Function Get-PSDBImportExportStatus 0.1.13 PSDB
Function Get-PSDBKVSecret 0.1.13 PSDB
Function Import-PSDBSqlDatabase 0.1.13 PSDB
Function Invoke-PSDBDatabaseQuery 0.1.13 PSDB
Function Set-PSDBDefaults 0.1.13 PSDB
So, for now there are seven functions available in v0.1.13. Let’s explore the usage of Export function first with PowerShell’s help system.
PS C:\> help Export-PSDBSqlDatabase
NAME
Export-PSDBSqlDatabase
SYNOPSIS
Export-PSDBSqlDatabase is a wrapper for New-AzSqlDatabaseExport cmdlet with tab completion for mandatory parameters.
SYNTAX
Export-PSDBSqlDatabase [-DatabaseName] <String> -AdministratorLogin <String> -AdministratorLoginPassword <SecureString> [-BlobName <String>] -ResourceGroupName <String> -ServerName <String> -StorageAccountName <String> -StorageContainerName <String> [-StorageKeyType <String>] [-Subscription <String>] [<CommonParameters>]
DESCRIPTION
Export-PSDBSqlDatabase is a wrapper for New-AzSqlDatabaseExport cmdlet with tab completion for mandatory parameters. It helps you export the database as you would do in Azure portal.
You can save the exported .bacpac file in different subscription by providing values to storage account name, container name and the subscription.
RELATED LINKS
REMARKS
To see the examples, type: "get-help Export-PSDBSqlDatabase -examples".
For more information, type: "get-help Export-PSDBSqlDatabase -detailed".
For technical information, type: "get-help Export-PSDBSqlDatabase -full".
As said it has selected parameters from New-AzSqlDatabaseExport but it provides additional functionality of tab completion and exporting the database from one subscription to another subscription.
- Export database .bacpac in same subscription
Let’s see an example on how to export .bacpac file in same subscription and compare it with Azure portal.
First retrieve the password from key vault. We can use Get-PSDBKVSecret function which is a wrapper for Get-AzKeyVaultSecret and returns only the secret value as secure string. This is specifically designed to work with this module. Also, if you have saved your username in key vault, it can be retrieved as plain text and passed to the function.
PS C:\> $password = Get-PSDBKVSecret -VaultName "kv-01" -SecretName "sqlpassword" # tab completion available for key vault name.
System.Security.SecureString # will be stored in the variable $password.
PS C:\> $username = Get-PSDBKVSecret -VaultName "kv-01" -SecretName "sqlusername" -AsPlainText
PS C:\> Export-PSDBSqlDatabase ` # use alias Export
-ResourceGroupName "myRSG" ` # tab completion available for resource group name.
-DatabaseName "SqlDatabase01" `
-ServerName "SqlServer01" ` # tab completion available for sql server.
-StorageAccountName "sqlbackup" ` # tab completion available for storage account name.
-StorageContainerName "backups" `
-AdministratorLogin "sqladmin" `
-AdministratorLoginPassword $password
Now that you will get the status link once you run the above cmdlet. This can then be passed to Get-PSDBImportExportStatus to get the status of export operation.
- Say that you want to export the .bacpac file to different subscription (which is possible in portal) this can be simply achieved by passing the value to -Subscription parameter and specifying the storage account name and container name which are in the subscription that you want to save the file to.
PS C:\> Export-PSDBSqlDatabase `
-ResourceGroupName "myRSG" `
-DatabaseName "SqlDatabase01" `
-ServerName "SqlServer01" `
-StorageAccountName "sqlbackup" `
-StorageContainerName "backups" `
-AdministratorLogin "sqladmin" `
-AdministratorLoginPassword $password `
-Subscription "mySubscription02"
As in Azure portal the .bacpac file will be exported as databaseName-YYYY-MM-DD-HH-SS.bacpac format. This can be changed by passing the database name to -BlobName parameter. Make sure to use the same name while importing the database too.
- Import database .bacpac in same subscription
Now that you have .bacpac file saved in subscription A and you want to import it to the same subscription but to different database. This can be done as follows.
PS C:\> Import-PSDBSqlDatabase ` # use alias Import
-ResourceGroupName "myRSG02" ` # tab completion available for resource group name.
-ServerName "SqlServer02" ` # tab completion available for sql server.
-StorageAccountName "sqlbackup" ` # tab completion available for storage account name.
-StorageContainerName "backups" `
-AdministratorLogin "sqladmin02" `
-AdministratorLoginPassword ("securepassword" | ConvertTo-SecureString -AsPlainText -Force)
Now that the .bacpac file will be imported to the database and the status can be retrieved with function Get-PSDBImportExportStatus.
- Get-PSDBImportExportStatus
Function Get-PSDBImportExportStatus helps you to not only check the status of import or export function but also to automate it. It is very useful when you want to automate the end to end import and export operation within same or different subscription and wait until one function is completed or to terminate the script if the function takes too long to complete.
Get-PSDBImportExportStatus has -Wait switch which allows you to wait till a function completes and report the status continuously. The waiting period (Interval) and script termination time (Time out) can be controlled by -TimeOut and -Interval parameters respectively.
Get-PSDBDatabaseDataandInvoke-PSDBDatabaseQueryare other useful functions included in this module which you can use to run query against the databases.
Closing
This is an introduction to the module and what you can gain with this. You can find the source code for module here and add your comments for any issue.