Use Powershell to export SQL Blob column data

Blob data can be exported using PowerShell in a simple way, by querying the data with Ado.Net – SqlClient  and then using a BinaryWriter  to write it on local hard drive.

This is how we can use PowerShell to export SQL Blob data to file.

## Export of "larger" Sql Server Blob to file            
## with GetBytes-Stream.         
# Configuration data            
$Server = ".\SQL105CTP3";         # SQL Server Instance.            
$Database = "ToDO";            
$Dest = "D:\Export\";             # Path to export to.            
$bufferSize = 8192;               # Stream buffer size in bytes.            
# Select-Statement for name & blob            
# with filter.            
$Sql = "SELECT [FileName]
              ,[Document]
        FROM Production.Document
        WHERE FileExtension = '.xlsx'";            
            
# Open ADO.NET Connection            
$con = New-Object Data.SqlClient.SqlConnection;            
$con.ConnectionString = "Data Source=$Server;" +             
                        "Integrated Security=True;" +            
                        "Initial Catalog=$Database";            
$con.Open();            
            
# New Command and Reader            
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con;            
$rd = $cmd.ExecuteReader();            
            
# Create a byte array for the stream.            
$out = [array]::CreateInstance('Byte', $bufferSize)            
            
# Looping through records            
While ($rd.Read())            
{            
    Write-Output ("Exporting: {0}" -f $rd.GetString(0));                    
    # New BinaryWriter            
    $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write;            
    $bw = New-Object System.IO.BinaryWriter $fs;            
               
    $start = 0;            
    # Read first byte stream            
    $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);            
    While ($received -gt 0)            
    {            
       $bw.Write($out, 0, $received);            
       $bw.Flush();            
       $start += $received;            
       # Read next byte stream            
       $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);            
    }            
            
    $bw.Close();            
    $fs.Close();            
}            
            
# Closing & Disposing all objects            
$fs.Dispose();            
$rd.Close();            
$cmd.Dispose();            
$con.Close();            
            
Write-Output ("Finished");

Server and Database Name is hard coded in this script. To make it parametrize, add these in first line;

param ($servername='.\SQL105CTP3', $databasename = 'ToDo',  $envname='Dev')
       

and then change these variables to get the value from parameters;

$Server = $servername;         # SQL Server Instance.            
$Database = $databasename;

Save the script and run it like this;

.\BLOBimagesCopy.ps1 -servername '(local' -databasename 'ToDO' -envname 'dev' 

Resources

https://social.technet.microsoft.com/wiki/contents/articles/890.export-sql-server-blob-data-with-powershell.aspx

https://www.red-gate.com/simple-talk/sysadmin/powershell/how-to-use-parameters-in-powershell/

Install Angular9 or latest version using PowerShell

For fresh start, Uninstall Node.js and npm

Uninstall node.js and npm using Windows Installer (msi)

To uninstall node.js and npm, Open Windows “Control Panel” -> “Uninstall a program”, select Node.js and click on uninstall to uninstaill both node.js and npm

Cleanup directories

After the uninstall, look for the following folders and delete them if exists
* Nodejs installed directory
* npm and npm-cache directories from %appdata% directory
* npmrc directory from user home directory ( C:\Users\{User} )

Now to re-install

Go to Node.js page and install current version

https://nodejs.org/en/download/current/

Check node and npm version;

node -v

npm -v

Open PowerShell in admin mode;

If you want to uninstall angular;

npm uninstall -g @angular/cli

npm cache clean –force

npm cache verify

Install a specific version of angular;

npm install -g @angular/cli@9.0.0

If you see this error, don’t worry. Angular is installed.

ng –v

You might get PowerShell digitally signed error; run these commands;

To check current execution policy, Get-ExecutionPolicy. A convenient method is to change the Execution policy using this cmdlet.

PS C:\ Set-ExecutionPolicy unrestricted

Press Y to confirm. The policy change is updated in the registry and remains this way, at least until you change it again. The unrestricted means loading absolutely all configuration files and running all scripts.

If you don’t want to unrestrict policy but to bypass the policy for current process, run this command;

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

Now running ng -v will work.

If you want to Install latest version of angular;

npm install -g angular/cli

This is the Location where angular package are stored;

C:\Users\[UserName]\AppData\Roaming\npm\node_modules\@angular\cli\bin\ng

Resources

https://windowsreport.com/powershell-not-digitally-signed/

Installing PowerShell and Az moduel for Azure

Microsoft has switched from AzureRM modeule to Az module. Here is GitHub link to download and install;

https://github.com/PowerShell/PowerShell/releases/tag/v7.1.3

The easiest method is to download. Navigate to the release page;

As of this writing v7.2.0 is available. Scroll down the page and you will see this msi package under Assets;

Download and install. To test open PowerShell in admin mode and type this;

$PSVersionTable.PsVersion

Your PowerShell installation is done. It’s time to install Az-Moduel to interact with Azure.

Install-Module -Name Az -AllowClobber -Force

The -Force flag will install a second version of this module if one already exist.

You might be prompted for NuGet provider pre-requisite at installation startup;

Run this command to install pre-requisite;

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force

Re-run Az installation command;

Install-Module -Name Az -AllowClobber -Force

This might take a minute or two depending on your connection speed. Once done, i can run this command to see how many Az module version i have installed;

Get-InstalledModule -Name Az -AllVersions | Select-Object -Property Name, Version

Since i have only a single version installed, so that’s what i see. if i had multiple versions installed, i would have seen many lines. By default, PowerShell uses the most recent version.

This concludes installation of Az module into PowerShell.

Time to do some good stuff. Run this command to connect to Azure;

Connect-AzAccount

This will open up browser and ask about your credentials. After verification it will show that your session is authenticated. Navigate back to PowerShell an you can see authentiation message.

If you have multiple Active Azure subscriptions, First one will be selected by default.

Run an azure resource command to confirm PowerShell is working;

Get-AzVM, Get-AzWebApp

Switching to another subscriptions

Run this command to see all of your subscriptions;

Get-AzSubscription

You will see a list of assigned subscriptions. To switch to another subscription, store your subscription in a context variable and switch the context.

$context = Get-AzSubscription -SubscriptionId <Subscription ID from list of subscriptions>

Set AzContext $context

Hope this will help.