SQLServer datatype conversion tip's

1. Introduction

This post is gathering information about SqlServer Datatypes Conversion.

2. Example

2.1. This is very good sample extracted from StackOverflow

Declare @d datetime

select @d = getdate()

select @d as OriginalDate,

convert(varchar,@d,100) as ConvertedDate,

100 as FormatValue,

'mon dd yyyy hh:miAM (or PM)' as OutputFormat

union all

select @d,convert(varchar,@d,101),101,'mm/dd/yy'

union all

select @d,convert(varchar,@d,102),102,'yy.mm.dd'

union all

select @d,convert(varchar,@d,103),103,'dd/mm/yy'

union all

select @d,convert(varchar,@d,104),104,'dd.mm.yy'

union all

select @d,convert(varchar,@d,105),105,'dd-mm-yy'

union all

select @d,convert(varchar,@d,106),106,'dd mon yy'

union all

select @d,convert(varchar,@d,107),107,'Mon dd, yy'

union all

select @d,convert(varchar,@d,108),108,'hh:mm:ss'

union all

select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'

union all

select @d,convert(varchar,@d,110),110,'mm-dd-yy'

union all

select @d,convert(varchar,@d,111),111,'yy/mm/dd'

union all

select @d,convert(varchar,@d,112),112,'yymmdd'

union all

select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'

union all

select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'

union all

select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'

union all

select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'

union all

select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'

3. References

• http://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion

Script to capture traffic HTTP and HTTPS on linux interface

1. Introduction

Script to capture http and https traffic on linux interface

2. Example

#! /bin/bash

PCAP_FILE_NAME=/tmp/http_traffic.$$.pcap

REPORT_FILE=/tmp/http_traffic_report_`date +%Y%m%d%H%M%S`.txt

DSTNET=10.3.0.0/16

/usr/sbin/tcpdump -G 3600 -w $PCAP_FILE_NAME -W 1 -s 64 -i eth5 "dst net $DSTNET and (src port 80 or src port 443)"

/usr/sbin/tcpdump -nn -r $PCAP_FILE_NAME | awk '{split($5,a,"."); print a[1] "." a[2] "." a[3] "." a[4]  ";" $NF}' | /bin/awk -F";" '{sz[$1] = sz[$1] + $2} END{for (x in sz) {print sz[x] "\t" x}}' | sort -n > $REPORT_FILE

/sbin/ip neighb show >> $REPORT_FILE

rm -f $PCAP_FILE_NAME

Script Power Shell to backup Windows local data to remote Amazon S3 and to remote location server via Security Copy

1. Introduction

Here are usefull scripts written in Power Shell to backup local Windows data to remote location via "Security Copy"  to remote location Amazon S3 via "AWS-CLI ( Command Line Interface)

1.1. Basic Explanation

• Scripts are prepared to run dayly, and separate remote data data into sub-directories sufixed by "day-of-month"
• Each sub-directory of day-of-month, there area sub-directories sufixed by "server name"
• Scripts rotate sub-directory "day-of-month" depending on current date

+ <root-path-area_backup>
  + 1
    + srv0001
    + srv0002
    + srvNNNN
  + 2
    + srv0001
    + srv0002
    + srvNNNN
  + 3
    + srv0001
    + srv0002
    + srvNNNN
    :
  + 31  

    + srv0001
    + srv0002
    + srvNNNN

1.2. Pre-requisites

• 'pscp.exe' 'puttygen.exe': Security Copy software software and key generation installed
• 7-Zip File Manager software installed
• AWS-CLI Amazon Web Service Command Line Interface installed ( see my post how to install )
• SMTP Server ip address availeble to send e-mails
• Pre-shared key file between source and destination server

2. Examples

2.1. Script #1 - Backup Windows DNS information, Windows DHCP and SQLServer Database information to remote server via SCP and send-email notify log 

# ##############################################################################
# filename: backup-srvNNN.ps1
# author  : Josemar F. A. Silva - josemarsilva@yahoo.com.br
# date    : 2014-04-02
# purpose : Backup data information
# pre-reqs: 
#           - Set-ExecutionPolicy Unrestricted
#           - Publich Sha Key 'C:\SCRIPT\srvNNN-PUBLICEKEY.ppk' stored on 
#             '/root/.ssh/authorized_keys'
# remarks :
# #############################################################################

#
# Initializing ...
#

$serverNamePrefixFile = "srvNNN"

$sourceDnsPath    = $Env:windir + "\system32\dns"
$sourceDhcpPath   = $Env:windir + "\system32\dhcp"
$sourceScriptPath = "C:\SCRIPT\*.*"

