Automate Azure File Uploads and File Deletes With PowerShell


By:   |   Updated: 2015-06-22   |   Comments (1)   |   Related: More > Azure


Problem

We have a need to upload files daily to Azure storage and then after 90 days remove these files. How can we automate the uploads and deletes? In this tip we will look at how to automate Azure file uploads and deletes using PowerShell.

Solution

The best option is to use PowerShell, which comes with functions we can use to automate Azure file uploads and file deletes. Because some may choose to never remove files, this tip will also highlight how to do that as well (i.e.: removing the delete portion).

Before using either or both functions, make sure that you have both Azure storage and a container on the storage setup. Through the Azure interface, you can quickly create the storage (New then Quick Create), then inside of the storage create the container (select the Containers tab and click Add).

Automate Azure File Uploads and File Deletes PowerShell Function

The first function shows only one approach to obtaining the key: in this case, we extract it from a table stored in a local SQL Server database through the stored procedure stp_GetStorageKey. In calling the function, we will pass in the server name, the storage name and the container name - both the database name (Configuration) and column name (StorageKey) are written in the function, though they can be changed.

We do have other alternatives for obtaining the key if we don't want to use a configuration table; we can (1) save the key to a file and extract it from the file (Get-Content), (2) use the function Get-AzureStorageKey and obtain the property for the primary storage key, or (3) directly put it in the script, though the latter is not recommended. Always consider what's available in your environment and best security approaches to that environment; if using alternatives, the first function will not be needed.

The second function requires the location, where we're keeping our files to be uploaded, the key, and the storage and container names.

###  The below function gets the key from a configuration table
Function Get-AzureStorageKey {
    Param(
    [string]$server
    , [string]$storagename
    , [string]$containername
    , [string]$column
    )
    Process
    {
        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = "Data Source=$server;Initial Catalog=Configuration;Integrated Security=true;"
        $cmd = New-Object System.Data.SqlClient.SqlCommand
        $cmd.Connection = $scon
        
        ###  In this example, the key is stored in a table (three columns of StorageName, ContainerName, and StorageKey) and a  
        ###  procedure returns the value by passing in the parameters for the storage and container name
        $cmd.CommandText = "EXECUTE stp_GetStorageKey '$storagename','$containername'"
        try
        {
            $scon.Open()
            $sqlread = $cmd.ExecuteReader()
    
            while ($sqlread.Read())
            {
                $returnvalue = $sqlread["$column"]
            }
        }
        catch
        {
            Write-Warning $_
        }
        finally
        {
            $scon.Close()
            $scon.Dispose()
        }
        return $returnvalue
    }
}

Function CopyRemove-FilesToAzureStorage {
    Param(
    [string]$location
    , [string]$key
    , [string]$storagename
    , [string]$container
    )
    Process
    {
        if (!(Test-Path $location))
        {
            Write-Warning "File location not found or currently unavailable."
        }
        else
        {
            $bacs = Get-ChildItem $location
            $context = New-AzureStorageContext -StorageAccountName $storagename -StorageAccountKey $key
            ###  Upload files
            foreach ($bac in $bacs | Where-Object {$_.LastWriteTime -gt ((Get-Date).AddDays(-1))})
            {
                $fn = $bac.FullName
                $nm = $bac.Name
                Set-AzureStorageBlobContent -Blob $nm -Container backups -File $fn -Context $context -Force
            }
        }
        ### Delete old files block
		$filelist = Get-AzureStorageBlob -Container $container -Context $context
		
        foreach ($file in $filelist | Where-Object {$_.LastModified.DateTime -lt ((Get-Date).AddDays(-90))})
        {
            $removefile = $file.Name
            if ($removefile -ne $null)
            {
                Write-Host "Removing file $removefile"
                Remove-AzureStorageBlob -Blob $removeFile -Container $container -Context $context
            }
        }
        ### End delete old files block
    }
}

###  Enter the storage and container names below:
$storage = ""
$container = ""
$k = Get-AzureStorageKey -server "" -storagename $storage -containername $container -column "StorageKey"

CopyRemove-FilesToAzureStorage -location "" -key $k -storagename $storage -container $container

Example:

$storage = "erepo"
$container = "sec"
$k = Get-AzureStorageKey -server "SERVER\INSTANCE" -storagename $storage -containername $container -column "StorageKey"

CopyRemove-FilesToAzureStorage -location "D:\Files\erepo\sec\" -key $k -storagename $storage -container $container

This would upload all the files from D:\Files\erepo\sec\ to the container sec on the Azure storage erepo.

Enhancements to the Function

A few possible enhancements to the second function:

$bacs = Get-ChildItem $location -Filter *.txt

If we only want to upload text files, we can just filter by the preferred extension so that we don't have to upload every file in the folder.

### Delete old files block
$filelist = Get-AzureStorageBlob -Container $container -Context $context

foreach ($file in $filelist | Where-Object {$_.LastModified.DateTime -lt ((Get-Date).AddDays(-90))})
{
	$removefile = $file.Name
	if ($removefile -ne $null)
	{
		Write-Host "Removing file $removefile"
		Remove-AzureStorageBlob -Blob $removeFile -Container $container -Context $context
	}
}
### End delete old files block

This whole block can be removed if you always want to keep the files. The script removes files which have been modified earlier than ninety days from the date it's called.

$k = ###  Alternative method of obtaining the key
CopyRemove-FilesToAzureStorage -location "" -key $k -storagename "" -container ""

Finally, if we obtain the key from another approach, we would only need the second function and could call it using the above, where the $k variable would come from our alternate method.

###  Upload block
$fn = $bac.FullName
###  Tells us what file is being uploaded
Write-Host $fn
###  Uploads the file:
Set-AzureStorageBlobContent -Blob $nm -Container backups -File $fn -Context $context -Force

###  Delete block
Write-Host "Removing file $removefile"
###  Comment this out during testing:
#Remove-AzureStorageBlob -Blob $removeFile -Container $container -Context $context

For testing, use Write-Host on the delete and on the upload if you want to change the files being uploaded and removed.

Next Steps
  • Update the script if the time for removal differs, or if you don't need to remove any files.
  • Test the script by first writing out the file names; uploading files and writing out the names that will be removed.
  • Start using this script to automate Azure file uploads and file deletes.
  • Check out these related tips


Last Updated: 2015-06-22


get scripts

next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources





Comments For This Article




Saturday, August 01, 2015 - 6:35:50 AM - Jack Owens Back To Top (38340)

 

Above article helped. If you can also post some screen-shots on what the data (UI Screen) in Azure looked like before/after that will help us understand how the PowerShell example feature really works

 

Thanks

 



download





Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Process Blob Files Automatically using an Azure Function with Blob Trigger

Using Azure Blueprints to deploy Azure SQL Server and Database with Key Vault Secrets

Managing Azure Blueprints with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools