Microsoft.Net Developer

VSTS - Automation for Azure Function issue

I create build Azure function project using VSTS and run successfully. But when I run Release it worked fine for first time, But when I run release next time for the same build definition, it gave me below error.

Error:

Web Deploy cannot modify the file 'DemoApp.DemoFunctions.dll' on the destination because it is locked by an external process. In order to allow the publish operation to succeed, you may need to either restart your application to release the lock, or use the AppOffline rule handler for .Net applications on your next publish attempt. Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_FILE_IN_USE. Error count: 1.

Solution:

1. Login to Azure portal

2. Goto function app and open your function app

3. Click on "Application settings" from Overview tab.

4. Goto "Application settings" section on "Application settings" page.

5. Add MSDEPLOY_RENAME_LOCKED_FILES = 1

6. Save settings from top left corner.

7. Redeploy your Azure function, it will work.

Hope this will help you and save your time.

Enjoy !!!

Insert - Update SQL Database using Powershell Script

Here is example of SQL Server database insert and update transaction using Powershell script function

SQL table

CREATE TABLE [dbo].[BackupLog](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [BackupDetail] [nvarchar](500) NOT NULL,
 [StartTime] [datetime] NULL,
 [EndTime] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert Record

function InsertRecord($con)
{
 ############  Insert Record  ############
 $sqlCommand = New-Object System.Data.SqlClient.SqlCommand
 $sqlCommand.Connection = $con
 $sqlCommand.CommandText = "SET NOCOUNT ON; " +
  "insert into BackupLog " +
  "VALUES (@BackupDetail,@StartTime,@EndTime); " +
  "SELECT SCOPE_IDENTITY() as [InsertedID]; "

 # Define SQL Parameters 
 $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@BackupDetail",[Data.SQLDBType]::NVarChar, 500))) | Out-Null
 $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@StartTime",[Data.SQLDBType]::DateTime2))) | Out-Null
 $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@EndTime",[Data.SQLDBType]::DateTime2))) | Out-Null
  
 # Set Parameters Value
 $sqlCommand.Parameters[0].Value =  get-date
 $sqlCommand.Parameters[1].Value = get-date
 $sqlCommand.Parameters[2].Value = get-date

 # Run the query and get the scope ID back into $InsertedID
 $InsertedID = $sqlCommand.ExecuteScalar()
 return $InsertedID
}

Update Record

function UpdateRecord($con, $recordId)
{
 ############  Update Record  ############
 $sqlCommandUpdate = New-Object System.Data.SqlClient.SqlCommand
 $sqlCommandUpdate.Connection = $con 
 $sqlCommandUpdate.CommandText = "update  BackupLog set  EndTime = @EndTime where id=$recordId " 
  
 # Define SQL Parameters  
 $sqlCommandUpdate.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@EndTime",[Data.SQLDBType]::DateTime2))) | Out-Null
 #$sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@FileLength",[Data.SQLDBType]::BigInt))) | Out-Null
 
 # Set Parameters Value
 $sqlCommandUpdate.Parameters[0].Value = get-date
 
 # Run the query and get the scope ID back into $UpdatedID
 $sqlCommandUpdate.ExecuteScalar() 
}

Powershell Script

cls
 
#SQL connection Configuration
$DBServer = "localhost"
$DBName = "DBName"

# Create SQL Connection object
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"
$sqlConnection.Open()

# Quit if the SQL connection didn't open properly.
if ($sqlConnection.State -ne [Data.ConnectionState]::Open) 
{
    "There is something wrong in connection string"    
}
else
{

 $newid = InsertRecord -con $sqlConnection 
 # Write to the console.
 "Inserted row ID = $newid"  
 
 #wait for 5 second
 Start-Sleep -s 5
 UpdateRecord -con $sqlConnection -recordId $newid
  
 # Close SQL Connection
 $sqlConnection.Close()
}

Hope this will help you and save your time.

Enjoy !!!

:)

Function in Powershell Script

Here is example of function return value in Powershell script

cls
 
Function Sum($ParamA, $ParamB)
{ 
 Write-Host "ParamA = " $ParamA
 Write-Host "ParamB = " + $ParamB
 $total = $ParamA + $ParamB
 Write-Host "Total = " $total
 return $total
} 

$returnValue = Sum -ParamA 10 -ParamB 15

Write-Host "return value = " $returnValue

Hope this will help you and save your time.

Enjoy !!!

Exclude folders from VSTS Build

We can exclude folder contents using VSTS automatic build, here are steps to exclude folders from build,

1. Open build definition
2. Select build solution option from left panel
3. Update MSBuild arguments

Phase 1 => Build Solution => MSBuild Arguments

Append below code of line in "MSBuild Arguments" textbox 
/p:ExcludeFoldersFromDeployment="Content\dist\css\skins;Content\dist\js"

This is exclude two folders, one from Content\dist\css\skins and another from Content\dist\js

Hope this will help you and save your time.

Enjoy !!!

Mongodb Backup using Powershell Script

Take mongodb backup using powershell script....

Create mongodb database backup
Compress backup file in zip file
Upload backup zip file on Azure blob storage

cls
#Set Variables
$environment = "EnvironmentName"
$database = "DatabaseNaMe"
$server = "localhost/IP:portnumber"
$timestamp = get-date -format yyyy-MM-dd-hh-mm-ss
$backupPath = 'D:\Backup\'
$directoryName = "$database-$timestamp"
$directoryPath = Join-Path $backupPath $directoryName
$fileName = "$directoryName.$archiveType"
$filePath = Join-Path $backupPath $fileName
 