$targetPath              = "C:\BACKUP"
$targetDnsZipFile        = $targetPath + "\"+ $serverNamePrefixFile + "-windows-system32-dns.zip"
$targetDhcpZipFile       = $targetPath + "\"+ $serverNamePrefixFile + "-windows-system32-dhcp.zip"
$targetDnsExportXmlFile  = $targetPath + "\"+ $serverNamePrefixFile + "-export-dns-server-config.xml"
$targetDnsExportTxtFile  = $targetPath + "\"+ $serverNamePrefixFile + "-export-dns-server-config.txt"
$targetDhcpExportTxtFile = $targetPath + "\"+ $serverNamePrefixFile + "-export-dhcp-server-config.txt"
$targetScriptZipFile     = $targetPath + "\"+ $serverNamePrefixFile + "-script.zip"

$secureCopyExe = "C:\SCRIPT\pscp.exe"
$secureCopyPrivateKeyFile = "C:\SCRIPT\srvNNN-PRIVATEKEY.ppk"
$secureSourcePath = "C:\BACKUP\*.*"
$secureCopyUserHostTarget = "root@backup.example.com.br:/hdex/area_backup/" + (Get-Date -Format %d) + "/srvNNN/disco"

$scriptFile = $MyInvocation.MyCommand.Name
$cr = "
"

$logLocalDirListing = "C:\SCRIPT\local_dir_listing.log"

# Sqlserver
$serverName = "SERVERNAME\SQLSERVERDATABASEINSTANCE"
$databaseName = "DATABASENAME"
$diskFileName = "C:\BACKUP\"+$databaseName+".BAK"
$sqlCmdLineQuery = """BACKUP DATABASE " + $databaseName + " TO DISK = 'C:\BACKUP\" + $databaseName + ".bak' WITH FORMAT, STATS = 10"""

#
$emailTo = "backup@example.com.br"
$emailFrom = "noreply@example.com.br"
$emailSubject = "[BACKUP] "+ $serverNamePrefixFile + " SCP-TO-HDEXT - Day: " + $dayOfMonth
$emailBody = "Sript: " + $scriptFile + $cr + "Server: "+ $serverNameTargetPathSufix + $cr + "Content: " + $sourcePath
$emailSmtpServer = "smtp.example.com.br"
$emailSmtpServerPort = 25
$emailattachment = "C:\SCRIPT\backup-srvNNN.log"


(Get-Date).ToShortDateString() + " " + (Get-Date).ToShortTimeString() + " - " + $scriptFile


#
# Functions ...
#

function create-7zip([String] $aDirectory, [String] $aZipfile){
    [string]$pathToZipExe = "C:\Program Files\7-zip\7z.exe";
    [Array]$arguments = "a", "-tzip", "$aZipfile", "$aDirectory", "-r";
    & $pathToZipExe $arguments;
}


#
# Checking target subdirectories  ...
#
"Checking target subdirectories:"

if (Test-Path -path $targetPath) {
    "  '" + $targetPath + "' already exists!"
} else {
    "  '" + $targetPath + "' created!"
    New-Item $targetPath -type directory
}


#
# Zipping DNS source into Zip targets ...
#
"Zipping '" + $sourceDnsPath + "' into '" + $targetDnsZipFile + "' ..."
create-7zip $sourceDnsPath $targetDnsZipFile


#
# Backup SqlServer
#
$cr + "Backup SqlServer ..." + $cr
& sqlcmd -E -S $serverName -Q ($sqlCmdLineQuery)  ;

#
# Exporting DNS to .xml and .txt ...
#
"Exporting DNS to .xml and .txt ..."
Get-DnsServer | Export-Clixml -Path $targetDnsExportXmlFile
Get-DnsServer >  $targetDnsExportTxtFile

#
# Zipping DHCP source into Zip targets ...
#
"Zipping '" + $sourceDhcpPath + "' into '" + $targetDhcpZipFile + "' ..."
create-7zip $sourceDhcpPath  $targetDhcpZipFile 

#
# Exporting DHCP to .txt ...
#
del $targetDhcpExportTxtFile
Export-DhcpServer -ComputerName $env:COMPUTERNAME -File $targetDhcpExportTxtFile

#
# Zipping SCRIPT source into Zip targets ...
#
"Zipping '" + $sourceScriptPath + "' into '" + $targetScriptZipFile + "' ..."
create-7zip $sourceScriptPath $targetScriptZipFile

#
# Secure Copy to Remote ...
#
$secureCopyExe +" " + "-i" +" " + $secureCopyPrivateKeyFile +" " + $secureSourcePath +" " + $secureCopyUserHostTarget
& $secureCopyExe "-i" $secureCopyPrivateKeyFile $secureSourcePath $secureCopyUserHostTarget;


#
# Local Directory Listing and Remote HdExt Directory Listing
#
$cr + "Local Directory Listing and Remote HdExt Directory Listing ..." + $cr 
dir $targetPath > $logLocalDirListing

#
# Sending e-mail ...
#
"Sending e-mail ..."
$smtpClient = New-Object Net.Mail.SmtpClient($emailSmtpServer, $emailSmtpServerPort)
$smtpClient.EnableSsl = $false
$smtpClient.Credentials = New-Object System.Net.NetworkCredential("username", "password") 

$mailmessage = New-Object system.net.mail.mailmessage 
$mailmessage.from = ($emailFrom) 
$mailmessage.To.add($emailTo)
$mailmessage.Subject = $emailSubject
$mailmessage.Body = $emailBody
$attachment = New-Object System.Net.Mail.Attachment($logLocalDirListing, 'text/plain')
$mailmessage.Attachments.Add($attachment)
$smtpClient.Send($mailmessage)

#
# Finishing ...
#
(Get-Date).ToShortDateString() + " " + (Get-Date).ToShortTimeString() + " - " + $scriptFile


#
# Call Backup aws-s3
#
$cr
"Calling C:\SCRIPT\backup-aws-s3-srvNNN.ps1 ..."
Invoke-Expression C:\SCRIPT\backup-aws-s3-srvNNN.ps1 > C:\SCRIPT\backup-aws-s3-srvNNN.log
$cr

2.2. Script #2 - Backup Windows Folder  to Amazon S3 and notify log

# ##############################################################################
# filename: backup-aws-s3-srvNNNN.ps1
# author  : Josemar Furegatti de Abreu Silva
# date    : 2014-04-02
# purpose : Backup dayly rotate to Amazon S3. Each <day> in 1 .. 31 backup runs,
#           and files are copied to path:
#           - "s3://<bucket-name>/<root-backup-path>/<day>/<servername>
#
# pre-reqs: 
#           - Set-ExecutionPolicy Unrestricted
#           - AWS-CLI (Amazon Command Line Interface) access key configured
# remarks :
# #############################################################################

#
# Initializing ...
#

$serverNameTargetPathSufix = "srvNNNN"
$sourcePath = "c:/BACKUP/"
$dayOfMonth=Get-Date -Format %d
$targetPath = "s3://my-backup/area_backup/"+$dayOfMonth+"/"+$serverNameTargetPathSufix+"/disco/"
$scriptFile = $MyInvocation.MyCommand.Name
$cr = "
"
$awsCpCommandLine = "aws s3 cp"
$awsCpCommandLineOptions = "--recursive"
#
$emailTo = "backup@example.com.br"
$emailFrom = "noreply@example.com.br"
$emailSubject = "[BACKUP] "+ $serverNameTargetPathSufix + " AWS-S3-CP-TO-AMAZON - Day: " + $dayOfMonth
$emailBody = "Sript: " + $scriptFile + $cr + "Server: "+ $serverNameTargetPathSufix + $cr + "Content: " + $sourcePath
$emailSmtpServer = "smtp.example.com.br"
$emailSmtpServerPort = 25
$emailattachment = "C:\SCRIPT\backup-aws-s3-srvNNNN.log"



(Get-Date).ToShortDateString() + " " + (Get-Date).ToShortbackupmeString() + " - " + $scriptFile + "$cr"


#
# AWS-CLI Copy to Amazon S3 ...
#
"$awsCpCommandLine" + " " + $sourcePath + " " + $targetPath + " " + $awsCpCommandLineOpbackupons

aws s3 cp $sourcePath $targetPath $awsCpCommandLineOpbackupons

#
# Sending e-mail ...
#
"Sending e-mail ..."
$smtpClient = New-Object Net.Mail.SmtpClient($emailSmtpServer, $emailSmtpServerPort)
$smtpClient.EnableSsl = $false
$smtpClient.Credenbackupals = New-Object System.Net.NetworkCredenbackupal("username", "password") 

$mailmessage = New-Object system.net.mail.mailmessage 
$mailmessage.from = ($emailFrom) 
$mailmessage.To.add($emailTo)
$mailmessage.Subject = $emailSubject
$mailmessage.Body = $emailBody
# $attachment = New-Object System.Net.Mail.Attachment($emailattachment, 'text/plain')
# $mailmessage.Attachments.Add($attachment)
$smtpClient.Send($mailmessage)


#
# Finishing ...
#
$cr
(Get-Date).ToShortDateString() + " " + (Get-Date).ToShortbackupmeString() + " - " + $scriptFile

3. References

•