Microsoft.Net Developer

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

Jquery - Get querystring value in jquery/javascript

Get querystring value in javascript or jquery...

$(document).ready(function () {
   
    function GetParameterValues(param) {
        var url = window.location.href.slice(window.location.href.indexOf('?') + 1).split('&');
        for (var i = 0; i < url.length; i++) {
            var urlparam = url[i].split('=');
            if (urlparam[0] == param) {
                return urlparam[1];
            }
        }
    } 
 
 //here uid is querystring
  var userId = GetParameterValues('uid');
   // console.log(userId);
 
});

Hope this will help you.

Enjoy !!!

SQL Server - Find Foreign key on table column

How to find foreign key on specific column in the table in SQL Server. Below is the script...

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
where tab1.name='tablename'
and  col1.name ='columnname'

Hope this will help you.

Enjoy !!!

Mongodb - Create instance

Download Mongodb

Open url - https://www.mongodb.com

It will download mongodb-win32-x86_64-2008plus-ssl-4.0.0-signed.msi file, install it in your computer.

Setup Configfilename.conf file

create file and type below commands

storage.dbPath: "D:/Data/Mongo/dbname"
systemLog:
   destination: file
   path: "d:/Data/Mongo/dbname/log/mongo.log"
   logAppend: true
net:
   port: portnumber (e.g. 20005)

Command

mongod.exe --config D:\Data\Mongo\configfilename.conf --service --install --serviceName "MongoDB dbname PortNumber" --serviceDisplayName "MongoDB dbname PortNumber"

Execute command

Open my computer and goto path C:\Program Files\MongoDB\Server\3.0\bin

Type cmd on path, it will open command window

Execute above command

Then open "Services.msc" and start service which you created

Enjoy !!!

Host .Net Core Web API on IIS - ISSUE

After uploaded .Net Core web API on IIS server, I faced below issue , but I found solution after some trial and error.

Issue

<div class = "content-container">
 <h3> HTTP Error 502.5 - Process Failure </h3>
</div>
<div class = "content-container">
 <fieldset>
  <h4> Common causes of this issue: </h4>
  <ul>
   <li> The application process failed to start </li>
   <li> The application process started but then stopped </li>
   <li> The application process started but failed to listen on the configured port </li>
  </ul>
 </fieldset>
</div>

Solution

Open web.config file and make some changes as below

Existing Configuration

<aspNetCore processPath="dotnet" arguments=".\CoreApp.dll -argFile IISExeLauncherArgs.txt" forwardWindowsAuthToken="false" startupTimeLimit="3600" requestTimeout="23:00:00" stdoutLogEnabled="false" stdoutLogFile=".\logs\stdout" />

Working Configuration:

<aspNetCore processPath="dotnet" arguments=".\CoreApp.dll -argFile IISExeLauncherArgs.txt" forwardWindowsAuthToken="false" startupTimeLimit="3600" requestTimeout="23:00:00" stdoutLogEnabled="false" stdoutLogFile=".\logs\stdout" />

1. I had removed ".\" before dll name, as I hosted my application on root folder on server.

2. Removed -argFile IISExeLauncherArgs.txt

I hope that you also resolve your issue by using this solution.

Enjoy !!!