Microsoft.Net Developer: August 2018

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 !!!

Windows Powershell - Error & Solution

I faced some issue while executing powershell script on Windows Server 2012 R2, then I search on google and found solution, which I am sharing with you, which help you for quick solution...

Error: Install-Module : The term 'Install-Module' is not recognized as the name of a cmdlet, function, script file, or operable program.

Solution:

It seems that your powershell version is old, so you need to update it.

Follow below steps,

Open url - https://www.microsoft.com/en-us/download/details.aspx?id=54616
Select - Win8.1AndW2K12R2-KB3191564-x64.msu and download
Install it.
Now you can install-module command will work

Error: New-AzureStorageContext : The term 'New-AzureStorageContext' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again

Solution:

It seems that Azure module is not installed for Powershell command

Install it using below command,

Open Powershell window
Execute - Install-Module -Name AzureRM -Repository PSGallery -Verbose

Error: Backup-SqlDatabase : The term 'Backup-SqlDatabase' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

Solution:

It seems that SQL module is not installed for Powershell command

Install it using below command,

Open Powershell window
Execute - Install-Module -Name SqlServer -Repository PSGallery -Verbose

Hope this will help you and save your time.

Enjoy !!!