$Watch = New-Object System.Diagnostics.StopWatch
$Watch.Start()

#Create mongodb backup
Write-Host "Backing up '$fileName' to local directory: $backupPath."
&'C:\Program Files\MongoDB\Server\3.0\bin\mongodump.exe' -h $server -d $database -o "$directoryPath"


#Create zip file
$source = $directoryPath
$directoryName =  $environment + "-$directoryName" 
$destination = Join-Path $backupPath "$directoryName.zip" 

If(Test-path $destination) {Remove-item $destination}
Add-Type -assembly "system.io.compression.filesystem"
[io.compression.zipfile]::CreateFromDirectory($Source, $destination) 

#Upload Backup Script on Azure blob storage
$localFileDirectory = "D:\backup\"
$StorageAccountName = "storageAccountname" 
$StorageAccountKey = "AccountKey"
$ContainerName = "ContainerName"

$ctx = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey

#Create container 
#New-AzureStorageContainer -Name $ContainerName -Context $ctx -Permission Blob

#Get files list from folder
$files = Get-ChildItem $localFileDirectory *.zip

#process each file
foreach($fileName in $files)
{  
 $localFile = $localFileDirectory + $fileName
 
 $azblob = Get-AzureStorageBlob -Blob $fileName -Container $ContainerName -Context $ctx -ErrorAction SilentlyContinue
 
 If ($azblob -ne $null) {
            # Blob already exists, check the lastwrite metadata
            Write-Host $fileName " file exists"
        } Else {
            
            #Upload file in storage
   Set-AzureStorageBlobContent -File $localFile -Blob $fileName -Container $ContainerName  -Context $ctx  -Force -ErrorAction SilentlyContinue
   #Remove file after upload file
   Remove-Item $localFile
        }
}

Write-Host "Backup file uploaded on storage..."

#Get files list from folder
$folders = Get-ChildItem $localFileDirectory -Directory
foreach($folderName in $folders)
{  
 $folder = $localFileDirectory + "\" + $folderName
 Remove-Item -recurse -force $folder #-erroraction silentlycontinue  
}

$Watch.Stop();

Hope this will help you and save your time.

Enjoy !!!

SQL Backup using Powershell Script

Take SQL Server database backup using powershell script....

Create SQL Server database backup
Compress backup file in zip file
Upload backup zip file on Azure blob storage

cls
#Set Variables
$Server = "localhost"
$Database = "database-name"
$BackupBaseFolder = "D:\backup\"
$BackupFolder = "D:\backup\"
$dt = get-date  -format MM-dd-yyyy-hh-mm-ss
$directoryName = "$($Database)_db_$($dt)"
$BackupFolder = "$($BackupFolder)$($directoryName)\"
$FilePath = "$($BackupFolder)$($Database)_db_$($dt).bak"

#Create backup folder 
md $BackupFolder
 
#Call SQL Command
Backup-SqlDatabase -ServerInstance $Server -Database $Database -BackupFile $FilePath 

#Create zip file
$source = $BackupFolder
$directoryPath = Join-Path $BackupFolder $directoryName
$destination = Join-Path $BackupBaseFolder "$directoryName.zip" 

If(Test-path $destination) {Remove-item $destination}
Add-Type -assembly "system.io.compression.filesystem"
[io.compression.zipfile]::CreateFromDirectory($Source, $destination) 

#Get files list from folder
$folders = Get-ChildItem $BackupBaseFolder -Directory
foreach($folderName in $folders)
{  
 $folder = $BackupBaseFolder + "\" + $folderName
 Remove-Item -recurse -force $folder #-erroraction silentlycontinue  
}

#Upload Backup Script on Azure blob storage

$localFileDirectory = "D:\backup\"
$StorageAccountName = "storageAccountname" 
$StorageAccountKey = "AccountKey"
$ContainerName = "ContainerName"

$ctx = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey

#Create container 
#New-AzureStorageContainer -Name $ContainerName -Context $ctx -Permission Blob
 
#Get files list from folder
$files = Get-ChildItem $localFileDirectory *.zip

#process each file
foreach($fileName in $files)
{  
 $localFile = $localFileDirectory + $fileName
 
 $azblob = Get-AzureStorageBlob -Blob $fileName -Container $ContainerName -Context $ctx -ErrorAction SilentlyContinue
 
 If ($azblob -ne $null) {
            # Blob already exists, check the lastwrite metadata
            Write-Host $fileName " file exists"
        } Else {
            
            #Upload file in storage
   Set-AzureStorageBlobContent -File $localFile -Blob $fileName -Container $ContainerName  -Context $ctx  -Force -ErrorAction SilentlyContinue
   #Remove file after upload file
   Remove-Item $localFile
        }
}

Write-Host "Backup file uploaded on storage..."

Hope this will help you and save your time.

Enjoy !!!

Run Powershell script using windows scheduler task

Once you are ready with powershell script and want to run in some specific time, I mean daily, weekly, monthly , yearly or specific date and time then use Windows scheduler task.

Open task scheduler from "C:\Windows\system32\taskschd.msc"

On the first screen Provide scheduler name which help you to identify in future to execute it.

On trigger tab - set time when you want to execute it automatically.

On action tab, provide powershell command file and script file, refer below screenshot.

Hope this will help you and save your time.

Enjoy !!!