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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

 

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

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools